?
Solved

Need help with SQL

Posted on 2008-10-01
6
Medium Priority
?
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 400 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 1600 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

770 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