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

On Mar 19, 8:39 am, "Giorgio" <FJMarti... (AT) googlemail (DOT) com> wrote:
I won't go into why this suggests a problem with your model, but I
think you want something like this:

SELECT Name FROM tbl_J
WHERE J_ID IN
(SELECT J1
FROM tbl_CJ
WHERE CJ_ID =23515
UNION
SELECT J2
FROM tbl_CJ
WHERE CJ_ID =23515
....
UNION
SELECT J6
FROM tbl_CJ
WHERE CJ_ID =23515) ORDER BY Name

Alternatively you could OR them all together:

SELECT Name FROM tbl_J
WHERE J_ID IN
(SELECT J1
FROM tbl_CJ
WHERE CJ_ID =23515)
OR J_ID IN
(SELECT J1
FROM tbl_CJ
WHERE CJ_ID =23515)
....
OR J_ID IN
(SELECT J6
FROM tbl_CJ
WHERE CJ_ID =23515)

ORDER BY Name

Giorgio wrote:

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

I posted this in microsoft.public.sqlserver.programming in response to
the copy you posted there. In the future if you must post to multiple
groups, include all of them in the same copy of the message.

There are a number of ways to do this, but most become long and
complicated. This may be the simplest.

SELECT Name
FROM tbl_J as A
WHERE EXISTS
(SELECT * FROM tbl_CJ as B
WHERE B.CJ_ID = 23515
AND A.J_ID IN
(B.J1, B.J2, B.J3,
B.J4, B.J5, B.J6))
ORDER BY Name

Roy Harvey
Beacon Falls, CT

On 19 Mar 2007 05:39:37 -0700, "Giorgio" <FJMartinho (AT) googlemail (DOT) com>
wrote: