• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

Select * from table1 where table1.id is not in table2.field

Hi

I have need of a query that selects all records that have an id that is not present in another table.


table_1
 field_id

table_2
 field_something

Select all from table_1 where field_id is not in table_2.field_something

Limitations: MySQL server 3.23

I tried the example off the MySql Reference page, only adjusting the fields and tables:

SELECT shop_products_pages.*
                FROM shop_products_pages
                LEFT JOIN shop_products
                ON shop_products.id=shop_products_pages.product
                WHERE shop_products_pages.product IS NULL;

But it fails to produce the desired result.



With kind regards



Ramses aka x_terminat_or_3


In the past my questions got misinterpreted, so to save us both some time I rephrase the question:

How to select those rows that have ids NOT PRESENT in another table.   For the other table, instead of id, use product field.  And it has to work on MySql 3.20

Table1
 id
  1
  2
  3
  4
  5

Table2
 product
 1
 2
 3

Query result:
 4
 5
0
x_terminat_or_3
Asked:
x_terminat_or_3
  • 2
1 Solution
 
BatalfCommented:
Try

SELECT shop_products_pages.*
                FROM shop_products_pages
                LEFT JOIN shop_products
                ON shop_products.id=shop_products_pages.product
                WHERE shop_products.id IS NULL;

That would select all items from shop_products_pages that doesn't have a match in shop_products.

0
 
x_terminat_or_3Author Commented:
Right... stuppid me!

Thanks a lot.



Ramses
0
 
BatalfCommented:
Glad I could help!

Batalf
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now