Simple query but having trouble....

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)

Simple query but having trouble....

Author

Topic

jakal
Starting Member

5 Posts

Posted - 2008-10-23 : 15:20:52

I have the below 2 tables, I would like to join them on Attribute but only want it to return ProdID 2 since prodID 2 is the only one that has both attributes that are in the second table.

Can anyone give me a solution that can do this in one query?

Thank You

Select ProdID = 1, Attribute = 'Browning'
union
Select 2,'Ithaca'
union
Select 2,'Mossberg'
union
Select 3,'Remington'
union
Select 4,'Ithaca'
union
Select 5,'Mossberg'
union
Select 5,'Remington'

Select Attribute = 'Ithaca'
union
Select 'Mossberg'

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 16:33:16

[code]
select a.prodid from #temp1 a , #temp2 b where a.attribute = b.attribute
group by a.prodid having count(a.prodid) = (select count(*) from #temp2)[/code]

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2008-10-23 : 16:40:14

There has to be a criteria to return just 2. I am assuming it is the count here. And so this should work

Select ProdID from Test1 where Attribute in (Select Attribute from test2) group by prodID
having count(prodID) > 1

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-10-23 : 16:47:16

Here's code that will actually verify that the records exist instead of just verifying a matching count. I wouldn't feel as comforitable useing the other method.


Select * 
into #Tmp1
from
(
Select ProdID = 1, Attribute = 'Browning'
union
Select 2,'Ithaca'
union 
Select 2,'Mossberg'
union 
Select 3,'Remington'
union 
Select 4,'Ithaca'
union 
Select 5,'Mossberg'
union 
Select 5,'Remington'
) a


select * 
into #Tmp2
from
(
Select Attribute = 'Ithaca'
union 
Select 'Mossberg'
) b


Select distinct a.ProdID
from 
#Tmp1 a
Left Join
(
				Select aa.ProdID from
				#Tmp1 aa 						
				Cross join
				#tmP2 bb
				Where
                                not exists (Select * from #Tmp1 aaa where aaa.Attribute = bb.Attribute and aaa.ProdID = aa.ProdID)
 ) b
on a.ProdID = b.ProdID
where b.ProdID is null

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

jakal
Starting Member

5 Posts

Posted - 2008-10-23 : 16:52:37

thanks hanbingl your solution seems to work, I need to test it out a bit more... but I think it is right.

The criteria is, I only want to show products with attributes that exist in the second table.

So if the second table had only 'Ithaca' then I would show

prodid
2
4

since both has that attribute.

when the second table has values of
'Ithaca'
'Mossberg'

I only want to show products that have both of those attributes.

jakal
Starting Member

5 Posts

Posted - 2008-10-23 : 16:55:53

Vinnie881

Thank you, your query seems to be a bit more reliable rather than simply matching the count, but I am going through a lot of records, so performace is very important, if mathing the count is reliable, I have to think about this one.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 17:28:50

Vinnie881's look reliable although can be a little more expansive. My query will fail if you allow Duplicate entries.. of course I can modify it to fit duplicate entry.

here is the query that will fit for duplicate entries:


select prodid from (
select distinct a.* from #temp1 a, #temp2 b where a.attribute = b.attribute)t 
group by prodid having count(*) = (select count(distinct attribute) from #temp2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources