CODEWITHSUNDEEP
sqlsql-server
barteloma
barteloma

Reputation: 6855

How to use NOT in relational SQL query

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.

Upvotes: 2

Views: 98

Answers (4)

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 0

boliwe
boliwe

Reputation: 233

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.

Upvotes: 0

Kishore Kumar
Kishore Kumar

Reputation: 78

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
    )

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 6

Related Questions