MySQL LEFT JOIN
I have a table structure as follows:
registered (1 to 0/1) cobuy (1 to 0/*) cobuy_area_instance (0/* to 1) area
A search on a registered user list must return users from left to right, e.g it must return registered users with no cobuy profile, those with a cobuy rofile but no area, and those with all th开发者_开发问答ree.
I know this should be achieved by a left join, but am unclear as to how best to approach. I need fields from the registered table, cobuy table and area table, depending on what exists.
Any help appreicated...
SELECT R.FIELD_1, C.FIELD_2, CAI.FIELD_3
FROM REGISTERED R
LEFT OUTER JOIN COBUY C ON R.UID=C.UID
LEFT OUTER JOIN COBUY_AREA_INSTANCE CAI ON C.CID=CAI.CID
Should give you every item from the REGISTERED table plus any existing items from the COBUY table and any existing items from the COBUY_AREA_INSTANCE table.
Note that I deal with MSSQL so the syntax may not be correct.
Edited for WHERE clause on joins.
SELECT R.FIELD_1, C.FIELD_2, CAI.FIELD_3
FROM REGISTERED R
LEFT OUTER JOIN COBUY C ON (R.UID=C.UID AND C.DATE > '2011-02-14')
LEFT OUTER JOIN COBUY_AREA_INSTANCE CAI ON C.CID=CAI.CID
Seems like the solution to me.
精彩评论