Match a list from a query to a list in a table

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

Transact-SQL (2000)

Match a list from a query to a list in a table

Author

Topic

sumo
Starting Member

45 Posts

Posted - 2003-02-14 : 13:30:50

Take the following data as an example below. Query1 is the data I want to check Table1 for an exact match to the list. Each list in Table1 is separated by the ID. Each list is unique to each other, but each list can have items that the others have (the example shows this below). The only thing we have is the list generated by Query1, nothing else. Is there a way to return the proper ID of the matching list from Table1? Many of the queries that I have tried bring back two or more ID's instead of one in certain situations. Here's the query I've tried:


declare @id int
select @id = l.[id] 
	from table1 l
        inner join query1 r on l.name = r.name
	group by l.[id]
	having count(l.[id]) = 7


Query1  Table1
-----   -----------
[Name]  [ID] [Name]
CHEVY   1    CHEVY
DODGE   1    DODGE
FORD    1    FORD
GMC     1    GMC
ISUZU   1    ISUZU
MAZDA   1    MAZDA
NISSAN  1    NISSAN
        2    CHEVY
        2    DODGE
        2    GMC
        3    CHEVY
        3    DODGE
        3    FORD
        3    GMC
        3    ISUZU
        3    MAZDA
        3    NISSAN
        3    TOYOTA

Any help is appreciated. If you need more info, let me know...

Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-14 : 19:39:07

You'll have to be more specific.

However if you want the highest value or lowest value id you can use the max and min aggregate functions

select
MAX(CASE Table1.id WHEN IS NULL THEN 0 ELSE Table1.id),
Query1.Name
FROM Query1 LEFT JOIN Table1 ON Query1.Name = Table1.Name
GROUP BY Query1.Name

sumo
Starting Member

45 Posts

Posted - 2003-02-17 : 07:55:52

quote:

You'll have to be more specific.

Not sure how I can be more specific. I need to match the list that comes from the query with a matching list in the table. It can't be the highest or lowest ID either. The list must match exactly. In the table, there will not be any list duplicates, and my query does work unless there is a list that has the same # of items in it, even if they don't match.

Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-02-17 : 08:08:22

You could use an in clause,

something like

select id
from table1
where table1.name in (name from query)

sumo
Starting Member

45 Posts

Posted - 2003-02-17 : 09:02:22

quote:

You could use an in clause,

something like

select id
from table1
where table1.name in (name from query)

That doesn't work if there are matching items in the list for other lists. Remember, items are duplicated, but lists are not. For example, CHEVY is in Table1 three times. Every one of those will match the IN clause to Query1, which is not the result I need.

Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/

sumo
Starting Member

45 Posts

Posted - 2003-02-17 : 09:07:05

I think I have an answer to myself that was provided by our DBA. I need to test it a bit more, but it seems to work.

Declare @count int
declare @id int

select @count = count(*) from query1
select @id = [id] 
from table1 l
INNER join query1 r on l.name = r.name
where @count = (select count(k.name) from table1 k where l.id=k.id)
group by [id]
having count(l.name) = @count

quote:

Take the following data as an example below. Query1 is the data I want to check Table1 for an exact match to the list. Each list in Table1 is separated by the ID. Each list is unique to each other, but each list can have items that the others have (the example shows this below). The only thing we have is the list generated by Query1, nothing else. Is there a way to return the proper ID of the matching list from Table1? Many of the queries that I have tried bring back two or more ID's instead of one in certain situations. Here's the query I've tried:

declare @id int
select @id = l.[id]
from table1 l
inner join query1 r on l.name = r.name
group by l.[id]
having count(l.[id]) = 7

Query1 Table1
----- -----------
[Name] [ID] [Name]
CHEVY 1 CHEVY
DODGE 1 DODGE
FORD 1 FORD
GMC 1 GMC
ISUZU 1 ISUZU
MAZDA 1 MAZDA
NISSAN 1 NISSAN
2 CHEVY
2 DODGE
2 GMC
3 CHEVY
3 DODGE
3 FORD
3 GMC
3 ISUZU
3 MAZDA
3 NISSAN
3 TOYOTA

Any help is appreciated. If you need more info, let me know...

Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/

Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-02-17 : 09:07:13

you must have more selection criteria to do what you want, what other attributes do your tables have?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-17 : 09:28:24

OK, the lists you are trying to search is in table1, where each list of records has a common "ID" field?

And you want to see which list ID in table1 has the exact same records as in query1, based on the "name" field?

Call this SQL1:

SELECT ID, Name, COUNT(*) as NameCount
FROM Table1
GROUP BY ID, Name

Call this SQL2:

SELECT Name, COUNT(*) as NameCount
FROM Query1
GROUP BY Name

Now, we want to match them up exactly:

SELECT ID FROM
(
SELECT ID, T.Name, T.NameCount
FROM (SQL1) T
INNER JOIN
(SQL2) Q
ON T.Name = Q.Name and T.NameCount = Q.NameCount
)
A
GROUP BY ID
HAVING SUM(T.NameCount) =
(SELECT SUM(NameCount) FROM (SQL2) Q)

That matches up all names and makes sure they occur the same # of times in each, and then checks the total "NameCount" for records that match to ensure it is the same total "NameCount" from query1.

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-02-17 : 10:41:53

Jeff, isn't that going to end up including IDs from Table1 that have name values not present in Query1?

This any use?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14718

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-17 : 10:48:37

ooops ... thanks Arnold.

Change the last query to an OUTER JOIN.

SELECT ID FROM
(
SELECT ID, T.Name, T.NameCount
FROM (SQL1) T
LEFT OUTER JOIN
(SQL2) Q
ON T.Name = Q.Name and T.NameCount = Q.NameCount
)
A
GROUP BY ID
HAVING SUM(T.NameCount) =
(SELECT SUM(NameCount) FROM (SQL2) Q)
AND
SUM(Q.NameCount) = SUM(T.NameCount)

That should take care of it; if the list in table1 has extra records, the HAVING() clause will fail and you won't get a match.

EDIT: added the AND part of the HAVING clause, to further make sure the lists match.

- Jeff

Edited by - jsmith8858 on 02/17/2003 11:00:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources