Nested Query Help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Nested Query Help

Author

Topic

mihamil
Starting Member

8 Posts

Posted - 2008-08-06 : 14:43:04

I think I need to use a nested query to do this but I'm unsure.

I have a query built that checks my database for Tax ID numbers that don't fall into one a several different patterns. This query works fine. I get that I expect.

However I also want to have any other rows with an adress that matches one of the addresses with a bad Tax ID number returned as well.

I'm extremely new to SQL and any help would be greatly appreciated.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-06 : 14:59:39

You can do a or in the where clause

where
a.MytaxID like '[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or
( a.Address1 = b.Address1
and a.Address2 = b.Address2
and a.city = b.City
and a.state = b.state
and a.zip = b.zip
)

mihamil
Starting Member

8 Posts

Posted - 2008-08-06 : 17:12:02

I appreciate the quick response but I'm not sure if I understand your response (Sorry I'm really new at this).
Suppose I run my first query just to get the tax id numbers that are not in the right pattern and get these two results

TaxID Street_Number Street_Name
111111 5677 Main
555555 9835 Plum

I also want the query to return any other rows from the same table where the address matches one of the two above. For Example it may look like this.

TaxID Street_Number Street_Name
111111 5677 Main <---Wrong taxid on new listing
555555 9835 Plum <---Wrong taxid on new listing
12A516 5677 Main <---Old listing with right info
13B949 9835 Plum <---Old listing with right info

I hope this makes sense.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-06 : 17:49:26

I see..


Select * from
(
Select a.TaxID,a.Street_Number,a.Street_Name 
from 
Mytable a
where not a.MytaxID like '[0-9][0-9][A-Z][0-9][0-9][0-9]'
) aa 
Left Join
Mytable bb
on aa.Street_Number = bb.Street_Number
and aa.Street_Name = bb.Street_Name

mihamil
Starting Member

8 Posts

Posted - 2008-08-07 : 11:27:01

That works perfect...Thank you

I do have another related question as well. We are trying to find options to solve our solution and I also suggested that instead of showing the listings with bad TaxId number and other listing with identical address, is there a way and I can only show the listing with bad TaxID number and include a count of how many other listing in the table have identical addresses. For example

Count TaxID Street_Number Street_Name.
1 11111 6578 Main
3 26165 7841 Super

The first row would be a bad TaxID and only one address...The one with the bad taxID
The second row would be a bad TaxID and 3 address...the bad one and two other listings that have matching address(which I dont think really need the information on)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources