I'm using mysql 4.0.x, which does not allow the following query (which works
fine in 4.1). Is there any way I can issue a single query to achieve the
same results? (I want a list of all records from table al, nulled where
there is no match in table alm, which has been filtered. Without a
subquery, the filtering occurs after the outer join, and for one specific
row from alm, I only see the nonmatching records from al where NO other alm
record matches.)
SELECT al.id, alm.idmember, al.listname
FROM addresslists AS al
LEFT OUTER JOIN
(SELECT idlist, idmember FROM addresslistmembers WHERE idmember = 4)
AS alm
ON al.id = alm.idlist
Move the search condition into the ON clause:
SELECT al.id, alm.idmember, al.listname
FROM addresslists AS al
LEFT OUTER JOIN addresslistmembers AS alm
ON al.id = alm.idlist
AND alm.idmember = 4
Re: workaround for outer join using filtering subquery ... Without a subquery, the filtering occurs after the outer join, and for one specific row from alm, I only see the nonmatching records from al where NO other alm record matches.) ... FROM addresslists AS al ...idmember FROM addresslistmembers WHERE idmember = 4) ... (comp.databases)
Re: workaround for outer join using filtering subquery ... Without a subquery, the filtering occurs after the outer join, and for one specific row from alm, I only see the nonmatching records from al where NO other alm record matches.) ... FROM addresslists AS al ...idmember FROM addresslistmembers WHERE idmember = 4) ... (comp.databases)
Re: workaround for outer join using filtering subquery ... Without a subquery, the filtering occurs after the outer join, and for one specific row from alm, I only see the nonmatching records from al where NO other alm record matches.) ... FROM addresslists AS al ...idmember FROM addresslistmembers WHERE idmember = 4) ... (comp.databases)
Re: workaround for outer join using filtering subquery ... that other alm rows with different idmember reference some of the al rows ... FROM addresslists AS al ... LEFT OUTER JOIN addresslistmembers AS alm ON al.id = ... (comp.databases)
workaround for outer join using filtering subquery ... there is no match in table alm,... subquery, the filtering occurs after the outer join, and for one specific ... LEFT OUTER JOIN...Prev by Date: ... (comp.databases)
