Posted by Ed Murphy on 03/19/07 13:11

Giorgio wrote:

> Can someone tell me options to do this statment because this one does
> not work!
>
> SELECT Name FROM tbl_J
> WHERE J_ID IN
> (SELECT J1, J2, J3, J4, J5, J6
> FROM tbl_CJ
> WHERE CJ_ID =23515) ORDER BY Name

You probably want to re-design tbl_CJ from this:

CJ_ID | J1 | J2 | J3 | J4 | J5 | J6
------+----+----+----+----+----+----
23515 | 1 | 2 | 3 | 4 |null|null
23516 | 5 | 6 | 7 |null|null|null

to this:

CJ_ID | J_ID
------+-----
23515 | 1
23515 | 2
23515 | 3
23515 | 4
23516 | 5
23516 | 6
23516 | 7

in which case the query becomes simple:

select j.Name
from tbl_J j
join tbl_CJ cj on cj.J_ID = j.J_ID
where cj.CJ_ID = 23515
order by j.Name

and, as an extra added bonus, you are no longer limited to a maximum of
six tbl_J records per tbl_CJ record.

Failing that, here is one of several ways to do it:

select Name
from tbl_J
where J_ID in (select J1 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J2 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J3 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J4 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J5 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J6 from tbl_CJ where CJ_ID = 23515)
order by Name

[Back to original message]