Single Table Query <blush> - 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

Single Table Query <blush>

Author

Topic

Hillman
Starting Member

2 Posts

Posted - 2006-10-20 : 07:15:46

I usually write (very simple) queries and export to Excel, then work my magic there. But there's got to be a better way.

sampletable
CUSTNO PARTNO
ABC001 A1
ABC002 A1
ABC002 A2
ABC003 A2

I need to find all CUSTNO that have both PARTNO A1 and A2.

select * from sampletable
where PARTNO in('A1','A2')
returns 2 rows.

I really only want a list of unique CUSTNO ... "Only 1 customer has both partno A1 & A2".

Thank you,
--mark

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 07:31:54

[code]Select distinct CustNo
from Tbl
where PartNo = 'A1' and PartNo = 'A2'[/code]

Harsh Athalye
India.
"Nothing is Impossible"

Hillman
Starting Member

2 Posts

Posted - 2006-10-20 : 07:48:22

I appreciate the quick response, but that query returns 0 rows.

I do not have update rights to the data, so I cannot add any columns or additional tables.

I've also tried various combinations of JOIN and INTERSECT without success. Then again, my typical usage ends after a basic SELECT WHERE statement.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 08:03:54

My sincere appologies!!

I didn't read it quite correctly. Try this:

Select CustNo
From tbl t1
where partno = 'A1'
and exists
(Select CustNo
from tbl t2
where t2.CustNo = t1.CustNo and
partno = 'A2')

Harsh Athalye
India.
"Nothing is Impossible"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-10-20 : 09:09:17

SELECT custno, count(*) from
(select * FROM dbo.tbl
WHERE partno IN ('A1,',A2')) a
GROUP BY custno
HAVING count(*) = 2

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-20 : 10:17:05

SELECT DISTINCT CUSTNO
FROM sampletable
WHERE PARTNO IN ('A1','A2')

My bad - didn't read the question, like the subject said - <blush>

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-10-23 : 06:46:25

snSQL...

SELECT DISTINCT CUSTNO
FROM sampletable
WHERE PARTNO IN ('A1','A2')

This won't work....because it would break the requirement "I need to find all CUSTNO that have both PARTNO A1 and A2."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 07:08:05

[code]-- Prepare test data
declare @test table (CUSTNO varchar(6), PARTNO varchar(2))

insert @test
select 'ABC001', 'A1' union all
select 'ABC002', 'A1' union all
select 'ABC002', 'A2' union all
select 'ABC003', 'A2'

-- Do the work
select distinct t1.custno
from @test t1
inner join @test t2 on t2.custno = t1.custno and t2.partno = 'a2'
where t1.partno = 'a1'
order by t1.custno[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 07:12:26

quote:
Originally posted by AndrewMurphy

SELECT custno, count(*) from
(select * FROM dbo.tbl
WHERE partno IN ('A1,',A2')) a
GROUP BY custno
HAVING count(*) = 2

What if there are two ABC001 with PartNo A1?

However, this works

SELECT		custno,
		count(*)
from		@test
GROUP BY	custno
HAVING		min(partno) = 'a1'
		and max(partno) = 'a2'

But also gives the right result if partno is A11 for same custno. But the original posting doesn't tell if A1 and A2 are the ONLY VALID options.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-23 : 08:37:48

lots of different variations here, some work and some don't, but assuming that PK of your table is PartNo/CustNo then the easiest solution is:

select CustNo
from YourTable
where PartNo in('a1','a2')
group by CustNo
having count(*) = 2

If there is no PK on that table or a different PK, then you need to add a derived table with a DISTINCT:

select CustNo
from (select distinct CustNo, PartNo from YourTable) x
where PartNo in('a1','a2')
group by CustNo
having count(*) = 2

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 09:01:59

Here is a query based on my previous that gets the custno's that has ONLY 'A1' and 'A2' as partno.

SELECT distinct	custno,
		count(*)
from		@test
GROUP BY	custno
HAVING		min(partno) = 'a1'
		and max(partno) = 'a2'
		and count(distinct partno) = 2

Peter Larsson
Helsingborg, Sweden

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-24 : 11:41:46

looks like this is answered, with several ways to go (I think Peso's first answer with the join is a good way to go, easy for a starting sql programmer to understand).. but, I was curious about the poster's comment :

select * from sampletable
where PARTNO in('A1','A2')
returns 2 rows.

How can this return 2 rows? It returns 4 rows from his sample data.. even if he put select distinct custno instead of select * it would return 3 rows.... curious...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources