tricky query - please help 
Author Message
 tricky query - please help

Hi,

I'm in way over my head here and would appreciate any help.  I've got

TableA (columns:  StatusCode & TableBID)
TableB (column: StatusCode)
TableC (columns: StatusCode & StatusDescription)

I want to print the StatusDescription of each TableA row AND the
StatusDescription of the associated StatusCode from the TableBID row of
TableB.  Too confusing?  I can do:

select a.TableBID, a.StatusCode, c.StatusDescription, b.TableBID,
b.StatusCode
from Tst_TableA a, Tst_TableB b, Tst_TableC c
where a.StatusCode = c.StatusCode

which yields the StatusDescription for a.StatusCode, but how can I get to
the StatusDescription of b.StatusCode ???

Thanks,
Deanna



Wed, 21 Jul 2004 01:26:43 GMT
 tricky query - please help

deanna,

what's the link between tableA and tableB?

with what you've posted, here is my guess...

select 'A' as tb, a.tableBID,a.statuscode,c.StatusDescription
from tableA a left outer join tableC c on a.statuscode=c.statuscode
union all
select 'B',b.tableBID,b.statuscode,c.StatusDescription
from tableB b left outer join tableC c on a.statuscode=c.statuscode

--
-oj
http://rac4sql.home.attbi.com


Quote:
> Hi,

> I'm in way over my head here and would appreciate any help.  I've got

> TableA (columns:  StatusCode & TableBID)
> TableB (column: StatusCode)
> TableC (columns: StatusCode & StatusDescription)

> I want to print the StatusDescription of each TableA row AND the
> StatusDescription of the associated StatusCode from the TableBID row of
> TableB.  Too confusing?  I can do:

> select a.TableBID, a.StatusCode, c.StatusDescription, b.TableBID,
> b.StatusCode
> from Tst_TableA a, Tst_TableB b, Tst_TableC c
> where a.StatusCode = c.StatusCode

> which yields the StatusDescription for a.StatusCode, but how can I get to
> the StatusDescription of b.StatusCode ???

> Thanks,
> Deanna



Wed, 21 Jul 2004 01:39:50 GMT
 tricky query - please help
Hi oj,

   I used a slightly modified version of what you sent  and it almost works,
except it returns the results as 2 rows (see below).  Is there any way to
get all the info into ONE row?

Thanks!
Deanna

======== QUERY =================================================
select  a.tableBID, a.statuscode, c.StatusDescription
from tst_tableA a left outer join tst_tableC c
on a.statuscode=c.statuscode
union all
select  b.tableBID, b.statuscode, c.StatusDescription
from tst_tableB b left outer join tst_tableC c
on b.statuscode=c.statuscode

======= RESULTS =================================================
TableID StatusCode StatusDirection
1                  1           StatusTableA
2                  2           StatusTableB

When tableA consists of (BTableID=1, StatusCode=1) and TableB consists of
(StatusCode=2)

(where TableA has 1 row (TableBID=1, Status=1))

Quote:
> deanna,

> what's the link between tableA and tableB?

> with what you've posted, here is my guess...

> select 'A' as tb, a.tableBID,a.statuscode,c.StatusDescription
> from tableA a left outer join tableC c on a.statuscode=c.statuscode
> union all
> select 'B',b.tableBID,b.statuscode,c.StatusDescription
> from tableB b left outer join tableC c on a.statuscode=c.statuscode

> --
> -oj
> http://rac4sql.home.attbi.com



> > Hi,

> > I'm in way over my head here and would appreciate any help.  I've got

> > TableA (columns:  StatusCode & TableBID)
> > TableB (column: StatusCode)
> > TableC (columns: StatusCode & StatusDescription)

> > I want to print the StatusDescription of each TableA row AND the
> > StatusDescription of the associated StatusCode from the TableBID row of
> > TableB.  Too confusing?  I can do:

> > select a.TableBID, a.StatusCode, c.StatusDescription, b.TableBID,
> > b.StatusCode
> > from Tst_TableA a, Tst_TableB b, Tst_TableC c
> > where a.StatusCode = c.StatusCode

