select id at a location and also if at a different location

[borisbe]

TABLE A
ID
term
crse_nbr

TABLE B
term
crse_nbr
session
title

TABLE C
term
crse_nbr
session
location
========================
select TABLE A.ID, TABLE B.term, TABLE B.title, TABLE C.location from
TABLE A,
TABLE B,
TABLE C,
where
TABLE A.term=TABLE B.term
and
TABLE A.crse_nbr=TABLE B.crse_nbr
and
TABLE B.term=TABLE C.term
and
TABLE B.crse_nbr=TABLE C.crse_nbr
and
TABLE A.session=TABLE C.session
and
TABLE C.location in (‘PC’,’BT’)
========================

The query so far just retrieves:

ID Location
1 PC

2 PC
2 BT

3 BT

========================
Some of these IDs are also at different location(s) so I really I need:

ID Location
1 PC
1 ON
1 DC

2 PC
2 BT

3 BT
3 SD

Thanks for helping me.

 

[SantaMufasa]

Borisbe,

It is always best practice to post the CREATE TABLE... and INSERT INTO... commands that represent your situation. Since you have not shown us what your data look like, it is difficult for us to recommend code to produce your desired results.

Therefore, please post the CREATE TABLE... and INSERT INTO... code you want us to use.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[borisbe]

Sorry, long day. I work with a program where I only can do select statements so I click on the tables needed and add the criteria like location in list PC, BT, etc.

TABLE A
ID term crse_nbr
1 0094 1
1 0094 5
1 0094 6

2 0094 1
2 0094 7

3 0094 8
3 0094 9

TABLE B
term crse_nbr session title
0094 1 1 test
0094 5 1 test1
0094 6 1 test2
0094 7 1 test3
0094 8 1 test4
0094 9 1 test5


term crse_nbr session location
0094 1 1 PC
0094 5 1 ON
0094 6 1 DC
0094 7 1 BT
0094 8 1 BT
0094 9 1 SD

select TABLE A.ID, TABLE B.term, TABLE B.title, TABLE C.location from
TABLE A,
TABLE B,
TABLE C,
where
TABLE A.term=TABLE B.term
and
TABLE A.crse_nbr=TABLE B.crse_nbr
and
TABLE B.term=TABLE C.term
and
TABLE B.crse_nbr=TABLE C.crse_nbr
and
TABLE A.session=TABLE C.session
and
TABLE C.location in ('PC','BT')

The query so far just retrieves:

ID term title location
1  0094 test PC

2  0094 test PC
2  0094 test3 BT

3  0094 test4 BT

========================
Some of these IDs are also at different location(s) so I really I need:

ID term title location
1 0094 test PC
1 0094 test1 ON
1 0094 test2 DC

2 0094 test PC
2 0094 test3 BT

3 0094 test4 BT
3 0094 test5 SD

Thanks for helping me mufasa. I really appreciate it.I hope this is a better explanation.

 

[ChrisHunt]

Ermm... remove this line?

and
TABLE C.location in ('PC','BT')

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[borisbe]

Sorry, I might have not explained what I need correctly. How do I replace that line because I need IDs at those locations plus if one of those IDs are also at other locations? If I just remove it then it will give me every ID in the table even the ones that might just be at a location like SD, etc.

Thanks

 

[ChrisHunt]

Ah, I see. Would have helped if you'd said that in the first place - we're not mind readers.

Try something like this:

SELECT a.ID, b.term, b.title, c.location
FROM   TABLE_A a,
       TABLE_B b,
       TABLE_C c,
WHERE  a.term = b.term
AND    a.crse_nbr = b.crse_nbr
AND    b.term = c.term
AND    b.crse_nbr = c.crse_nbr
AND    b.session = c.session
AND EXISTS (SELECT 99
            FROM   table_c c2
            WHERE  c2.session = b.session
            AND    c2.crse_nbr = b.crse_nbr
            AND    c2.term = b.term
            AND    c2.location IN ('PC','BT'))

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[borisbe]

Thanks Chris for helping me, even though, I didn't explain what I needed correctly in the beginning. I tried your statement and I just got rows with PC and BT for the IDs so for example of ID #1, I didn't get a row for ON or DC locations.

 

[borisbe]

I got my query working so no need to reply to my last post. thanks