Join two select statements and collecting data from both

[ms901Boss]

I have two queries that have data that I need in one report.

Query a1:

Select fname, lname,state
from contact
where state = 'TN'

Ouput data:

fname    lname    state
Kim	  Smith	  TN
Jim       Jones   TN
Sarah     Lewis   FL
Joe       Wills   TN

Query a2:

Select fname, lname, state
from customer
where state = 'TN'

Ouput data:

 fname    lname    state
Kim	  Smith	   TN
Jim       Jones    TN
John      LONG     CA
Tim       Brown    TN

Here is what I did with the two quieres

select a1.fname, al.lname,a2.state
from contact 
(select fname, lname,state
from contact
where state = 'TN') as a1
left join 
(select fname, lname,state
from customer
where state = 'TN') as a2
on a1.state = a2.state
where state = 'TN'

The output I get is only the data from the left table.

The expected outcome:
Kim	  Smith	TN
Jim   Jones TN
Joe   Wills TN
Tim   Brown TN

How do I get data from both queries that match the main select statement criteria?

 

[MakeItSo]

Your output data does not match your query.
state='TN' and you expect outputs with states FL and CA?

Please clarify what exactly it is you need.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[pjw001]

I agree with MakeItSo - it would help to know what you are trying to achieve.

Taken at its simplest, I think that this will achieve what you are asking for.

Select fname, lname, state
from contact
where state = 'TN'
UNION
Select fname, lname, state
from customer
where state = 'TN'

It might be useful to add the source table name to each select.

Select fname, lname, state, 'contact' as from_table
from contact
where state = 'TN'
UNION
Select fname, lname, state, 'customer' as from_table
from customer

Apologies is there are errors but I am doing this from memory and have no way of checking it, but hopefully it will point you in the right direction.
where state = 'TN'

 

[mikrom]

try UNION

select fname, lname, state from 
(
  select fname, lname, state from contact
  union
  select fname, lname, state from customer
) as t
where state = 'TN'

Output:

FNAME           LNAME           STATE
Jim       	Jones     	TN
Joe       	Wills     	TN
Kim       	Smith     	TN
Tim       	Brown     	TN

 

[pjw001]

mikrom, why does your code not produce duplicates?

 

[mikrom]

Hi pjw001,
UNION does not produce duplicates, if you need duplicates use UNION ALL

 

[pjw001]

I don't think I ever knew that. I wonder how many times this caused me problems.

I would suggest that UNION ALL is probably what OP wants.

 

[mikrom]

Hi pjw001,
I thought, that probably UNION is needed, because ms901Boss wrote above this

The expected outcome:
Kim   Smith TN
Jim   Jones TN
Joe   Wills TN
Tim   Brown TN

and that is without duplicates.

 

[pjw001]

Hi mikrom,

Yes, but he did say that he wanted data from both tables.

I guess we will have to wait and see what OP wants.

 

[mikrom]

Hi pjw001,
Exactly, the boss who gave us this task should appear here to explain how the result should be.

 

[Andrzejek]

I would also question why do we have the same data in more than one place (table)?
If Kim Smith from TN married and changes her last name, we would need to find her in many places to change her name.
It is also possible to have two (or more) people with the same First/Last name that live in the same state.
Then what

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[pjw001]

My guess is that this is the exact reason OP is trying to extract the data. i.e. so that the database can be tidied up (and possibly redesigned).

Oh, and Kim could be a he.

 

[Andrzejek]

Nothing prevents HIM to change his name to his wife's when married
(or hiding from the law...)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mikrom]

Probably, these are result tables from two queries and the task is to create third query from them.