> > which yields the StatusDescription for a.StatusCode, but how can I get
to
> > the StatusDescription of b.StatusCode ???

> > Thanks,
> > Deanna



Wed, 21 Jul 2004 04:16:13 GMT
 tricky query - please help
deanna,

it really makes no sense to combine 2 rows into one if there isn't a
relationship between them (tableA and tableB)...howerver, i am including a
query that would give you a head start (note: this is based on what you've
posted.).

select  a.tableBID, a.statuscode, c.StatusDescription,
tbBID=(select  b.tableBID
from tst_tableB b left outer join tst_tableC c
on b.statuscode=c.statuscode),
stat=(select b.statuscode
from tst_tableB b left outer join tst_tableC c
on b.statuscode=c.statuscode),
descript=(select  c.StatusDescription
from tst_tableB b left outer join tst_tableC c
on b.statuscode=c.statuscode)
from tst_tableA a left outer join tst_tableC c
on a.statuscode=c.statuscode

--
-oj
http://rac4sql.home.attbi.com


Quote:
> Hi oj,

>    I used a slightly modified version of what you sent  and it almost
works,
> except it returns the results as 2 rows (see below).  Is there any way to
> get all the info into ONE row?

> Thanks!
> Deanna

> ======== QUERY =================================================
> select  a.tableBID, a.statuscode, c.StatusDescription
> from tst_tableA a left outer join tst_tableC c
> on a.statuscode=c.statuscode
> union all
> select  b.tableBID, b.statuscode, c.StatusDescription
> from tst_tableB b left outer join tst_tableC c
> on b.statuscode=c.statuscode

> ======= RESULTS =================================================
> TableID StatusCode StatusDirection
> 1                  1           StatusTableA
> 2                  2           StatusTableB

> When tableA consists of (BTableID=1, StatusCode=1) and TableB consists of
> (StatusCode=2)

> (where TableA has 1 row (TableBID=1, Status=1))


> > deanna,

> > what's the link between tableA and tableB?

> > with what you've posted, here is my guess...

> > select 'A' as tb, a.tableBID,a.statuscode,c.StatusDescription
> > from tableA a left outer join tableC c on a.statuscode=c.statuscode
> > union all
> > select 'B',b.tableBID,b.statuscode,c.StatusDescription
> > from tableB b left outer join tableC c on a.statuscode=c.statuscode

> > --
> > -oj
> > http://rac4sql.home.attbi.com



> > > Hi,

> > > I'm in way over my head here and would appreciate any help.  I've got

> > > TableA (columns:  StatusCode & TableBID)
> > > TableB (column: StatusCode)
> > > TableC (columns: StatusCode & StatusDescription)

> > > I want to print the StatusDescription of each TableA row AND the
> > > StatusDescription of the associated StatusCode from the TableBID row
of
> > > TableB.  Too confusing?  I can do:

> > > select a.TableBID, a.StatusCode, c.StatusDescription, b.TableBID,
> > > b.StatusCode
> > > from Tst_TableA a, Tst_TableB b, Tst_TableC c
> > > where a.StatusCode = c.StatusCode

> > > which yields the StatusDescription for a.StatusCode, but how can I get
> to
> > > the StatusDescription of b.StatusCode ???

> > > Thanks,
> > > Deanna



Wed, 21 Jul 2004 04:50:54 GMT
 tricky query - please help
There IS a relationship since tableA holds tableBID which is a pointer to a
row in tableB.  Your example works perfectly.

Thank you SO much!
Deanna


Quote:
> deanna,

> it really makes no sense to combine 2 rows into one if there isn't a
> relationship between them (tableA and tableB)...howerver, i am including a
> query that would give you a head start (note: this is based on what you've
> posted.).

> select  a.tableBID, a.statuscode, c.StatusDescription,
> tbBID=(select  b.tableBID
> from tst_tableB b left outer join tst_tableC c
> on b.statuscode=c.statuscode),
> stat=(select b.statuscode
> from tst_tableB b left outer join tst_tableC c
> on b.statuscode=c.statuscode),
> descript=(select  c.StatusDescription
> from tst_tableB b left outer join tst_tableC c
> on b.statuscode=c.statuscode)
> from tst_tableA a left outer join tst_tableC c
> on a.statuscode=c.statuscode

