Compare Mysql Table And Display Which Not Exist

[shahzad429]

I have two table

 

1. TableID, ProductName

 

2. TableID, AgencyID, ProductID

 

 

what i want to do is to display all the products from table 1 and exclude when TableID of table 1 match ProductID of table 2.

 

 

any help regarding this pelase

 

 

Thanks,

Shahzad

[Christian F.]

I recommend looking up "MySQL JOIN", as that's what you're looking for.

[shahzad429]

select * from product where TableID not in (select ProductID from agencytoproduct where AgencyID='$object[TableID]') order by ProductName asc

 

 

i have used this and it worked

is it ok??

[Barand]

I'd do it like this

 

SELECT p.TableID, p.ProductName
FROM product AS p
LEFT JOIN agencytoproduct AS a ON p.TableID = a.productID
 AND a.AgencyID='$object[TableID]'
WHERE a.productID IS NULL
ORDER BY p.ProductName

Edited December 24, 2012 by Barand

[SofWare]

I'd do it like this

 

SELECT p.TableID, p.ProductName
FROM product AS p
LEFT JOIN agencytoproduct AS a ON p.TableID = a.productID
AND a.AgencyID='$object[TableID]'
WHERE a.productID IS NULL
ORDER BY p.ProductName

 

a.productId isn't null - it doesn't exist - which is why this method doesn't work.

[Christian F.]

If the column doesn't exist then the above method wouldn't work either. If the field is not set to a value, or the JOIN clause doesn't produce any matches for said row, it is NULL.

[Barand]

a.productId isn't null - it doesn't exist - which is why this method doesn't work.

 

SofWare, don't knock it till you've tried it

 

mysql> SELECT * FROM product;
+------------+-------------+
| product_id | productName |
+------------+-------------+
|		  1 | Lawnmower   |
|		  2 | Greenhouse  |
|		  3 | Paving slab |
|		  4 | BBQ		 |
|		  5 | Chainsaw    |
+------------+-------------+


mysql> SELECT * FROM agencytoproduct;
+----+----------+-----------+
| id | agencyID | productID |
+----+----------+-----------+
|  1 |	    1 |		 1 |
|  2 |	    1 |		 2 |
|  3 |	    1 |		 5 |
|  4 |	    2 |		 1 |
|  5 |	    2 |		 3 |
|  6 |	    2 |		 4 |
+----+----------+-----------+

 

Note that for agency 1 there are no products 3 and 4.

 

Running my query:

 

mysql> SELECT p.product_id, p.ProductName
   -> FROM product AS p
   -> LEFT JOIN agencytoproduct AS a ON p.product_ID = a.productID
   -> AND a.AgencyID = '1'
   -> WHERE a.productID IS NULL
   -> ORDER BY p.ProductName;
+------------+-------------+
| product_id | ProductName |
+------------+-------------+
|		  4 | BBQ		 |
|		  3 | Paving slab |
+------------+-------------+