November 13, 2003 at 4:44 am
Hi,
I have a product that is composed of several attributes, some simple, some complex..
Eg, CAR
Schema
tblCar
carId PK INT
carName VARCHAR(30)
height INT
wheelTypeId INT refs (tblWheelType.wheelTypeId)
exhaustTypeId INT refs (tblExhaustType. exhaustTypeId)
I have a query that will return all the cars that are the same as my current car.,
eg,
SAME:
1) height
2) wheelType
3) exhaustType
SELECT
c.carId,
c.carName,
wt.Wheelname,
et.exhaustName
FROM
tblCar c,
tblWheelType wt,
tblExhaustType et
WHERE
c.height = (@carHeight)
c.wheelTypeId = wt. WheelTypeId
AND c.exhaustTypeId = et. exhaustTypeId
AND c.wheelTypeId = @wheelTypeId
AND c.exhaustTypeId = @ exhaustTypeId
Etc, I think you see where I am going with this..
Ideally, I’d like to be able to comapre products that are not EXACTLY the same, but are fairly similar, eg, same wheels, but different height etc.
Do I have to create a separate query for each product attribute?Is this what you folks normally do?
Many thanks,
Yogi.
November 13, 2003 at 6:02 am
Perhaps something like this:
USE pubs
go
SELECT f.Title_Id, p.Pub_Name, f.Type, f.Advance
FROM Titles f JOIN Titles c ON f.Title_Id <> c.Title_id
AND (f.Type = c.Type OR f.Pub_Id = c.Pub_Id OR f.Advance = c.Advance)
JOIN Publishers p ON f.Pub_Id = p.Pub_Id
WHERE c.Title_Id = 'TC3218'
AND CASE f.Type WHEN c.Type THEN 1 ELSE 0 END
+ CASE f.Pub_Id WHEN c.Pub_Id THEN 1 ELSE 0 END
+ CASE f.Advance WHEN c.Advance THEN 1 ELSE 0 END >= 2
--Jonathan
--Jonathan
November 13, 2003 at 11:18 am
Cheers Jonathan,
I can build use this as a basis for my queries.
ta, yogi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply