Join Query assitance

Question

Join Query assitance

sunil.si

SSC Enthusiast

Points: 151
More actions
December 15, 2008 at 9:40 pm

#94288

Friends,
I've the following tables out of which have to get some rows based on the condition given below.
Utilization
--------------
Asso_ID
Prj_ID
Available_Bill_Hr
Month
Allocation
--------------
Asso_ID
Prj_ID
Ass_Name
Prj_Name
Type
AdditionalDetails
------------------------
Asso_ID
Prj_ID
TotalHr
I need to select Asso_ID, Prj_ID, Available_Bill_Hr, ( Available_Bill_Hr - TotalHr ) from the above tables satisfying the conditions (Utilization.Month = 11 && Allocation.Type = 'Contract').. I tried it some, but couldn't able to make it.
Please help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Sunil

Please read the link at the bottom of my post, it will show you how to write table creation scripts and insert statements to provide forum users with sample data.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Also, rather than have one of use write the entire query for you, show the work that you've tried, even if it doesn't work, and then we can help correct just the concepts that you're a bit shy on. You'll learn a lot more that way.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

khandelwal_arun SSC Eights! Points: 929 More actions · Answer 3

Hi Sunil,

I don't know ur business requirment, but u can help below example query:

Select U.Asso_Id, U.Prj_Id, U.Available_Bill_Hr - T.TotalHr as Hr

From (Utilization U Inner Join (Allocation Al Inner Join

(Select Asso_Id, Prj_Id, TotalHr from AdditionalDetails) as T On Al.Asso_Id = T.Asso_Id and Al.Prj_Id = T.Prj_Id)

On U.Asso_ID = Al.Asso_ID and U.Prj_ID = Al.Prj_ID and T.Asso_Id = U.Asso_Id and T.Prj_Id = U.Prj_Id)

Where U.Month = 11 And Al.Type = 'Contract'

khandelwal_arun SSC Eights! Points: 929 More actions · Answer 4