MySQL Forums
Forum List  »  Newbie

Re: which kind of join?
Posted by: Peter Brawley
Date: June 11, 2014 10:23AM

Before you can write a correct query, you need to describe the requirement accurately. "select all rows from TABLE A that aren't referenced from TABLE B with a VALUE='aaaa'" would be ...

select distinct a.*
from a
join b 
where b.name<>'aaaa';
+------+------+
| ID   | NAME |
+------+------+
|    1 | abc  |
|    2 | def  |
|    3 | ghi  |
+------+------+

... but the spec for the result you say you want is "select all rows from TABLE A that aren't referenced from TABLE B with a matching id and with a VALUE='aaaa'". which would be ...

select a.*
from a
left join b on a.id=b.id_table_a and b.name='aaaa'
where b.id is null;
+------+------+
| ID   | NAME |
+------+------+
|    2 | def  |
|    3 | ghi  |
+------+------+

... left join on ... where b... is null is an exclusion join.



Edited 1 time(s). Last edit at 06/11/2014 10:25AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
June 11, 2014 02:33AM
Re: which kind of join?
June 11, 2014 10:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.