Hi All,

I am hoping some of your talented minds can me with a query. I
have two tables that look like this.

Table1

UID--NAME--PROGRAM--COMPANY

Table2

UID--STATUS--ISMGR

I want to get all the Unique PROGRAM names that have a particular
STATUS is there way to do this? I UID in Table1 may or may not exist
in Table2.

like

select distinct t1.program from Table1 as t1 JOIN on Table2 as t2
with where STATUS ='X' and t1.uid = t2.uid

Anyone have a clue?

Thanks,
-SJ

SJ,

SELECT A.UID, A.PROGRAM, B.STATUS
FROM Table1 A JOIN Table2 B
ON A.UID = B.UID
WHERE B.STATUS = SomeValue
GROUP BY A.UID, A.PROGRAM, B.STATUS

The GROUP BY is in case there are multiple Table2 rows with the same status
for the same ID. Et cetera. (Logically, there should not be, but I don't
know your constraints.)

RLF

"SirCodesALot" <sjourdan@gmail.com> wrote in message
news:3c803eba-8fbf-4a21-8883-44fd7c51ac99@r66g2000hsg.googlegroups.com...
> Hi All,
>
> I am hoping some of your talented minds can me with a query. I
> have two tables that look like this.
>
> Table1
>
> UID--NAME--PROGRAM--COMPANY
>
> Table2
>
> UID--STATUS--ISMGR
>
> I want to get all the Unique PROGRAM names that have a particular
> STATUS is there way to do this? I UID in Table1 may or may not exist
> in Table2.
>
> like
>
> select distinct t1.program from Table1 as t1 JOIN on Table2 as t2
> with where STATUS ='X' and t1.uid = t2.uid
>
> Anyone have a clue?
>
> Thanks,
> -SJ

On Tue, 9 Sep 2008 16:24:54 -0700 (PDT), SirCodesALot
<sjourdan@gmail.com> wrote:

>I want to get all the Unique PROGRAM names that have a particular
>STATUS is there way to do this? I UID in Table1 may or may not exist
>in Table2.
>
>like
>
>select distinct t1.program from Table1 as t1 JOIN on Table2 as t2
>with where STATUS ='X' and t1.uid = t2.uid

SELECT distinct
t1.program
FROM Table1 as t1
LEFT OUTER
JOIN Table2 as t2
ON t1.STATUS = 'X'
AND t1.uid = t2.uid

Roy Harvey
Beacon Falls, CT

On Sep 9, 6:34pm, "Russell Fields" <russellfie...@nomail.com> wrote:
> SJ,
>
> SELECT A.UID, A.PROGRAM, B.STATUS
> FROM Table1 A JOIN Table2 B
> ON A.UID = B.UID
> WHERE B.STATUS = SomeValue
> GROUP BY A.UID, A.PROGRAM, B.STATUS
>
> The GROUP BY is in case there are multiple Table2 rows with the same status
> for the same ID. Et cetera. (Logically, there should not be, but I don't
> know your constraints.)
>
> RLF
>
> "SirCodesALot" <sjour...@gmail.com> wrote in message
>
> news:3c803eba-8fbf-4a21-8883-44fd7c51ac99@r66g2000hsg.googlegroups.com...
>
>
>
> > Hi All,
>
> > I am hoping some of your talented minds can me with a query. I
> > have two tables that look like this.
>
> > Table1
>
> > UID--NAME--PROGRAM--COMPANY
>
> > Table2
>
> > UID--STATUS--ISMGR
>
> > I want to get all the Unique PROGRAM names that have a particular
> > STATUS is there way to do this? I UID in Table1 may or may not exist
> > in Table2.
>
> > like
>
> > select distinct t1.program from Table1 as t1 JOIN on Table2 as t2
> > with where STATUS ='X' and t1.uid = t2.uid
>
> > Anyone have a clue?
>
> > Thanks,
> > -SJ- Hide quoted text -
>
> - Show quoted text -

Thanks alot! Wow that was fast. I really appreciate it.