Comparing 2 quries 2

[TheCandyman]

I have one two queries and i am trying to compare them. The queries pull the same info but for different years. I want everyone in 2003 but not in 2004 to show, how do i do it? My mind just isn't putting this together, but i think i need something like this

select.. qry1, qry2 from...
WHERE companyname03 Not Equal companyname04


something like that work?

 

[jdegeorge]

What is the SQL behind your 2 existing queries. Show us what you've done so far.

Jim DeGeorge

 

[TheCandyman]

Okay, this is the codei have for showing the same companies in both queries. It works, but i want to find the companies that don't match in both.

SELECT qsub_Registered_2003_not_2004.Company AS [2003 Company], qry_Registered_Booth.Company AS [2004 Company]
FROM qry_Registered_Booth INNER JOIN (qsub_Registered_2003_not_2004 INNER JOIN tbl_Company ON qsub_Registered_2003_not_2004.Company = tbl_Company.Company) ON qry_Registered_Booth.Company = tbl_Company.Company;

 

[dodge20]

How about something like this

select * from Query1
where not exists
(select 1 from Query2
where Query1.Company = Query2.Company)
union
select * from Query2
where not exists
(select 1 from Query1
where Query2.Company = Query1.Company)


Dodge20

 

[TheCandyman]

whats with the "select 1 from..."

 

[dodge20]

A very quick explanation of exists is:
Exists just checks to see if a value is returned. It takes a subquery as an argument and returns TRUE if the subquery returns anthing and FALSE if the result is empty. You could put anything inplace of the 1. For example you could put an *, but putting a 1 increases query performance. This isn't like any join, it just checks to see if the record is found.

Dodge20

 

[TheCandyman]

Holy cow!!

it worked

 

[dodge20]

Glad to hear it.

Dodge20

 

[puter55]

That query is worth a star in my book. I have had a similar problem, and was never able to solve it. Have a star on me.