Query with multiple ID to check and response...

Hi there, I tried to make it different as usual but i′m stacked into this problem:

Supose TABLE Details
ID_DET ID_CAR DETAILS
1 3 1,2,3,4,5,6
2 4 2,4
3 5 5,6,7,8

and
TABLE Details_Items
ID_DI DETAIL_NAME
1 Stereo HiFi CD
2 Alarm
3 AirConditioning
4 LeatherSeats
5 Pro Wires
6 Aluminium Wheels

The problem appears when i need to bring CAR DETAILS (NAME) from TABLE DETAIL_ITEMS.
Mi guess i that I should make something like:
SELECT * FROM DETAILS_NAME WHERE id_di = (( Array(i) FROM Details )) one by one...

I really dont know how to face it.

First I thought in bringing ALL details_Items (datafieldtext = id_di and datavaluetext=Details_names) into a dataview.
And then "somohow?" filter this dataview according with the Array previuosly splited by me with a For each function.

Then I thought "Perhaps" there is a simpliest way to do that using SQL views, o advanced SQL QUERYS.

and Finally I thought that creating a VIEW in for both TABLES would be great.

The point is that, neither 1,2,3 options, honestly , I dont know how to face them.

Thanks in advance, apologise my "rude" English grammar.

LUCAS ( From Argentina )

Are you saying that you are storing numeric values as a comma separated list in the DETAILS column? This is very bad database design. You should use a link table instead. Create a new table called DETAIL_IDS ?with two columns:?ID_DET and DETAIL_ID

eg.

?ID_DET ???DETAIL_ID
?1 ?????????????1
?1 ?????????????2
?1 ?????????????3
?1 ?????????????4
?1 ?????????????5
?1 ?????????????6
?2 ?????????????4
?2 ?????????????6

etc.|||

This is a very bad way to store IDs in string field because of speed and problems you have now. If you do not wont to change your database structure you can do something like this:

declare @.lcCommand as varchar(8000)

SELECT @.lcCommand = 'SELECT * FROM DETAILS_NAME WHERE id_di in (' + DETAILS + ') '
from Details
where ID_Car=3 -- for car with ID 3

Exec (@.lcCommand)

This is not recommended way to execute select statement but in your case is probably the simplest way. Another way would be to create table returned function which will convert comma delimited string to table and just select from this table. The advantage is that you will not build command string which is safest, and store procedures will be precompiled so works faster.

Thanks

JPazgier

sql