How to use NOT in relational SQL query

Question

when I select table like this: select count(*) from products is returning 12900 result.

I have a relational query that returns multiple table relation reult like this:

SELECT category.name, 
       manifacturer.name,
       supplier.name,
       product.name
FROM   products as product,
       suppliers as supplier, 
       manifacturers as manifacturer,
       categories as category
WHERE  product.sid=supplier.id AND
       product.manid = manifacturer.id AND
       product.catid = category.id

This query returns 12873 result,

So I can not find which data is not matched. How can I find this lost data? I used NOT query but did not return any result.

You have NULL's somewhere in product.sid, product.manid or product.catid. — jarlh
– jarlh, Commented Jan 16, 2015 at 13:45
Not related to the question but an alias is intended to give a new name to a table. When you alias the table as the same name it provides no benefit but requires more typing. And for what it is worth, there is no "i" in manufacturer. :) — Sean Lange
– Sean Lange, Commented Jan 16, 2015 at 14:07

Gordon Linoff · Accepted Answer · 2015-01-16 12:22:31Z

First, you should learn to use proper, explicit join syntax:

SELECT category.name, manifacturer.name, supplier.name, product.name
FROM   products as product join
       suppliers as supplier
       on product.sid = supplier.id join
       manifacturers as manifacturer
       on product.manid = manifacturer.id join
       categories as category
       on product.catid = category.id;

Then if you want non-matches, switch to left join and look for non-matches in the where clause:

SELECT category.name, manifacturer.name, supplier.name, product.name
FROM   products as product left join
       suppliers as supplier
       on product.sid = supplier.id left join
       manifacturers as manifacturer
       on product.manid = manifacturer.id left join
       categories as category
       on product.catid = category.id
WHERE supplier.id IS NULL OR manifacturer.id IS NULL or category.id IS NULL;

boliwe · Accepted Answer · 2015-01-16 12:59:27Z

0

You can see that not matched records with FULL OUTER JOIN like this:

SELECT prod.id, rel.id FROM (
SELECT category.name, 
       manifacturer.name,
       supplier.name,
       product.name
FROM   products as product,
       suppliers as supplier, 
       manifacturers as manifacturer,
       categories as category
WHERE  product.sid=supplier.id AND
       product.manid = manifacturer.id AND
       product.catid = category.id
) as rel
FULL OUTER JOIN products as prod
ON rel.id = prod.id

So you can see null id and not null ids in list.

answered Jan 16, 2015 at 12:59

boliwe

2331 gold badge2 silver badges13 bronze badges

Comments

Sean Lange · Accepted Answer · 2015-01-16 14:11:40Z

0

In addition to my comments about aliasing above you should use the "newer" join syntax introduced in ANSI-92. Notice how much less code there is here for the same thing. The way you wrote your code all your joins were inner joins, since you want to return rows with no match I changed them to left joins.

SELECT c.name, 
       m.name,
       s.name,
       p.name
FROM products p
left join suppliers s on p.sid = s.id
left join manifacturers m on p.manid = m.id
left join categories c on p.catid = c.id

answered Jan 16, 2015 at 14:11

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Comments

Kishore Kumar · Accepted Answer · 2015-01-16 12:29:38Z

-1

Below Query returns those records whose products are not present in your Query :

SELECT *
FROM Products P
WHERE (P.sid,P.mainid,P.catid) NOT IN (
    SELECT DISTINCT product.sid
    ,product.mainid
    ,product.catid
    FROM products AS product
        ,suppliers AS supplier
        ,manifacturers AS manifacturer
        ,categories AS category
    WHERE product.sid = supplier.id
        AND product.manid = manifacturer.id
        AND product.catid = category.id
    )

answered Jan 16, 2015 at 12:29

Kishore Kumar

786 bronze badges

Collectives™ on Stack Overflow

How to use NOT in relational SQL query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related