Question for SQL experts !
Hye,
I request your help, because I'm blocked by an SQL request. I've tried several
SQL syntax but without success. Does someone have enough knowledges to help
me ?!
Question:
I've three tables:
* table Figurines with columns IDFig, ...
* table Series with columns IDSerie, ...
* table A_SeriesFig with columns IDFig, IDSerie
The third table is an association table between Series and Figurines.
I want to select all rows from the Figurines table which are NOT associated
to a row from the Series table. (i.e. I want to select all Figurines rows
which IDFig is not present in the association table A_SeriesFig).
Does someone know how to do that ?
Thanks for your help.
Gantalf
[786 byte] By [
Gantalf] at [2007-11-9 21:10:31]
# 1 Re: Question for SQL experts !
Regarding the previous problem, I've tried the following SQL request but without
success.
Can someone explain to me why there is a SQL syntax error in my request ?
SELECT count(*) FROM A_SeriesFig,Fig WHERE Fig.IDFig NOT IN (SELECT A_SeriesFig.IDFig
FROM A_SeriesFig)
Thanks,
Gantalf
PS: I'm using MySQL runing on Windows 2000 via ODBC
"Gantalf" <gantalf@free.fr> wrote:
>
>I've three tables:
>* table Fig with columns IDFig, ...
>* table Series with columns IDSerie, ...
>* table A_SeriesFig with columns IDFig, IDSerie
>
>The third table is an association table between Series and Fig.
>
>I want to select all rows from the Fig table which are NOT associated
>to a row from the Series table. (i.e. I want to select all Fig rows
>which IDFig is not present in the association table A_SeriesFig).
# 2 Re: Question for SQL experts !
I've found the solution :
SELECT IDFig from Fig LEFT JOIN A_SeriesFig ON Fig.IDFig=A_SeriesFig.IDFig
WHERE A_SeriesFig.IDFig IS NULL
Regarding the second question, MySQL doesn't support sub select !
Thanks
"Gantalf" <gantalf@free.fr> wrote:
>
>Regarding the previous problem, I've tried the following SQL request but
without
>success.
>
>Can someone explain to me why there is a SQL syntax error in my request
?
>
>SELECT count(*) FROM A_SeriesFig,Fig WHERE Fig.IDFig NOT IN (SELECT A_SeriesFig.IDFig
>FROM A_SeriesFig)
>
>Thanks,
>Gantalf
>
>PS: I'm using MySQL runing on Windows 2000 via ODBC
>
>"Gantalf" <gantalf@free.fr> wrote:
>>
>>I've three tables:
>>* table Fig with columns IDFig, ...
>>* table Series with columns IDSerie, ...
>>* table A_SeriesFig with columns IDFig, IDSerie
>>
>>The third table is an association table between Series and Fig.
>>
>>I want to select all rows from the Fig table which are NOT associated
>>to a row from the Series table. (i.e. I want to select all Fig rows
>>which IDFig is not present in the association table A_SeriesFig).
>
