Solved

Need help with SQL

Posted on 2008-10-01
6
156 Views
Last Modified: 2012-05-05
I just don't do enough SQL to effectively know what I am doing wrong.  I could use some help.

I'll set-out the tables as best I can, as follows:

WORKITEMS
workitem_id    PK
workitem_type_id     FK
acount_id  FK
due_date
assigned_user_id

ACCOUNTS
account_id   PK
name

WORKITEM_TYPES
workitem_type_id   PK
name

WORKITEM_ACTIONS
workitem_action_id  PK
workitem_id    FK
start_date
end_date
action_id
reason_id

ACTION_REASONS
action_reason_id
action_id
reason_id

REASONS
reason_id   PK
name
description


What I want is to select all rows from WORKITEM where assigned_user_id is a certain number.

Then I want to join to WORKITEM to ACCOUNTS so I can get the account name for each workitem

Then I want to join to WORKITEM to WORKITEM_TYPES so I can get the external_name

Then I want to join to WORKITEMS to WORKITEM_ACTIONS so I can find-out the end_date and action_id

Then I want to join to WORKITEM_ACTIONS to ACTION_REASONS so I can find out the reason_id

Then I want to join to REASONS to get the friendly name (the reason for the action)

AND

I want to do all of this based upon action_id being 1 or 3



Here is my current SQL attempt, which is not returning any rows...althoug based on the data in the tables I think it should be returning something (see snippet):







BEGIN

SELECT				

		cmsa.[name] as 'Account Name',		

		wt.external_name as 'Work Item Type',

		w.due_date as 'Due Date',

		wa.end_date as 'Pause/On Hold Date',

		r.[name] as 'Pause/On Hold Reason'

	FROM

		WORKFLOW..WORKITEMS (NOLOCK) w

			JOIN

		CMS..ACCOUNTS (NOLOCK) cmsa

			 ON w.account_id = cmsa.account_id

				JOIN

		WORKFLOW..WORKITEM_TYPES (NOLOCK) wt 

			ON w.workitem_type_id = wt.workitem_type_id

				JOIN

		WORKFLOW..WORKITEM_ACTIONS (NOLOCK) wa

			ON w.workitem_id = wa.workitem_id

				JOIN

		WORKFLOW..ACTION_REASONS (NOLOCK) ar

			ON wa.action_id = ar.action_id

				JOIN

		WORKFLOW..REASONS (NOLOCK) r

			ON ar.reason_id = r.reason_id

	WHERE

		w.assigned_user_id = @AssignedUserID

END

Open in new window

0
Comment
Question by:knowlton
  • 3
  • 2
6 Comments
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 100 total points
Comment Utility
Perhaps one (or more) of your tables contain no information.  Like the workITem_Actions or actions_reasons.  Try changing some of those inner joins (which is the defualt if inner/outer is not specified) to left outer joins.
0
 
LVL 13

Accepted Solution

by:
AielloJ earned 400 total points
Comment Utility
Your text / explanation and what you want to do is laid out well from the simplest one table operation and gradually adds the next, more complex step, one at a time.  Have you tried to implement your query one step at a time?  ie:

1) Strip query down to just "... select all rows from WORKITEM where assigned_user_id is a certain number."

2) If step 1 works as expected goto the next step: "Then ... join to WORKITEM to ACCOUNTS so I can get the account name for each workitem"

At some point you'll add a join that makes it all go to elephant snot and you'll have found your answer.

I'd be interested in hearing how it turns out.

JRA
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
AielloJ:

If you are willing to work with me, that would be great.

Here is the first spoonfull of elephant:

select wt.external_name, w.workitem_id from workflow..workitems w
join workitem_types wt on w.workitem_type_id = wt.workitem_type_id
where w.assigned_user_id = 936

yields:

external_name                workitem_id
NULL                           6
NULL                           8
Link Copy Creation 3      191
NULL                           192
NULL                           193
NULL                           194


How do I exclude the nulls on the left?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 5

Author Comment

by:knowlton
Comment Utility
Making progress.

select cmsa.[name], wt.external_name, w.workitem_id from workflow..workitems w
join workflow..workitem_types wt on w.workitem_type_id = wt.workitem_type_id
join cms..accounts cmsa on w.account_id = cmsa.account_id
where w.assigned_user_id = 936 and wt.external_name is not null


yields:


name                               external_name                      workitem_id
OrangeSoda, Inc.          Link Copy Creation 3               191



So far so good....
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
I think I have it:


BEGIN
      SELECT cmsa.[name] as 'Account Name',
            wt.external_name as 'Work Item Type',
            w.due_date as 'Due Date',
            wa.end_date as 'Pause/On Hold Date',
            r.[name] as 'Pause/On Hold Reason'
      FROM WORKFLOW..WORKITEMS w
            JOIN WORKFLOW..WORKITEM_TYPES wt ON w.workitem_type_id = wt.workitem_type_id
            JOIN CMS..ACCOUNTS cmsa on w.account_id = cmsa.account_id
            JOIN WORKFLOW..WORKITEM_ACTIONS wa on w.workitem_id = wa.workitem_id
            JOIN WORKFLOW..REASONS r on wa.reason_id = r.reason_id
      WHERE
            w.assigned_user_id = @AssignedUserID and wt.external_name is not null
            and
            w.workitem_status_id in (30,40)                              
END
0
 
LVL 13

Expert Comment

by:AielloJ
Comment Utility
knowlton:

I was offline when you were doing most of your work.  Didn't mean to not respond to your "If you are willing to work with me, that would be great."  Looks like you found your answer.

JRA
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now