Union where it matches against only one shared column

Question

Post reply

Union where it matches against only one shared column

ldanks

Say Hey Kid

Points: 676
More actions
April 3, 2025 at 1:04 pm
Go to Answer
#4572509

Hi folks
I have two tables, both with a NAME, START and END fields
I'd like to list all of the rows from Table A, and then only the rows from Table B where only the START does not exist in Table A. The NAMES in Table B will ALWAYS be different from those in Table A
- and this is what causes my problem, the union returns all values from both tables, because it compares all fields and sees no exact matches to limit the data - I wanted it to only match on START to decide what to filter out of Table B!
this is also very simplified, each 'set' of tables is actually a complex query of multiple tables with identical field names and datatypes
any help appreciated
Les

Phil Parkin

SSC Guru

Points: 247180

April 3, 2025 at 1:10 pm

Answer

#4572511

SELECT cols FROM TableA
UNION ALL 
SELECT Cols FROM TableB b 
WHERE NOT EXISTS (SELECT 1 FROM TableA a WHERE a.START = b.START)

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ldanks Say Hey Kid Points: 676 More actions · Answer 1

Hi Phil, that has worked a treat .. I used the same convention but used a CTE to create 'virtual tables' since Tables A and B did not exist in real terms

So my main dataset becomes SOURCE, and my second query result set becomes STRUCTURE

Select starta, enda, titlea from Source

union all

select startb, endb, titleb from structure t

where not exists (Select 1 from Source s where s.starta = t.startb)

It has raised a secondary problem - the SOURCE dataset includes an ID and I could probably do with whatever that is in all the rows for the final result even though the STRUCTURE has no ID! So effectively appended the table a id to all unioned results. This possible?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

No problem. Just create a fake matching column in the STRUCTURE dataset (make sure its datatype matches what's in SOURCE)

SELECT Cols, Id
from Source 
UNION ALL
Select Cols, Id = 0
from STRUCTURE

ldanks Say Hey Kid Points: 676 More actions · Answer 3

Hi Phil - this is adding an ID of zero into the 'unioned' result - I need the second part of this union to effectively inherit the ID from the first part - does that make any sense?

so if all rows returned from tableA have an ID of 'Les1' for example (data from one of it's fields)

the lower part of the union also needs an ID of 'les1'

don't mean to swallow up your time..I WILL find a way LOL

ldanks Say Hey Kid Points: 676 More actions · Answer 4

I've resolved by virtualising the union results in the cte and adding another select - this may slow the whole thing down a touch but in truth the query will be always be filtered to 1 person (couple of thousand rows max), so probably not noticeable.

, unioned AS (

Select starta, enda, titlea from Source

union all

select startb, endb, TITLEb from structure t

where not exists (Select 1 from Source s where s.starta = t.startb))

select distinct ID, u.starta, u.enda, u.titlea from myperson p

inner join source s on p_id=s.ID

,unioned u

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

ldanks wrote:

Hi Phil - this is adding an ID of zero into the 'unioned' result - I need the second part of this union to effectively inherit the ID from the first part - does that make any sense?

There's no straightforward way of doing that (other than adding another layer around it, as you have subsequently done). The data in the two parts of the UNION ALL are separate and cannot interact.

ldanks Say Hey Kid Points: 676 More actions · Answer 6

Thanks for the help Phil, it was much appreciated.