Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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

borisbe (TechnicalUser) (OP)
28 Jul 09 15:12
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 (TechnicalUser)
28 Jul 09 15:52
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.

santaMufasa
(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 (TechnicalUser) (OP)
28 Jul 09 17:00
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 (Programmer)
29 Jul 09 5:09
Ermm... remove this line?

CODE

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

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

borisbe (TechnicalUser) (OP)
29 Jul 09 10:49
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 (Programmer)
30 Jul 09 3:46
Ah, I see. Would have helped if you'd said that in the first place - we're not mind readers.

Try something like this:

CODE

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 (TechnicalUser) (OP)
31 Jul 09 17:09
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 (TechnicalUser) (OP)
1 Aug 09 22:04
I got my query working so no need to reply to my last post. thanks

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close