CODEWITHSUNDEEP
sqljoin
Chendur Srinivasan
Chendur Srinivasan

Reputation: 29

Unmatched records

I have two tables where i want to display the Id which are present in the MBA table but which are not held in the PIX table

PIX

 Id Channel Product ProgDate    ProgStartTime   ProgEndTime 
    2   PIX Parachute Advansed  2011-04-02  18:00:00.0000000    01:00:00.0000000
    5   PIX Parachute Advansed  2011-04-02  18:00:00.0000000    01:00:00.0000000
    6   PIX Parachute Advansed  2011-04-02  23:00:00.0000000    01:00:00.0000000

MBA

mbaID   Channel Product-MAP Progdate-MAP    AdvTime 
2   PIX Parachute Advansed  4/2/11  19:54:56
3   PIX Parachute Advansed  4/2/11  19:15:59
4   PIX Parachute Advansed  4/2/11  22:26:28
5   PIX Parachute Advansed  4/2/11  21:47:01
6   PIX Parachute Advansed  4/3/11  0:31:08
7   PIX Parachute Advansed  4/3/11  0:17:23

I need columns of 3 , 4 and 7 to be displayed. But when i perform JOin it gives me duplicates. Please help

Upvotes: 0

Views: 76

Answers (2)

Aspirant
Aspirant

Reputation: 2278

Using SUBQUERY

select distinct mbaid from mba where mbaid not in (select distinct id from pix)

Using LEFT OUTER JOIN

select distinct mbaid from mba,pix where mba.mbaid=pix.id(+) and pix.id is null;

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

LEFT JOIN the table MBA with IS NULL predicate to display only those rows that has no entries in the other table PIX. Something like:

SELECT MBA.*
FROM MBA 
LEFT JOIN PIX ON MBA.mbaID = pix.id
WHERE pix.Id IS NULL;

SQL Fiddle Demo

This will give you the rows with the ids 3, 4, 7:

| MBAID | CHANNEL | PRODUCTMAP | PROGDATEMAP |                      ADVTIME |
-----------------------------------------------------------------------------
|     3 |     PIX |  Parachute |    Advansed | April, 02 1911 21:15:59+0000 |
|     4 |     PIX |  Parachute |    Advansed | April, 03 1911 00:26:28+0000 |
|     7 |     PIX |  Parachute |    Advansed | April, 03 1911 02:17:23+0000 |

You can also use the NOT IN predicate instead of JOIN:

SELECT MBA.*
FROM MBA 
WHERE mbaid NOT IN(SELECT id
                   FROM PIX 
                   WHERE id IS NOT NULL);

SQL Fiddle Demo

Upvotes: 2

Related Questions