> --
> -oj
> http://rac4sql.home.attbi.com



> > Hi oj,

> >    I used a slightly modified version of what you sent  and it almost
> works,
> > except it returns the results as 2 rows (see below).  Is there any way
to
> > get all the info into ONE row?

> > Thanks!
> > Deanna

> > ======== QUERY =================================================
> > select  a.tableBID, a.statuscode, c.StatusDescription
> > from tst_tableA a left outer join tst_tableC c
> > on a.statuscode=c.statuscode
> > union all
> > select  b.tableBID, b.statuscode, c.StatusDescription
> > from tst_tableB b left outer join tst_tableC c
> > on b.statuscode=c.statuscode

> > ======= RESULTS =================================================
> > TableID StatusCode StatusDirection
> > 1                  1           StatusTableA
> > 2                  2           StatusTableB

> > When tableA consists of (BTableID=1, StatusCode=1) and TableB consists
of
> > (StatusCode=2)

> > (where TableA has 1 row (TableBID=1, Status=1))


> > > deanna,

> > > what's the link between tableA and tableB?

> > > with what you've posted, here is my guess...

> > > select 'A' as tb, a.tableBID,a.statuscode,c.StatusDescription
> > > from tableA a left outer join tableC c on a.statuscode=c.statuscode
> > > union all
> > > select 'B',b.tableBID,b.statuscode,c.StatusDescription
> > > from tableB b left outer join tableC c on a.statuscode=c.statuscode

> > > --
> > > -oj
> > > http://rac4sql.home.attbi.com



> > > > Hi,

> > > > I'm in way over my head here and would appreciate any help.  I've
got

> > > > TableA (columns:  StatusCode & TableBID)
> > > > TableB (column: StatusCode)
> > > > TableC (columns: StatusCode & StatusDescription)

> > > > I want to print the StatusDescription of each TableA row AND the
> > > > StatusDescription of the associated StatusCode from the TableBID row
> of
> > > > TableB.  Too confusing?  I can do:

> > > > select a.TableBID, a.StatusCode, c.StatusDescription, b.TableBID,
> > > > b.StatusCode
> > > > from Tst_TableA a, Tst_TableB b, Tst_TableC c
> > > > where a.StatusCode = c.StatusCode

> > > > which yields the StatusDescription for a.StatusCode, but how can I
get
> > to
> > > > the StatusDescription of b.StatusCode ???

> > > > Thanks,
> > > > Deanna



Wed, 21 Jul 2004 12:02:00 GMT
 tricky query - please help
deanna,

although you have gotten it to work :) ...i would suggest you post the
simplied ddl (create table) here. as what i have given you IS not the most
efficient way and probably would give you erroneous result in some cases.

--
-oj
http://rac4sql.home.attbi.com


Quote:
> There IS a relationship since tableA holds tableBID which is a pointer to
a
> row in tableB.  Your example works perfectly.

> Thank you SO much!
> Deanna



> > deanna,

> > it really makes no sense to combine 2 rows into one if there isn't a
> > relationship between them (tableA and tableB)...howerver, i am including
a
> > query that would give you a head start (note: this is based on what
you've
> > posted.).

> > select  a.tableBID, a.statuscode, c.StatusDescription,
> > tbBID=(select  b.tableBID
> > from tst_tableB b left outer join tst_tableC c
> > on b.statuscode=c.statuscode),
> > stat=(select b.statuscode
> > from tst_tableB b left outer join tst_tableC c
> > on b.statuscode=c.statuscode),
> > descript=(select  c.StatusDescription
> > from tst_tableB b left outer join tst_tableC c
> > on b.statuscode=c.statuscode)
> > from tst_tableA a left outer join tst_tableC c
> > on a.statuscode=c.statuscode

> > --
> > -oj
> > http://rac4sql.home.attbi.com



> > > Hi oj,

> > >    I used a slightly modified version of what you sent  and it almost
> > works,
> > > except it returns the results as 2 rows (see below).  Is there any way
> to
> > > get all the info into ONE row?

