Joins with three tables[Resolved]

[lpere68]

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.

[Zakary]

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

 
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

[Zakary]

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

[szlamany]

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?

[lpere68]

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