CODEWITHSUNDEEP
sqlpostgresql
Patton
Patton

Reputation: 2032

Require help in writing a query

I have 2 tables Table_A and Table_B. Table_A is a parent of Table_B. Table_B has 2 columns which refer to Table_A(I Mean both the columns refer to a column in Parent).

Table_A contains following columns (id,name,className) Table_B contains following column (source,destination) both the columns refer to id in Table_A

Assume that Table_A contains the following data

id     name    className 
---------------------------
1       N1      C1 
2       N2      C2
3       N3      C3
4       N4      C4
5       N5      C6

Table_B

Source  Destination
------------------
1        2
2        3
3        4
3        5

I need a query where I require all Ids present in the Destination and not in Source and I need to join this Ids with Table_A to get name and className.

For the data I have shown the query should retrieve the following rows.

4     N4   C4
5     N5   C5

BTW here I have shown partial data but Table_B has more than 1000 records.

EDIT

I have written the following query but it was showing wrong results

select * from Table_A where id not in (select distinct Source from Table_B)

But it was giving right results currently but on long run we may face performance issue and also query retrieves data which is not present in both Source and Destination.

Upvotes: 5

Views: 101

Answers (3)

hochl
hochl

Reputation: 12920

I only have SQLite installed, but this short query did the job:

SELECT table1.id, table1.name
FROM table1, table2
WHERE table2.destination NOT IN
        (SELECT DISTINCT source FROM table2)
AND table1.id=table2.destination;

-- Result:
-- 4|N4
-- 5|N5

EDIT: Of course it would be interesting to see a performance comparison with other solutions.

Upvotes: 5

onedaywhen
onedaywhen

Reputation: 57023

WITH T1 (id)
     AS
     (
      SELECT Destination
        FROM Table_B
      EXCEPT 
      SELECT Source
        FROM Table_B
     )
SELECT T2.id, T2.name, T2.className
  FROM T1 NATURAL JOIN Table_A AS T2;

Upvotes: 1

Joey Ciechanowicz
Joey Ciechanowicz

Reputation: 3663

You need to split your problem into two parts, the first is getting the Destinations that are not in source, the second is joining the two tables. To get the Destinations which aren't in source, something like this should work:

SELECT Destination FROM Table_B WHERE Destination NOT IN (SELECT Source AS Destination FROM Table_B)

Then what we need is the join, so

SELECT Table_A.* FROM Table_A
JOIN Table_B on Table_A.id = Table_B.Destination
WHERE Destination NOT IN (SELECT Source AS Destination FROM Table_B)

I cant gaurnetee this is correct as I dont have access to a database or query analyzer at the moment, but it should put you on the right track.

Upvotes: 2

Related Questions