vendredi 20 février 2015

Select all values ​​except repetitive

I have three tables. First. Name: ExTable. Column: id(integer), name(text)



--------------------
| id | name |
--------------------
| 1 | exA |
| 2 | exB |
| 3 | exC |
--------------------


Second: Name: CerTable. Column: id(integer), name(text)



-------------------
| id | name |
-------------------
| 1 | first |
| 2 | second|
| 3 | third |
-------------------


Third: Name: CerExTable. Column: id(integer), id_cer(integer) from CerTable, id_ex(integer) - from ExTable.



----------------------------------
| id | id_cer | id_ex |
----------------------------------
| 1 | 1 (first) |1 (exA)|
| 2 | 1 (first) |2 (exB)|
| 3 | 1 (first) |3 (exC)|
| 3 | 2 (second) |1 (exA)|
| 3 | 2 (second) |3 (exC)|
| 3 | 3 (third) |2 (exB)|
---------------------------------


So, I want to select data with id_cer = 2 and id_ex !=(not equal!) 1 and 3. So, I want to get next value: exB. if id_cer=3, I want to get: exA, exC, If id_cer=1, I want to get null.


My SQL query:



SELECT ExTable.id, ExTable.name FROM ExTable, CerExTable WHERE CerExTable.id_cer = 2 AND CerExTable.id_ex != ExTable.id


Expected result:



--------------------
| id | name |
--------------------
| 2 | exB |
--------------------


But result is next:



--------------------
| id | name |
--------------------
| 2 | exB |
| 3 | exC |
| 1 | exA |
| 2 | exB |
--------------------


because this query get data firstly for "except exA" - exB, exC, and then "except C" - exA, exB


How to get data exception instead of two lines(for example) "at one time"?


Aucun commentaire:

Enregistrer un commentaire