CODEWITHSUNDEEP
phpmysqlsql
Syed Arif Iqbal
Syed Arif Iqbal

Reputation: 1427

Fetching unique rows from one table that columns reference has not in another table

I've two table first Name orders
2nd named printing
orders table structure like this

id job_code job_name qty
1 597 xyz 1000
2 598 lmn 2500
3 599 oqr 20000
4 600 odc 15000

and printing table structure like this

id job_code dispatch qty
1 598 yes 1800
2 600 yes 1456

i want to select all job.code From orders which is not in printing table

I tried myself with this query. 

SELECT DISTINCT orders.job_code, orders.job_name, orders.qty FROM orders  
INNER JOIN printing
ON orders.job_code <> printing.job_code ORDER BY orders.job_code DESC LIMIT 10;

OR

SELECT DISTINCT orders.job_code, orders.job_name, orders.qty FROM orders  
INNER JOIN printing
ON orders.job_code NOT IN (printing.job_code) ORDER BY orders.job_code DESC
LIMIT 10;

but it'll return all jobs which held on orders and printing tables

Upvotes: 1

Views: 58

Answers (3)

Phani
Phani

Reputation: 559

use the query like this

SELECT job_code FROM `orders` left join printingorders on orders.job_code not in ( select job_code from printingorders)

This will give you the result. I tried in my Phpmyadmin.

Upvotes: 0

Mad Angle
Mad Angle

Reputation: 2330

You can use this query.

SELECT
    DISTINCT orders.job_code, orders.job_name, orders.qty 
FROM
    orders 
WHERE
    orders.job_code
    NOT IN
        (SELECT printing.job_code FROM printing)
ORDER BY
    orders.job_code DESC
LIMIT 10

Upvotes: 1

Mikhail Timofeev
Mikhail Timofeev

Reputation: 2169

select job_code from orders 
where job_cobe not in (select job_code from printing)

Upvotes: 1

Related Questions