Re: JOIN - Which and how?

[Nigel Peck]

Nigel Peck wrote:

Ananda Kumar wrote:

did u try this

select * from raw_materials where raw_mat_id not in (select raw_mat_id from

raw_materials__Products);

Hi Ananda,

Thanks for this, I'm really looking to do this in a single SELECT, for efficiency. I'm sure it's possible with a join but just not sure how.

I've just tried:

SELECT `Raw__materials`.`raw_mat_id`, `Raw__materials`.`name`
    FROM
           `Raw__materials`
       LEFT JOIN
           `Raw__materials__Products`
       ON

`Raw__materials`.`raw_mat_id` = `Raw__materials__Products`.`raw_mat_id`

       WHERE

`Raw__materials__Products`.`stock_id` IS NULL AND `Raw__materials__Products`.`product_id` = '1'

But I just get an empty result set. Although the manual seems to suggest that it should work.

I tried taking out " AND `Raw__materials__Products`.`product_id` = '1'" but still an empty result set.

Cheers,
Nigel

Sorry, make that:

SELECT `Raw_materials`.`raw_mat_id`, `Raw_materials`.`name`
      FROM
          `Raw_materials`
      LEFT JOIN
          `Raw_materials__Products`
      ON
          `Raw_materials`.`raw_mat_id` = `Raw_materials__Products`.`raw_mat_id`
      WHERE
          `Raw_materials__Products`.`raw_mat_id` IS NULL AND 
`Raw_materials__Products`.`product_id` = '1'

Cheers,
Nigel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]