SEO

#1 (**permalink**) 02-28-2008, 09:48 AM

I have a table that has a column of partids. They are seperated with
commas. I need to select the partids from certain columns and use
them to select from another table. They are the ids for the subjects
table. If I grab the ids from the table then do another query using
them in a where id in clause it works fine. But I can't use a
subquery like where id in (select partids from collections where id =
346). It only uses the first number in the partids and that is it.
Is there a way around having to do 2 queries.

#2 (**permalink**) 02-28-2008, 09:48 AM

Extremest wrote:
> I have a table that has a column of partids. They are seperated with
> commas. I need to select the partids from certain columns and use
> them to select from another table. They are the ids for the subjects
> table. If I grab the ids from the table then do another query using
> them in a where id in clause it works fine. But I can't use a
> subquery like where id in (select partids from collections where id =
> 346). It only uses the first number in the partids and that is it.
> Is there a way around having to do 2 queries.
>

Redesign your tables and have a multi-to-multi or maybe one-to-multi table
which you use to connect things from one table to another instead of your
column with the comma separated IDs.

--

//Aho

#3 (**permalink**) 02-28-2008, 09:48 AM

Extremest wrote:
> I have a table that has a column of partids. They are seperated with
> commas. I need to select the partids from certain columns and use
> them to select from another table. They are the ids for the subjects
> table. If I grab the ids from the table then do another query using
> them in a where id in clause it works fine. But I can't use a
> subquery like where id in (select partids from collections where id =
> 346). It only uses the first number in the partids and that is it.
> Is there a way around having to do 2 queries.

And after you've done what Aho suggested, use a JOIN instead of a subquery,
it is many many many many times more efficient.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Where in does not work for this