CODEWITHSUNDEEP
mysqlunion
Zaid Kajee
Zaid Kajee

Reputation: 712

mysql check if field value exists in another table

TABLE PRODUCTS

ProductID   | ProductCode  | SampleRefNum
1              a1
2                             b2
3                             c2

TABLE STOCK LIST

  StockListID   | ProductCode  | SampleRefNum  | QTY
    1              a1                            10
    2                             b2             10
    3                             j100           25

I have to 2 tables. The products tables is the current products on the site. Stock list table is a list uploaded from the POS system , with products and their quantities. Products either have a ProductCode or SampleRefNum .

I am trying to generate a list off all items in the product table that do not have a matching ProductCode or SampleRefNum in the stock list table. in the following e.g the query should return ProductID 3 .

I'm trying to do this with a query , instead of looping and checking results in PHP.

Upvotes: 0

Views: 7140

Answers (5)

Zaid Kajee
Zaid Kajee

Reputation: 712

This is the correct answer. It was part right by @Ye Win and 웃웃웃웃웃

SELECT * FROM products AS p
LEFT JOIN stock_list AS s
ON p.ProductCode = s.ProductCode OR p.SampleRefNum = s.SampleRefNum
WHERE (s.ProductCode IS NULL AND p.ProductCode IS NOT NULL OR s.SampleRefNum IS NULL AND p.SampleRefNum IS NOT NULL)

Upvotes: 0

Mr.Geeker
Mr.Geeker

Reputation: 445

select table1.id table2.id from table1,table2 where tpl1.id=tpl2.id

after that check num_rows of return data if total is 0 that mean no things

Upvotes: 0

Nagendra Nigade
Nagendra Nigade

Reputation: 876

Select productId from Products where productId NOT IN(Select p.productId From Products p
INNER JOIN Stocklist s ON ( p.ProductCode=s.ProductCode || p.SampleRefNum=s.SampleRefNum));

I guess that productId and Stocklist Id are not related to each other they are just primary keys of two different tables.

So try above query.

If my guess is wrong comment the same in detail.

Upvotes: 0

Ye Win
Ye Win

Reputation: 2098

You can use left join for correct your result.

SELECT p.* FROM products AS p
LEFT JOIN stockList AS s
ON p.productID = s.stockListId
OR p.sampleRefNum = s.sampleRefNum;

Upvotes: 2

웃웃웃웃웃
웃웃웃웃웃

Reputation: 11984

Try JOIN these two tables and take products with ProductCode or SampleRefNum is null from STOCK_LIST

select t1.* from PRODUCTS as t1 
left join STOCK_LIST as t2 on t1.ProductID = t2.StockListID
where (t2.ProductCode IS NULL or t2.SampleRefNum is NULL)

Upvotes: 2

Related Questions