Query Where the "WHERE" clause is in multiple rows

Question

Post reply

Query Where the "WHERE" clause is in multiple rows

dipetersen

SSC Eights!

Points: 888
More actions
July 2, 2007 at 6:42 am

#173132

I am creating an automation system that will route requests based on a number of conditions. I store the route information in one table and then I have a name/value pair table that has a foreign key from the first table. These name/value pairs are the conditions that have to be met to select the route. The number and type of conditions are dynamic which is why I put them into a separate table.
Now, when I want to create a query that will select all records that match all conditions of the route, the only way I can think of creating this query is to create a query that joins the name/value table on itself for each name/value pair I want to check. For example:
If I have:
Color: Brown
State: NE
Floors: 5
Then my query would look something like
SELECT routeId
FROM routeData INNER JOIN routeData a
ON routeData.routeId = a.routeId and a.itemname='Color' and a.itemvalue='Brown' INNER JOIN routeData b
ON a.routeId = b.routeId and b.itemname='State' and b.itemvalue='NE' INNER JOIN routeData c
ON b.routeId = c.routeId and c.itemname='Floors' and c.itemvalue='5'
This is the best I have come up with. I don't write SQL everyday so I'm not sure if this is the most efficient way to query the information or even if this is the best data model. Any suggestions from the Pro would be helpful.
Kindest Regards,
David Petersen
dipetersen.com

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dipetersen SSC Eights! Points: 888 More actions · Answer 1

--Here is some test data to help explain what I'm trying to do.

--create some sample data

CREATE TABLE #route (routeId int, priority int, destination int)

CREATE TABLE #routeData(routeDataId int, routeId int, itemname varchar(50), itemvalue varchar(50))

--Insert some data

INSERT INTO #route

SELECT 1, 1, 4050 UNION ALL

SELECT 2, 5, 4060 UNION ALL

SELECT 3, 2, 5643

INSERT INTO #routeData

SELECT 1, 1, 'BuildingId','43' UNION ALL

SELECT 2, 1, 'City','Birmingham' UNION ALL

SELECT 3, 1, 'State','AL' UNION ALL

SELECT 4, 2, 'BuildingId','43' UNION ALL

SELECT 5, 2, 'Lastname','Jones' UNION ALL

SELECT 6, 3, 'BuildingId','43' UNION ALL

SELECT 7, 3, 'City','Birmingham' UNION ALL

SELECT 8, 3, 'State','AL' UNION ALL

SELECT 9, 3, 'Lastname','Johnson'

-- check the sample data

SELECT * FROM #route

SELECT * FROM #routeData

-- This should return two records

SELECT r.routeId, r.priority, r.destination

FROM #route r INNER JOIN #routeData a ON r.routeId = a.routeId and a.itemname='BuildingId' and a.itemvalue='43'

INNER JOIN #routeData b ON a.routeId = b.routeId and b.itemname = 'City' and b.itemvalue='Birmingham'

INNER JOIN #routeData c ON b.routeId = c.routeId and c.itemname = 'State' and c.itemvalue='AL'

ORDER BY r.priority ASC

-- This should only return one record

SELECT r.routeId, r.priority, r.destination

FROM #route r INNER JOIN #routeData a ON r.routeId = a.routeId and a.itemname='BuildingId' and a.itemvalue='43'

INNER JOIN #routeData b ON a.routeId = b.routeId and b.itemname = 'City' and b.itemvalue='Birmingham'

INNER JOIN #routeData c ON b.routeId = c.routeId and c.itemname = 'State' and c.itemvalue='AL'

INNER JOIN #routeData d ON c.routeId = d.routeId and d.itemname = 'Lastname' and d.itemvalue='Johnson'

ORDER BY r.priority ASC

DROP TABLE #route

DROP TABLE #routeData

Kindest Regards,

David Petersen
dipetersen.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi David

