join statement

[cs3mw]

Im really sorry about this because Im sure its really easy. I have the following table called soldproperty

propertyID primary key
clientID
propertytype foreign key
housenumber
sold_date

then i have a table callled property with amongst other info the following

propertyID
mainimage
description

where propertytype in the table soldproperty references the propertyID in the property table. Now what I want is to allow the client to view all the property s/he has bought which will be illustrated by the mainimage and description. I have the code to do this into a simple array but its just extracting the values from the mysql database!! anyone have any ideas?

[jasonahoule]

Give this a try. I am not sure what server side language you are using. Just replace $userID with whatever the equal would be in your language.

SELECT *
FROM property
WHERE propertyid IN (
SELECT propertytype
FROM soldproperty
WHERE clientID = $clientID );

[cs3mw]

yeah that seems to do the trick bar one small thing!! a client can buy a number of the same property hence the housenumber in the soldproperty table. However at the moment this is only showing the one tuple. I hope this makes sence!!

[jasonahoule]

Ok, how about this then?

SELECT *
FROM property p, soldproperty s
WHERE p.propertyID IN (
SELECT propertytype
FROM soldproperty
WHERE clientID = $clientID )
AND p.propertyID = s.propertyID;

[cs3mw]

yeah that works a treat the only difference was the last line instead of p.propertyID = s.propertyID it should be p.propertyID = s.propertytype. Thanks very much for your help

[jasonahoule]

No problem. Glad to help.