Select only certain records

[kernal]

I hope I can explain my problem and not exclude anything in this message:

I have to retrieve records only if a person is in a certain area.

Table A
person_id
term
charge_descr
amount

Table B
person_id
term
class_number
area

What I have so far:

Select a.person_id, a.term, b.class_number, a.charge_descr, a.amount from
table a,
table b
where
a.person_id=b.person_id
and
a.term=b.term (used left outer joins)
and
a.term='F06'

Results so far:

person_id term class_number charge_descr amount

1 F06 6988 tuit 50
1 F06 fee 5

Problem:

There are different areas where a person can be charged so I only need to retrieve a person in a certain area and the area is in table b.

So only retrieve students in area Testing. I’ve tried just adding where area='Testing' but then of course, it doesn’t give me the fee data.

Thanks for the help.

 

[SantaMufasa]

Try this code that uses an additional condition:

Select a.person_id
      ,a.term
      ,b.class_number
      ,a.charge_descr
      ,a.amount
  from table_a a,table_b b
 where a.person_id=b.person_id
   and a.term=b.term (used left outer joins)
   and a.term='F06'
   and exists (select 'x'
                 from table_b c
                where area = 'Testing'
                  and a.person_id = c.person_id)
/

Since you did not include any "CREATE TABLE..." and "INSERT INTO..." statements, I did not test my code assertion, but I believe it should work for you.

Let us know.

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.”

 

[kernal]

Sorry, I messed this all up. Here are the tables and what I have so far. I hope you can still help me. Thanks Dave

Table A
a.person_id
a.term
a.class_number
a.charge_descr
a.amount

Table B
b.person_id
b.term
b.class_number
b.status
b.session

Table C
c.term
c.class_number
c.title,
c.area
c.session


Select a.person_id, a.term, a.class_number, a.charge_descr, a.amount, b.status, c.title, c.area from
table a,
table b,
table c

where

a.person_id=b.person_id
and
a.term=b.term
and
a.class_number=b.class_number
and

b.term=c.term
and
b.class_number=c.class_number
and
b.session=c.session (outer joins on all)

and

b.status='active'
and
a.term='F06'

 

[SantaMufasa]

Kernal,

Not a problem, but, given the prototype of the code (which I posted earlier) to do what you wanted, can you:[ul][li]Formulate and test code to do what you want[/li][li]Post code, along with the results, here[/li][li]Then assert any issues or questions you have along with your code and results?[/li][/ul]

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.”

 

[kernal]

It's including all areas for a person instead of just testing. Here is the statement I've been using:

SELECT a.person_id, a.term, a.class_number, a.charge_descr, a.amount, b.status, c.title, c.area FROM
fee a,
person b,
class c

WHERE

a.person_id=b.person_id
AND a.term=b.term
AND a.class_number=b.class_number

AND b.term=c.term
AND b.class_number=c.class_number
AND b.session=c.session (outer joins on all)

AND b.status='active'
AND a.term='F06'

AND EXISTS (SELECT 'x'
FROM person d, class e
WHERE e.area = 'Testing'
AND d.person_id = a.person_id
AND d.term = e.term
AND d.class_number = e.class_number
AND d.session = e.session)

Results:

person_id term class_number charge_descr amount status title area

1 F06 6988 tuit 50 enrl test 1 testing
1 F06 fee 5
1 F06 1400 tuit 10 drop account1 account

Thanks again.

 

[carp]

kernal -
How does the following work for you:

SELECT  a.person_id, a.term, a.class_number, a.charge_descr, a.amount, b.status, c.title, c.area
  FROM tableA a
       INNER JOIN (SELECT person_id FROM tableB WHERE area = 'Testing') v
          ON a.person_id = v.person_id
       LEFT OUTER JOIN tableB b
          ON a.person_id = b.person_id
             AND a.term = b.term
             AND a.class_number = b.class_number
       LEFT OUTER JOIN tableC c
          ON b.term = c.term
             AND b.class_number = c.class_number
             AND b.session = c.session
 WHERE b.status = 'active'
   AND a.term = 'F06';

I have not replicated your situation, so this is untested code. But I think it might work for you.

 

[kernal]

I really messed up this whole thing. I forgot some other elements involved so I'm going to have to create more than 1 query for my data and use my reporting application that I have to join the data. Thanks for replying to my message. Sorry that I took up your valuable time.