I'd made a start on this before you posted up your sample data. Rather than start again I thought I'd post up what I've got. Essentially the only difference is that the query is reproducible, i.e. you don't have to keep adding in a JOIN for each extra pair of conditions.

The query will return all routes which have all of the conditions specified. It will also return routes which have conditions in addition to those specified. Routes which are missing one or more conditions are excluded. Let me know if this is any help at all, if not, I'll fall back on your sample data.

-- make some sample data
DROP TABLE #routeData
DROP TABLE #routeParms

CREATE TABLE #routeData (RowID int IDENTITY (1, 1) NOT NULL, routeId INT NOT NULL, itemname VARCHAR(10) NULL, itemvalue VARCHAR(10) NULL)

INSERT INTO #routeData (routeId, itemname, itemvalue)
SELECT 65, 'Color',  'Brown' UNION ALL
SELECT 65, 'State',  'NE' UNION ALL
SELECT 65, 'Floors',  '5' UNION ALL
SELECT 65, 'Carpark',  'Yes' UNION ALL
SELECT 66, 'Color',  'Brown' UNION ALL
SELECT 66, 'State',  'NE' UNION ALL
SELECT 66, 'Floors',  '5' UNION ALL
SELECT 67, 'Color',  'Brown' UNION ALL
--SELECT 67, 'State',  'NE' UNION ALL
SELECT 67, 'Floors',  '5' UNION ALL
SELECT 67, 'Carpark',  'No'

SELECT * FROM #routeData -- Sanity check

-- Create a new table for holding the (unknown?) number of parameter pairs
CREATE TABLE #routeParms (itemname VARCHAR(10) NULL, itemvalue VARCHAR(10) NULL)
INSERT INTO #routeParms (itemname, itemvalue)
 SELECT 'Color',  'Brown' UNION ALL
 SELECT 'State',  'NE' UNION ALL
 SELECT 'Floors',  '5'

SELECT * FROM #routeParms -- Sanity check

-------------------------------------------------------------------------
-------------------------------------------------------------------------

-- this is similar to the original query, just another sanity check
SELECT a.routeId
FROM #routeData a
INNER JOIN #routeData b
 ON a.routeId = b.routeId and b.itemname='State' and b.itemvalue='NE' 
INNER JOIN #routeData c
 ON b.routeId = c.routeId and c.itemname='Floors' and c.itemvalue='5'
WHERE a.itemname='Color' and a.itemvalue='Brown'

-- this is a new query with a constant number of joins
SELECT routeId, count(*) from (
SELECT b.routeId, a.itemname, a.itemvalue 
FROM #routeParms a 
INNER JOIN #routeData b ON b.itemname = a.itemname AND b.itemvalue = a.itemvalue
) t GROUP BY routeId HAVING COUNT(*) = (SELECT COUNT(DISTINCT itemname+itemvalue) FROM #routeParms)

-- which could be joined back to the original table
SELECT x.* 
FROM #routeData x
INNER JOIN (
SELECT routeId from (
 SELECT b.routeId, a.itemname, a.itemvalue 
 FROM #routeParms a 
 INNER JOIN #routeData b ON b.itemname = a.itemname AND b.itemvalue = a.itemvalue
) t GROUP BY routeId HAVING COUNT(*) = (SELECT COUNT(DISTINCT itemname+itemvalue) FROM #routeParms)

) y ON y.routeId = x.routeId

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dipetersen SSC Eights! Points: 888 More actions · Answer 3

Chris

Thanks for the idea. I see where you are going. The data structure that I have is a little different than what you have but it is similar enough that I think I could adapt it to work. My current environment is that I will be receiving XML files and based on the data in those files, I need to route them to specific "buckets". The information contained in the XML file provides the values that makes up the WHERE clause. I'm writing this in ASP and so I suppose I could create a delimited string of all conditions and then send it to a stored procedure that creates a temp table from the delimited string. Then use that temp table in the join as you have illustrated.

Sound right to you? I'm going to give it a try.