Join Brainfart...

MySQL.
Two relevant tables.

table one (swatches)
swatch_id
swatch_desc
...other stuff that doesn't matter....

Table two (avail)
avail_id
swatch_id
product_id

..end

the query i want is this...

SELECT swatches.* from swatches WHERE swatches.swatch_id NOT_IN(SELECT swatch_id FROM avail WHERE product_id='somenumber')

But I know that mysql doesn't support sub-queries. (which it really should) ...

I know I have done this before but my brain is on too many hours of ASP and JAVA so help a brother out....

SELECT swatches.* from swatches LEFT JOIN avail ON swatches.swatch_id=avail.swatch_id WHERE avail.product_id='somenumber' AND ...?

tha_mink

Hi,

if you want to select all records of a table that have no counterpart in another table you can do:

SELECT swatches.* FROM swatches
LEFT JOIN avail ON swatches.swatch_id=avail.id
WHERE avail.product_id='somenumber' AND
avail.swatch_id IS NULL

I hope this works.

To get all records out if swatches that don't have a counterpart in avail you can remove the "avail.product_id='somenumber' AND" in the query.

Thanks for the effort but that's not what I want.

I need to return rows that do not exist in a subselect.

From what I can tell, Tsinka has answered your question.

If you need more help, see this thread.