Both sides of table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Both sides of table

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2004-12-28 : 12:59:51

I have a query connecting various tables, but I need the number of specialtyID to be the same (but it can be dynamic depending on the tblID I need). I've tried various things, but would like advice.

Query:
SELECT TOP 100 PERCENT t.tblID, t.orderID, r.specialtyID, SUM(r.nbr) AS nbr, MAX(r.nbrType) AS nbrType, d.discipline, s.specialty, x.reason
FROM dbo.xTblExitReason x RIGHT OUTER JOIN
dbo.tblReport r ON x.reasonID = r.xReasonID LEFT OUTER JOIN
dbo.xTblDisciplines d RIGHT OUTER JOIN
dbo.xTblSpecialties s ON d.discID = s.discID ON r.specialtyID = s.specialtyID RIGHT OUTER JOIN
dbo.xTblReportTitles t ON r.titleID = t.xReportID AND t.tblID = 1
GROUP BY t.tblID, t.orderID, r.specialtyID, r.xReasonID, s.specialty, d.discipline, x.reason
ORDER BY t.tblID, t.orderID, d.discipline, s.specialty, x.reason

What I get:
tblID orderID specialtyID nbr nbrType discipline specialty reason
1          0         NULL     NULL NULL   NULL       NULL      NULL
1          1         1          54    1        text        text        NULL
1          1         2          8     1        text        text        NULL
1          1         3          47    1        text        text        NULL
1          1         13          4    1        text        text        NULL
1          1         14          2    1        text        text        NULL
1          2         1          8    1        text        text        NULL
1          2         2          1    1        text        text        NULL
1          2         3          28    1        text        text        NULL
1          2         13          2    1        text        text        NULL
1          3         1          4    1        text        text        NULL

What I need:
tblID orderID specialtyID nbr nbrType discipline specialty reason
1          0         NULL     NULL NULL   NULL       NULL      NULL
1          1         1          54    1        text        text        NULL
1          1         2          8     1        text        text        NULL
1          1         3          47    1        text        text        NULL
1          1         13          4    1        text        text        NULL
1          1         14          2    1        text        text        NULL
1          2         1          8    1        text        text        NULL
1          2         2          1    1        text        text        NULL
1          2         3          28    1        text        text        NULL
1          2         13          2    1        text        text        NULL
1          2         14          0    1        text        text        NULL

Thanks so much.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-28 : 21:46:22

Hey Janetb,
If I am understanding you right, you need the specialtyid to be sorted 1,2,3,13,14 and not 1,13,14,2,3 right? You need to move specialtyid up in your ORDER BY for the latter, and I think you will need to add leading zeros in order to get it 1,2,3,13,14...

There's never enough time to type code right,
but always enough time for a hotfix...

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-29 : 00:38:54

Hi Janet,
The only difference I see in the result sets is the last record. You are expecting

1          2         14          0    1        text        text        NULL

but you are getting

1          3         1          4    1        text        text        NULL

I don't think its your sort that is failing but the join condition.

Make sure xTblExitReason has data that relates to specialtyID 14. i.e,


    xTblExitReason.reasonID    = tblReport.xReasonID 
and xTblReportTitles.xReportID = tblReport.titleID
and xTblReportTitles.tblID     = 1 
and xTblReportTitles.orderID   = 2
and tblReport.specialtyID      = 14

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

janetb
Yak Posting Veteran

71 Posts

Posted - 2004-12-29 : 08:12:34

Hiya folks. My fault for not being explicit. I'll try again:

dbo.xTblDisciplines, dbo.xTblSpecialties, dbo.xTblExitReason,
dbo.xTblReportTitles are lookup tables...
dbo.tblReports contains the data

For the pull, I'll be selecting tblID=1. If I select distinct specialtyID from tblReport for tblID1 I would have 1,2,3,13,14. But, for each titleID (tables link titleID from dbo.tblReport to dbo.xTblReportTitles through xReportID) there may or may not be data for each specialtyID. If there is no data in tblReport, I need for a line item to appear (0 for null preferrably).

If you look at the last line of the first set of results, you'll see it goes to orderID=3, specialtyID=1 etc.; but if you look at the last line of the second table results, I need the missing line showing specialtyID 14.

Don't worry about the sort too much - I think I can handle that part; and, many many thanks for looking at this.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-29 : 23:45:26

Hmm..I think what we need are the DDLs and sample data for the tables...

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

janetb
Yak Posting Veteran

71 Posts

Posted - 2004-12-30 : 10:08:31

Hiya folks,
I got it (well, with a lot of help)! Here it is in case you're interested. Thanks so much for all of your time and effort

The following gives me what I need to pull into a recordset for display.

select distinct rs.orderID, rs.specialtyID, rs.nbr, f.lineTitle from dbo.xTblReportTitles f,
(select s.orderID, s.specialtyID, coalesce(sum(r.nbr),0) as nbr from dbo.tblReport as r join dbo.xtblReportTitles as t on t.xReportID = r.titleID and t.tblID = 1 right outer join
(select distinct r.specialtyID, t.orderID from dbo.tblReport as r cross join dbo.xtblReportTitles as t) as S
on s.specialtyID = r.specialtyID and s.orderID = t.orderID
group by s.orderID, s.specialtyID -- changed to s.
) as rs
where (f.orderID=rs.orderID) and (tblID=1) and
(rs.specialtyID in (select distinct specialtyID from dbo.tblReport where tblID=1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources