[SOLVED] SELECT only rows whose keys can not be found in an another table

[taps128]

Hi, Im working on a database for a bakery.
I have one table which stores ingediends , and one table which stores recepies and the keys of the ingrediends which go in those recepies.
Now I dont want my users to change those ingredients which are allready used by some recepies, the easiest way to do it for me is not to show those ingredients on the change menu

I used this sql statement to do it

[code]SELECT distinct sirovine.sifra_sirovine as sifra_sirovine , sirovine.naziv_sirovine as naziv_sirovine FROM sirovine,recepti_sirovine WHERE sirovine.sifra_sirovine!=recepti_sirovine.sifra_sirovine ORDER BY sirovine.sifra_sirovine ASC[/code]

now, it doesn't work like it should. It still shows ingredients which are used in a recepie. Does any one how can I not show rows whiche have correltaitng data in one other table?

TIA

[taps128]

I solved the problem. Here is the solution

[code]SELECT *
FROM sirovine
WHERE NOT
EXISTS (

SELECT *
FROM recepti_sirovine
WHERE sirovine.sifra_sirovine = recepti_sirovine.sifra_sirovine
)
ORDER BY sifra_sirovine ASC [/code]

[Vikas Jayna]

A left join could be used to do the same:

[code]SELECT *
FROM sirovine left join recepti_sirovine on sirovine.sifra_sirovine=recepti_sirovine.sifra_sirovine
where recepti_sirovine.sifra_sirovine is null
ORDER BY sirovine.sifra_sirovine ASC [/code]