I'm having the following tables:
Table a +-------+------------------+------+-----+ | Field | Type | Null | Key | +-------+------------------+------+-----+ | bid | int(10) unsigned | YES | | | cid | int(10) unsigned | YES | | +-------+------------------+------+-----+
Table b +-------+------------------+------+ | Field | Type | Null | +-------+------------------+------+ | bid | int(10) unsigned | NO | | cid | int(10) unsigned | NO | | data | int(10) unsigned | NO | +-------+------------------+------+
When I want to select all rows from b where there's a corresponding bid/cid-pair in a, I simply use a natural join SELECT b.* FROM b NATURAL JOIN a;
and everything is fine.
When a.bid or a.cid is NULL, I want to get every row where the other column matches, e.g. if a.bid is NULL, I want every row where a.cid=b.cid
, if both are NULL I want every column from b.
My naive solution was this:
SELECT DISTINCT b.* FROM b JOIN a ON ( ISNULL(a.bid) OR a.bid=b.bid ) AND (ISNULL(a.cid) OR a.cid=b.cid )
Is there any better way to to this?