CODEWITHSUNDEEP
sqlpostgresqljoin
takanuva15
takanuva15

Reputation: 1678

How to union query results but exclude matches based on two columns being equal

I'm trying to combine results from 2 queries but eliminate rows based on whether two columns are equal.

Here's a quick example:

select * from func1(arg1) q1

name      id    parent
----------------------
 name1     0      10
 name2     1      12


select * from func1(arg2) q2

name      id    parent
----------------------
 name4     10      42
 name5     11      42
 name6     12      42

What I can currently get working is:

 select * from
 (select * from func1(arg1))
 union
 (select * from func1(arg2))

but this combination will return 5 total rows (a regular union of q1 and q2).

name      id    parent
----------------------
 name1     0      10
 name2     1      12
 name4     10     42
 name5     11     42
 name6     12     42

I'm looking for 3 total rows like this:

 name      id    parent
----------------------
 name1     0      10
 name2     1      12
 name5     11     42

ie, if a row in q2 has an id that exists as a parent in q1, then exclude it in the union's result. Essentially, this combines "children" and "childless parents"

Additional question: Is it possible to order the results to go by q1.parent unless rows exist in q2 after elimination, in which case we'd "insert" the q2 rows into the result set of q1 based on q1.parent and q2.id?

Example result ordering:

 name      id    parent
----------------------
 name1     0      10
 name5     11     42
 name2     1      12

Upvotes: 0

Views: 1225

Answers (2)

Dennis TheBald
Dennis TheBald

Reputation: 1

Can't you just add a where clause onto the second select? 

  (select * from func1(arg2) 
   where ID not in (select parent from func1(arg1))

Or maybe you need to select the union into a temp table and then apply the filter to a new select from the results.

I don't have access to a DB right now to fiddle around with it.

Upvotes: 0

Steven
Steven

Reputation: 15258

something like this should work:

 select * from
 (select * from func1(arg1) a where not exists (select 1 from func1(arg2) b where a.id = b.parent))
 union
 (select * from func1(arg2))

Upvotes: 1

Related Questions