get a list of values appearing in several tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

get a list of values appearing in several tables

Author

Topic

leosuth
Starting Member

5 Posts

Posted - 2008-02-01 : 11:28:41

I have two tables with different numbers of rows so different numbers of values with a single field of interest. Values that can appear anywhere in the Values field in either table are

<lpn> (these are actually freight Licence Plate Numbers of the form "JD0002267647000213")
<blank>
NULL
"N/A"

any of these can be duplicated ie. appear in both tables - although there are any number of <blank>,NULL, and "N/A" in each table, <lpn> are unique within each table.

What I need is code to produce a list of unique values that exist in the two tables - ie. I want to end up with something like

NULL
<blank>
N/A
JD0002267647nnnnn1
JD0002267647nnnnn2
JD0002267647nnnnn3
JD0002267647nnnnn4
JD0002267647nnnnn5
JD0002267647nnnnn6

etc
I don't want to involve temporary tables becuase the result of the query is to be a dataset to my VB.net application

Good one,eh?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-01 : 15:36:49

SELECT Col1 FROM Table1
UNION
SELECT Col1 FROM Table2

E 12°55'05.25"
N 56°04'39.16"

leosuth
Starting Member

5 Posts

Posted - 2008-02-03 : 09:28:45

Hi Thanks

Yes I know that bit - but that will return duplicates - if JD0002267647000213 appears in both tables then the Union will return

JD0002267647000213
JD0002267647000213

What I want is to return a Unique value , ie so JD0002267647000213 appears only once - I just don't know how to apply the DISTINCT and/or GROUP By to the UNION to get the result.

I could do it as a two stage opereation using a temp table, but I'm sure it can be done in a single query. This Query also has to exlude the <blank>, NULL and "N/A" values as well, so my list will be only of unique Licence Plate Numbers

But thx for your help anyway

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-03 : 10:30:07

I think you need to read Books Online about the difference between UNION and UNION ALL.
If I had suggested UNION ALL, you would have get duplicates.
The UNION operator takes care of that for you.

You will get NO DUPLICATES with the use of UNION.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-03 : 10:31:56

[code]SELECT Col1 FROM Table1 WHERE Col1 > '' AND Col1 <> 'N/A'
UNION
SELECT Col1 FROM Table2 WHERE Col1 > '' AND Col1 <> 'N/A'[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-03 : 10:33:04

If all license plate numbers starts with 'J', then you can use

SELECT Col1 FROM Table1 WHERE Col1 > '' AND Col1 < 'N/A'
UNION
SELECT Col1 FROM Table2 WHERE Col1 > '' AND Col1 < 'N/A'

This will automatically elimitate the NULL values for you.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-03 : 10:39:41

To demonstrate the validity of my statements, try this

-- Prepare sample data
DECLARE	@Table1 TABLE (Plate VARCHAR(20))

INSERT	@Table1
SELECT	NULL UNION ALL
SELECT	'' UNION ALL
SELECT	'N/A' UNION ALL
SELECT	'JD0002267647nnnnn1' UNION ALL
SELECT	'JD0002267647nnnnn2' UNION ALL
SELECT	'JD0002267647nnnnn3' UNION ALL
SELECT	'JD0002267647nnnnn4'

DECLARE	@Table2 TABLE (Plate VARCHAR(20))

INSERT	@Table2
SELECT	NULL UNION ALL
SELECT	'' UNION ALL
SELECT	'N/A' UNION ALL
SELECT	'JD0002267647nnnnn3' UNION ALL
SELECT	'JD0002267647nnnnn4' UNION ALL
SELECT	'JD0002267647nnnnn5' UNION ALL
SELECT	'JD0002267647nnnnn6'

-- Show the expexted output
SELECT Plate FROM @Table1 WHERE Plate > '' AND Plate < 'N/A'
UNION
SELECT Plate FROM @Table2 WHERE Plate > '' AND Plate < 'N/A'

And this is the output from the sample data above

Plate
JD0002267647nnnnn1
JD0002267647nnnnn2
JD0002267647nnnnn3
JD0002267647nnnnn4
JD0002267647nnnnn5
JD0002267647nnnnn6

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-03 : 10:40:20

quote:
Originally posted by leosuth

But thx for your help anyway

You're welcome.
It's a comfort to know you are that sure my suggestion was wrong. Good luck with your studies.

Feel free to ask a new question anytime.

E 12°55'05.25"
N 56°04'39.16"

leosuth
Starting Member

5 Posts

Posted - 2008-02-04 : 06:20:27

quote:
It's a comfort to know you are that sure my suggestion was wrong. Good luck with your studies.

Really no need for sarcasm - I was not at all sure I was right and you were wrong. I'd just forgotten about UNION ALL, is all

However, I've proven it to myself, thanks for helping me

- have to say DOH! here I should really have known the answer myself

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 06:58:32

I was objecting to

quote:
but that will return duplicates

since that indicated that

1) You didn't care to even test the suggestion
2) You had made your mind up that the suggestion would not work
3) You did test but did not copy and paste exact code as given in the suggestion.

It is good you tested yourself and found the answer.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 07:25:05

quote:
Originally posted by leosuth

Hi Thanks

Yes I know that bit - but that will return duplicates - if JD0002267647000213 appears in both tables then the Union will return

JD0002267647000213
JD0002267647000213

What I want is to return a Unique value , ie so JD0002267647000213 appears only once - I just don't know how to apply the DISTINCT and/or GROUP By to the UNION to get the result.

I could do it as a two stage opereation using a temp table, but I'm sure it can be done in a single query. This Query also has to exlude the <blank>, NULL and "N/A" values as well, so my list will be only of unique Licence Plate Numbers

But thx for your help anyway

select 'JD0002267647000213'
union
select 'JD0002267647000213'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources