Re: Subqueries and Primary Keys

From: Karsten Farell <kfarrell_at_medimpact.com>
Date: Thu, 10 Oct 2002 21:40:53 GMT
Message-ID: <Ftmp9.2896$du2.170750142@newssvr14.news.prodigy.com>

Dennis Webb wrote:
> I've tried doing simple joins, of the sort you suggest, but it still
> doesn't give me the correct rows. Instead, I still get a row
> representing every row on Table_C. But you will notice I have an OR
> in my SQL, since I want a matching record from Table_B OR from Table_C
> (one of the two matching Table_A).

Pass me my reading glasses! I missed that 'OR' in your sql. I am not sure what is happening in your case. Maybe you can give an example of your data and expected output (scrubbed, of course, since I see you are posting from a .mil domain ... though that's not always accurate since my work uses PacBell ... now SBC ... and they use a Prodigy news server).

Here are some things to look into:

1. If you want to return one or the other, but not both:

where table_a.ssn = '123456789'
and table_a.date = (

         select date from table_b where ssn = '123456789'
         union
         select date from table_c where ssn = '123456789'
       )

2. Look into "inline views" - eg,

select a.ssn, a.date, b.age, c.sex
from table_a a,

        (
          select ssn, date, age
          from   table_b
        ) b,
        (
          select ssn, date, sex
          from   table_c
        ) c

where a.ssn = '123456789'
and (

           (b.ssn = a.ssn and b.date = a.date)
        or (c.ssn = a.ssn and c.date = a.date)
        )

You might need to add a where clause to the inline views.

I'm not sure if I got the syntax of the above correct, since I'm typing from memory ... but it might lead you in the right direction. Look up 'union' and 'inline views' in your doc set for details. Received on Thu Oct 10 2002 - 16:40:53 CDT