sql server programming: Help with WHERE clause -- sql server programming

Re: Help with WHERE clause

Stu
11/25/2006 6:36:45 PM

You're probably going to get a lot of responses as to why your design
is a bad one, so please be prepared. Why is your database using EAV
(entity attribute value) design instead of normalization? What are you
trying to accomplish?

Stu

[quoted text, click to view]

Re: Help with WHERE clause

Chris Lim
11/25/2006 6:37:44 PM

Create a table to hold your search conditions like so:

CREATE TABLE #search(attrib VARCHAR(12) NOT NULL)
INSERT #search VALUES('black')
INSERT #search VALUES('manual')

Then you can compare the matches in your attributes table against the
number of rows in your search condition table:

SELECT DISTINCT veh_id
FROM #attrib
WHERE
( SELECT COUNT(*)
FROM #attrib a
INNER JOIN #search s
ON s.attrib = a.attrib
AND a.veh_id = #attrib.veh_id
)
= ( SELECT COUNT(*) FROM #search)

Chris

[quoted text, click to view]

Re: Help with WHERE clause

Chris Lim
11/25/2006 6:51:58 PM

[quoted text, click to view]

I have used this approach once, where we had a system that allowed
different clients to capture different information about their
customers, and this information wasn't of benefit to the core system.
In other words, the client could defined their own data without it
being a proper part of the database (e.g. customer's favorite pet, eye
color, etc).

But I agree, in the example shown, where the information being captured
seems to be pretty standard then EAV isn't a good approach.

Help with WHERE clause

Dave
11/25/2006 7:08:56 PM

I have a table that associates cars (vehicle ids) with attributes (color,
body style, transmission) and I want to return a list of all cars that have
certain attributes (e.g., a black car with manual transmission).

The table looks like this...

if object_id('tempdb..#attrib') IS NOT NULL
DROP TABLE #attrib
GO
CREATE TABLE #attrib
(veh_id int
,attrib varchar(12)
)
GO
INSERT #attrib (veh_id, attrib) VALUES(1, 'black')
INSERT #attrib (veh_id, attrib) VALUES(1, '2 door')
INSERT #attrib (veh_id, attrib) VALUES(1, 'manual')
INSERT #attrib (veh_id, attrib) VALUES(2, 'red')
INSERT #attrib (veh_id, attrib) VALUES(2, '4 door')
INSERT #attrib (veh_id, attrib) VALUES(2, 'manual')

--check
SELECT * FROM #attrib

--I want all the black cars with manual transmission....

--this returns no matches
SELECT *
FROM #attrib
WHERE attrib='manual' and attrib='black'

--this returns both cars
SELECT distinct veh_id
FROM #attrib
WHERE attrib IN ('manual', 'black')

--this returns both cars
SELECT distinct veh_id
FROM #attrib
WHERE attrib ='manual'
UNION
SELECT distinct veh_id
FROM #attrib
WHERE attrib ='black'

--this returns only the black car with a manual transmission
SELECT distinct a1.veh_id
FROM #attrib a1 JOIN
(SELECT distinct veh_id
FROM #attrib
WHERE attrib ='black') As a2
ON a1.veh_id=a2.veh_id
WHERE attrib ='manual'

I could also write it as a subquery but is there a better approach?

What if I want to find all black cars with manual trsnamissions that are
2 -doors? Then I have to add another join or subquery. I won't know ahead
of time how many attributes the search will be on.

Thanks for any suggestions.

Re: Help with WHERE clause

Dave
11/25/2006 7:48:51 PM

Thanks guys.

Your point is well taken but if I could paraphrase one of America's favorite
Secretaries of Defense:

"You go to war with the schema you have, not the schema you wish you
had."

[quoted text, click to view]

Re: Help with WHERE clause

Dave
11/25/2006 8:28:54 PM

Thanks Chris. That is a creative approach.

[quoted text, click to view]

Re: Help with WHERE clause

Steve Dassin
11/25/2006 9:27:23 PM

An EAV model is anathema to sql precisely because sql
is not relational.
See the thread:
Querying an EAV Table
http://tinyurl.com/yks8hu

for an idea of how a relational database could handle EAV.

http://racster.blogspot.com/

Re: Help with WHERE clause

Chris Lim
11/26/2006 12:01:46 AM

[quoted text, click to view]

In case you're interested, this problem is an example of relational
division. There's actually a better way to do it than the query I
posted (this one doesn't require any subqueries):

SELECT A.veh_id
FROM #attrib A
INNER JOIN #search S
ON S.attrib = A.attrib
GROUP BY A.veh_id
HAVING COUNT(A.attrib) = (SELECT COUNT(attrib) FROM #search);

See http://www.dbazine.com/ofinterest/oi-articles/celko1for an article
on relational division.

Re: Help with WHERE clause

Uri Dimant
11/26/2006 8:59:06 AM

Or
SELECT DISTINCT veh_id
FROM #attrib a
WHERE NOT EXISTS
(SELECT *
FROM #search
WHERE NOT EXISTS
(SELECT *
FROM #attrib AS a1
WHERE (a.attrib = a1.attrib)
AND (a1.veh_id = #search.veh_id)));

[quoted text, click to view]

Re: Help with WHERE clause

JXStern
11/28/2006 11:35:47 PM

On Sat, 25 Nov 2006 19:08:56 -0800, "Dave"
[quoted text, click to view]

select
*
into #candidate
from #attrib
where attrib in ('black','manual')

--- if the arity fits, you've got a hit
select veh_id
from #candidate
group by veh_id
having count(*)=2

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.