Simple Query - 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

General SQL Server Forums

New to SQL Server Programming

Simple Query

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-11 : 10:20:56

For some odd reason, I cannot figure out how to only pull the incorrect data that I am looking for. I know it has to be something simple, but still not sure.

AHS Product Address 1
X IMW 495 THOMAS JONES WAY
X WCP 4 95 THOMAS JONES WAY

What I am trying to do is pull only the active records that have these two products with the same address. Seems simple right, but I am not only getting what I have above, I am getting any wcp or IMW addresses and that is not what I want. I just want to pull the ones that have the same addresses.

Note....AHS = 'X' means active clients.

Here is the select statment that I was trying to start.

select Distinct
AHS,
product,
[Termination Date],
[Address 1]
from vw_All_Products
where ---[PCS Number] = '00072760' and
Product IN ('WCP','IMW') and
AHS = 'X'
order by [Address 1]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 10:57:44

I assume the difference in your sample addresses is a typo.

Not beautyful but it should work:

select *
from vw_All_Products
where Product IN ('WCP','IMW') and
AHS = 'X'
and [Address 1] in
(
select [Address 1]
from vw_All_Products
where Product IN ('WCP','IMW')
and AHS = 'X'
group by [Address 1]
having count(*) > 1)

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-11 : 11:13:26

Just this wont do?

select AHS,Address1 from vw_All_Products
where AHS = 'X' and Product IN ('IMW','WCP')
GROUP BY AHS,Address1
having count(*) > 1

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-11 : 13:08:12

Webfred, no there is no typo. That was my fault when I was copying and pasting this to the forum.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-12 : 11:27:49

So is your problem now solved or not?

No, you're never too old to Yak'n'Roll if you're too young to die.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-14 : 06:36:51

It's pulling more of the errors tht I want to see, but it is still giving me other records that don't pertain to what I want to see. I Just want to be able to pull all active addresses that have the same product for WCP and IMW.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-14 : 09:58:20

What are the other unwanted records that it seems to be pulling? Can you give some examples. Also, Pls post the entire query you used.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-14 : 12:33:36

I actually figured somethin out. Used this and it worked for me. Thanks for you help.

Select Distinct [Pcs Number], [Address 1], [Tax ID]from vw_ALL_Products
Where
[AHS] = 'X' and Product = 'IMW'

Intersect

Select Distinct [Pcs Number], [Address 1], [Tax ID]from vw_ALL_Products
Where
[AHS] = 'X' and Product = 'WCP'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources