开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