Solved

Need help with SQL

Posted on 2008-10-01
6
161 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:Tom Knowlton
  • 3
  • 2
6 Comments
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 100 total points
ID: 22619270
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
ID: 22619752
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:Tom Knowlton
ID: 22619809
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:Tom Knowlton
ID: 22619851
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:Tom Knowlton
ID: 22620001
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
ID: 22623068
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

749 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