SQL Question

[cards4]

I'm trying to write a SQL query that will give me the records of those who have two different tax codes. Here is my query:

select eid, tax_code from tax_table where tax_status='?' group by eid, tax_code order by eid

For simplicity purposes, this is an example of a record that this query produced:

Employee ID Tax Code
00027 KS
00027 MO

Is there a way for SQL to determine for each employee ID if they have more than one Tax code? I've tried doing a count on Tax Code but its given me errors. Please help! Thank you.

The ? is not part of the actual query.

 

[mrdenny]

You will want something like this.

select a.EmployeeID, a.TaxCode
from table a
join (select EmployeeID, count(distinct TaxCode) ct
from table
group by EmployeeID
having count(distinct TaxCode) <> 1) b
on a.EmployeeID = b.EmployeeID

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[cards4]

I'm sorry. I didn't provide enough information. In my original post I forgot to display another column that will be of interest:
Employee ID Tax ID Tax Code
00027 STATE KS
00027 STATE MO

I need to get those records where there are more than one tax code for the tax id of 'State'. Please help.

 

[VVVA]

I'm not sure I totally understand what you are trying to do, but see if this works...

select
 eid,
 tax_code 
from
 tax_table 
where
 tax_status='?' and
 taxid='State'
group by
 eid,
 tax_code
having 
 count(TaxCode)>1
order by eid

 

[VVVA]

Whoops...

select
 eid,
 tax_code
from
 tax_table
where
 tax_status='?' and
 taxid='State'
group by
 eid,
 tax_code
having
 count(distinct TaxCode)>1
order by eid

 

[Catadmin]

Denny's code should work for you. All you have to do is add the Tax_ID column to the SELECT statement and add a WHERE Tax_ID = State to both the subquery and the outside query.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[cards4]

Yes, it worked. Thank you very much.

 

[cards4]

I have another question. The results I got from the query were correct but now I want to left outer join those results to another table. Here's my query:

(
select a.eid, a.tid, a.tc, a.ts
from et a
join (select eid, count(distinct tc) tcc
from et
where ts='?' and tid='STATE' and (ytw > 0 and ytow > 0)
group by eid
having count(distinct tc) > 1) b
on a.eid = b.eid
) y
left outer join (select eid, eno, fn, ln, mn from ep) x
on y.eid=x.eid

When I run this query, it tells me incorrect syntax near 'y' and incorrect syntax near 'x'. I'm new to SQL and I'm not sure why its giving me this error. Please help.

 

[SQLBill]

Y is a derived table with no SELECT. For more detail, see my response in THREAD183-1312121.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

SQLBill is correct. You have no SELECT and FROM statement outside the subquery & JOIN, so your query, as written, won't work.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"