Which 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

General SQL Server Forums

New to SQL Server Programming

Which Join?

Author

Topic

spasticnapjerk
Starting Member

7 Posts

Posted - 2012-08-21 : 17:12:04

Hello everyone, my name is Drew and this is my first post. I work as a Remedy dba at a joint US-Honduran base in the middle of Honduras. I can do Remedy programming ok, but pure SQL I'm not good at.

I'm trying to write a query that will return some Help Desk records that will be summarized by department in an Excel pivot table. The problem is that my query doesn't return records for departments that have no records. But I need that data so the pivot table will show that the J9 department had 0 tickets for that period. Shouldn't this be some kind of join query?

If the Help Desk table has a ticket number column and a department column, and I have a Department table that has a FK of department, what kind of join will get me a row with data from the department but null values for the Help Desk ticket number? I've tried left outer join, left join, right join, ect.

Am I explaining the problem correctly? Thanks for the help, I've been at it a couple of days.

Drew
Comayagua, Honduras

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 17:17:45

yep. you need to have a master table for departments and left join your heldesk table to you. then group on department name field and take count of records from HelpDesk table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spasticnapjerk
Starting Member

7 Posts

Posted - 2012-08-21 : 17:43:53

Thank you visakh. I'm trying to get that to work but I'm getting hung up on the GROUP BY statement. There are five or six columns in the select statement. Do I have to group by each of them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 17:58:07

what are those six columns? are they all from Departments table?

if you can post table structures of both then that would be ideal

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spasticnapjerk
Starting Member

7 Posts

Posted - 2012-08-22 : 10:53:32

I was able to get the query to run using the ORDER BY clause by adding every selected field to the ORDER BY clause. I'm not sure if that's giving me the incorrect results or not, but I'm still not seeing the NULL row for every department. Here are the table structures, pared down:

Help Desk Table
RequestID, MSC_Directorate

Departments Table
RequestID, MenuValue, MSCForPowerPoint, MSCSortOrder

The join is on MSC_Directorate = MenuValue. The real PK of the Departments Table is RequestID, but MenuValue can be a PK also as there are no duplicates. MSCForPowerpoint is so that I can use that data instead of the MenuValue data. Some data of the MSCForPowerpoint column, which I need to see regardless if there is a matching row:

CMD
J1
J2
J3
J4
J6
J7
J8

Visakh, thank you for any and all help, I'm stuck here!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 11:01:33

[code]SELECT d.RequestID,d.MSCForPowerPoint,d.MSCSortOrder,COUNT(hd.RequestID)
FROM Departments d
LEFT JOIN HelpDesk hd
ON hd.MSC_Directorate = d.MenuValue
GROUP BY d.RequestID,d.MSCForPowerPoint,d.MSCSortOrder
ORDER BY d.MSCSortOrder
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spasticnapjerk
Starting Member

7 Posts

Posted - 2012-08-22 : 11:52:49

Thanks again visakh. I had tried the query using the departments table on the left side, but before you said to use the order by clause. I'll try this out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 12:11:24

ok..let me knw how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spasticnapjerk
Starting Member

7 Posts

Posted - 2012-08-22 : 12:17:05

Hi visakh...still not getting all the rows in the departments table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 12:22:28

nope thats not correct

then i'm sure you've someother part for your query which you've not told as so far

my last suggestion will give you all records in department

if you're using different query please post it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spasticnapjerk
Starting Member

7 Posts

Posted - 2012-08-22 : 12:39:40

There is a part of the query, my WHERE clause is filtering records in regards to date and another column in the hd table. But I'm pretty sure that doesn't matter. If I take the where clause out, yes, I'll see the missing rows because data will be returned.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 14:34:41

quote:
Originally posted by spasticnapjerk

There is a part of the query, my WHERE clause is filtering records in regards to date and another column in the hd table. But I'm pretty sure that doesn't matter. If I take the where clause out, yes, I'll see the missing rows because data will be returned.

how do you determine if that matters or not?

move the filter conditions to join using AND and you'll see the difference

the reason is this

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

hope this will illustrate you the need of posting full details for a question in future to avoid uncessary confusions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spasticnapjerk
Starting Member

7 Posts

Posted - 2012-08-22 : 15:32:41

Yep, there they are. Quite possibly the problem for the entire time!

I read the blog post and understood some of it, but my brain is mush. Thanks for pointing that out and thanks for your brainpower and help visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 15:37:00

quote:
Originally posted by spasticnapjerk

Yep, there they are. Quite possibly the problem for the entire time!

I read the blog post and understood some of it, but my brain is mush. Thanks for pointing that out and thanks for your brainpower and help visakh.

No problem you're welcome

Glad that I could be of help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources