Joins with three tables[Resolved]

Hi,
I want this result:

ProductName FirstName SecondName
Server Jesse James
PC Peter Parker
Laptop null null


My problem is that ProductName comes from a different table, First Name and Last Name from user table and there is an association between the two in another table

I have tried writing joins for this but am struggling right now. This was my earlier query when Firstyname and second name could not be null

select product.productname, userprofile.firstname, userprofile.secondnamefrom userprofile, product, facilityuserassoc where facilityuserassoc.fk_product_user_type_id = 1 and facilityuserassoc.fk_product_id = product.pk_product_id and facilityuserassoc.fk_user_id = userprofile.pk_user_id

Can you please have a look into this.

Try this ..
You'll probably have to switch LEFT for RIGHT join depending on witch table is the father VS child,. with I don't know

Code:

---


SELECT
product.productname,
userprofile.firstname,
userprofile.secondname
FROM
facilityuserassoc
        LEFT JOIN product
        ON acilityuserassoc.fk_product_id = product.pk_product_id
        LEFT JOIN userprofile
                ON facilityuserassoc.fk_user_id = userprofile.pk_user_id

---

You may also take a look here
http://msdn.microsoft.com/library/de...a-ses_9sfo.asp

Do you want the product to appear even if no users are associated with it? That appears to be the case since you show LAPTOP with no one...

Can a product have more than one associated user?

Yes,
A product may have no users associated with it

and

No,
A product can have a maximum of 1 user

Thanks Zakary,

It Works.

Now, I have to insert a couple of more tables into it