> > > Thanks!
> > > Deanna

> > > ======== QUERY =================================================
> > > select  a.tableBID, a.statuscode, c.StatusDescription
> > > from tst_tableA a left outer join tst_tableC c
> > > on a.statuscode=c.statuscode
> > > union all
> > > select  b.tableBID, b.statuscode, c.StatusDescription
> > > from tst_tableB b left outer join tst_tableC c
> > > on b.statuscode=c.statuscode

> > > ======= RESULTS =================================================
> > > TableID StatusCode StatusDirection
> > > 1                  1           StatusTableA
> > > 2                  2           StatusTableB

> > > When tableA consists of (BTableID=1, StatusCode=1) and TableB consists
> of
> > > (StatusCode=2)

> > > (where TableA has 1 row (TableBID=1, Status=1))


> > > > deanna,

> > > > what's the link between tableA and tableB?

> > > > with what you've posted, here is my guess...

> > > > select 'A' as tb, a.tableBID,a.statuscode,c.StatusDescription
> > > > from tableA a left outer join tableC c on a.statuscode=c.statuscode
> > > > union all
> > > > select 'B',b.tableBID,b.statuscode,c.StatusDescription
> > > > from tableB b left outer join tableC c on a.statuscode=c.statuscode

> > > > --
> > > > -oj
> > > > http://rac4sql.home.attbi.com



> > > > > Hi,

> > > > > I'm in way over my head here and would appreciate any help.  I've
> got

> > > > > TableA (columns:  StatusCode & TableBID)
> > > > > TableB (column: StatusCode)
> > > > > TableC (columns: StatusCode & StatusDescription)

> > > > > I want to print the StatusDescription of each TableA row AND the
> > > > > StatusDescription of the associated StatusCode from the TableBID
row
> > of
> > > > > TableB.  Too confusing?  I can do:

> > > > > select a.TableBID, a.StatusCode, c.StatusDescription, b.TableBID,
> > > > > b.StatusCode
> > > > > from Tst_TableA a, Tst_TableB b, Tst_TableC c
> > > > > where a.StatusCode = c.StatusCode

> > > > > which yields the StatusDescription for a.StatusCode, but how can I
> get
> > > to
> > > > > the StatusDescription of b.StatusCode ???

> > > > > Thanks,
> > > > > Deanna



Wed, 21 Jul 2004 12:19:53 GMT
 tricky query - please help
Good discussion! Deanna, let me know if you want me to further research the
topic.

Hope this helps,
Robert Ware
=================================================
*  This posting is provided "AS IS" with no warranties, and confers no
rights.
*  Please do not send email directly to this alias.Respond to newsgroup

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit,
please visit http://www.microsoft.com/security.

--------------------

Quote:

>Newsgroups: microsoft.public.sqlserver.programming
>Subject: tricky query - please help
>Date: Fri, 01 Feb 2002 17:26:43 GMT

>Hi,

>I'm in way over my head here and would appreciate any help.  I've got

>TableA (columns:  StatusCode & TableBID)
>TableB (column: StatusCode)
>TableC (columns: StatusCode & StatusDescription)

>I want to print the StatusDescription of each TableA row AND the
>StatusDescription of the associated StatusCode from the TableBID row of
>TableB.  Too confusing?  I can do:

>select a.TableBID, a.StatusCode, c.StatusDescription, b.TableBID,
>b.StatusCode
>from Tst_TableA a, Tst_TableB b, Tst_TableC c
>where a.StatusCode = c.StatusCode

>which yields the StatusDescription for a.StatusCode, but how can I get to
>the StatusDescription of b.StatusCode ???

>Thanks,
>Deanna



Sat, 24 Jul 2004 03:19:51 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. A Tuff Tricky One - Please Help with Queries

2. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

3. Tricky Update statement need help please...

4. Tricky problem, help please!

5. challenge..very tricky sql statement..please help

6. APPROACH QUERY-PLEASE PLEASE PLEASE HELP

7. Help with a tricky SQL query

8. Need help with a tricky query

9. Tricky Query help

10. HELP: Tricky query....

11. Help with tricky SQL query


 
Powered by phpBB® Forum Software