Left Join - 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

Transact-SQL (2000)

Left Join

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2008-11-19 : 17:20:17

SELECT Date, Hours, BeginTime, EndTime, Narrative, Category, SubCategory
FROM Daysummary
LEFT JOIN Activity ON Daysummary.DaySummaryID = Activity.DaySummaryID
LEFT JOIN Category ON Activity.CategoryID = Category.CategoryID
LEFT JOIN SubCategory ON Activity.SubCategoryID = SubCategory.SubCategoryID

DaySummary Table
Date, Hours, BeginTime, EndTime, Narrative

Activity Table
DaySummaryID, CategoryID, SubCategoryID

Category Table
CategoryID, Category

SubCategory Table
SubCategoryID, SubCategory

There are many records in the activity table relative to the DaySummary table.

How do I get the activity, categories and subcategories and return one DaySummary record? I am using a left join because some days might not have activity but I want to show that.

SimpleSQL
Yak Posting Veteran

85 Posts

Posted - 2008-11-19 : 21:46:44

I am unsure what is the requirement here. Can you explain more with some sample data maybe.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-20 : 04:13:06

quote:
Originally posted by forwheeler
...
...
There are many records in the activity table relative to the DaySummary table.

How do I get the activity, categories and subcategories and return one DaySummary record? I am using a left join because some days might not have activity but I want to show that.

Out of these *many* records in activity which 1 do you want to bring back? -- Sample data and expected output please!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 05:58:01

quote:
Originally posted by forwheeler

SELECT Date, Hours, BeginTime, EndTime, Narrative, Category, SubCategory
FROM Daysummary
LEFT JOIN Activity ON Daysummary.DaySummaryID = Activity.DaySummaryID
LEFT JOIN Category ON Activity.CategoryID = Category.CategoryID
LEFT JOIN SubCategory ON Activity.SubCategoryID = SubCategory.SubCategoryID

DaySummary Table
Date, Hours, BeginTime, EndTime, Narrative

Activity Table
DaySummaryID, CategoryID, SubCategoryID

Category Table
CategoryID, Category

SubCategory Table
SubCategoryID, SubCategory

There are many records in the activity table relative to the DaySummary table.

How do I get the activity, categories and subcategories and return one DaySummary record? I am using a left join because some days might not have activity but I want to show that.

when you want to return only a record for daysummary you've only two choices
either bring values from Activity as comma seperated along with single record of DaySummary or return aggregated results from activity table like min() or max() or count() values
can you illustrate with some sample output what you're looking at?

forwheeler
Starting Member

44 Posts

Posted - 2008-11-20 : 09:26:39

I want to send this data to reporting services 2005 so I wanted to pre-format it as best I could before formatting it in SSRS but this might be futile.

This is the current output:

10/6/2008 0:00 9 10/6/2008 9:00 10/6/2008 18:30 AT instructors meeting at RO License Checks NULL 4
10/6/2008 0:00 9 10/6/2008 9:00 10/6/2008 18:30 AT instructors meeting at RO Violation Investigations Violations reported/investgated (includes CAP) 1
10/10/2008 0:00 14 10/10/2008 8:00 10/10/2008 22:00 ATL 321 code 4 License Checks NULL 28
10/10/2008 0:00 14 10/10/2008 8:00 10/10/2008 22:00 ATL 321 code 4 OHV OHV checks/contacts (ATV, UTV, motorbike) 9
10/10/2008 0:00 14 10/10/2008 8:00 10/10/2008 22:00 ATL 321 code 4 Violation Investigations Violations reported/investgated (includes CAP) 2

Of course most of the fields in the result set are duplicated since I am using a left join. I will need to format this in reporting services I think with group bys.

forwheeler
Starting Member

44 Posts

Posted - 2008-11-24 : 13:20:29

Changing my requirements some - Here is my query with a number field added:

SELECT Date, Hours, BeginTime, EndTime, Narrative, Category, SubCategory, Number
FROM Daysummary
LEFT JOIN Activity ON Daysummary.DaySummaryID = Activity.DaySummaryID
LEFT JOIN Category ON Activity.CategoryID = Category.CategoryID
LEFT JOIN SubCategory ON Activity.SubCategoryID = SubCategory.SubCategoryID
GROUP BY Date, Hours, BeginTime, EndTime, Narrative, Category, SubCategory, Number

My tables:

DaySummary Table
Date, Hours, BeginTime, EndTime, Narrative

Activity Table
DaySummaryID, CategoryID, SubCategoryID, Number

Category Table
CategoryID, Category

SubCategory Table
SubCategoryID, SubCategory

I want to change my query to get all records from the DaySummary table and then only the activities with a number > 0

Required output:
10/5/2008 0:00 0 10/5/2008 0:00 10/5/2008 0:00 off
10/5/2008 0:00 1 10/5/2008 22:30 10/5/2008 23:30 time, email
10/5/2008 0:00 8 10/5/2008 8:30 10/5/2008 22:30 patrol 40 . Patrol on 2 properties Calls for Service Phone call 2
10/6/2008 0:00 1 10/6/2008 9:00 10/6/2008 10:00 Enter timesheet
10/6/2008 0:00 6.5 10/6/2008 8:00 10/6/2008 14:30 Techniques Planning
10/6/2008 0:00 4 10/6/2008 8:00 10/6/2008 18:00 License Checks 6

If I filter by the number > 0, I will not get all the DaySummary records. How can I write the query to get all the DaySummary records but only the activities with number > 0

forwheeler
Starting Member

44 Posts

Posted - 2008-11-24 : 14:56:08

Adding HAVING Number > 0 fixes it.

forwheeler
Starting Member

44 Posts

Posted - 2008-11-24 : 16:13:00

I spoke too soon. HAVING doesn't fix it. While I filter out records with activity = 0, I still want all the DaySummary records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 01:21:59

did you mean this?


SELECT Date, Hours, BeginTime, EndTime, Narrative, Category, SubCategory, Number
FROM Daysummary
LEFT JOIN Activity ON Daysummary.DaySummaryID = Activity.DaySummaryID AND Activity.Number >0
LEFT JOIN Category ON Activity.CategoryID = Category.CategoryID
LEFT JOIN SubCategory ON Activity.SubCategoryID = SubCategory.SubCategoryID
GROUP BY Date, Hours, BeginTime, EndTime, Narrative, Category, SubCategory, Number

forwheeler
Starting Member

44 Posts

Posted - 2008-11-25 : 08:19:19

Yes that works. The answer looks simple after I see it.
Thanks for the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 12:08:42

No problem
you're welcome