|
|
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
Navigation:
[Reply to this message]
|