Solved

Select statement with multiple joins

Posted on 2007-03-25
8
1,243 Views
Last Modified: 2008-01-09
How can I select fields from multiple select statements into one select statement.  I have the following selects with a common field called contact_id.  The select statements are greatly simplified for this purpose.  Current, Zero30, etc are calculated and not fields in a table.

I want to be able to generate ONE record with each of the values from the joined selects. I've included a sample but don't know how the joins should be phrased (JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN).

SELECT a.contact_id, b.Current, c.Zero30, d.ThirtyOne60, e.SixtyOne90, f.Over90 FROM accounting a
(HOW TO JOIN EACH???)
(SELECT contact_id, Current FROM ar) AS b ON a.contact_id = b.contact_id JOIN?
(SELECT contact_id, Zero30 FROM ar) AS c ON a.contact_id = c.contact_id JOIN?
(SELECT contact_id, ThirtyOne60 FROM ar) AS d ON a.contact_id = d.contact_id JOIN?
(SELECT contact_id, SixtyOne90 FROM ar) AS e ON a.contact_id = e.contact_id JOIN?
(SELECT contact_id, Over90 FROM ar) AS f ON a.contact_id = f.contact_id
WHERE a.contact_id = 455
0
Comment
Question by:TSFLLC
8 Comments
 
LVL 11

Expert Comment

by:dready
ID: 18789884
I am not really sure wether i understand what you want to do, but i asume there are 2 tables, accounting and ar? If not, please name the table names in the other select statements.

SELECT a.contact_id, b.Current, b.Zero30, b.ThirtyOne60, b.SixtyOne90, b.Over90 FROM accounting a
left join ar as b on ON a.contact_id = b.contact_id

mmm... doesnt make sense, cause in that case you wouldnt need table at all...

so, please describe how many tables you have and which fields are in which table
0
 
LVL 9

Assisted Solution

by:dbeneit
dbeneit earned 150 total points
ID: 18790100
I supposed that each "ar" table is a distinct table or joins of tables.
if each contact_id is in alls tables INNER JOIN, if there are records in accounting that not exist in others LEFT JOIN , and right JOIN is if you have records in  "f" and they can't be in others.

SELECT a.contact_id, b.Current, c.Zero30, d.ThirtyOne60, e.SixtyOne90, f.Over90 FROM accounting a
INNER JOIN
(SELECT contact_id, Current FROM ar) AS b ON a.contact_id = b.contact_id
INNER JOIN
(SELECT contact_id, Zero30 FROM ar) AS c ON a.contact_id = c.contact_id
INNER JOIN
(SELECT contact_id, ThirtyOne60 FROM ar) AS d ON a.contact_id = d.contact_id
INNER JOIN
(SELECT contact_id, SixtyOne90 FROM ar) AS e ON a.contact_id = e.contact_id
INNER JOIN
(SELECT contact_id, Over90 FROM ar) AS f ON a.contact_id = f.contact_id
WHERE a.contact_id = 455

If ar is a unique table. ANd all record of accounting  have 1 or more records in "ar"  then better inner join than left join

0
 

Author Comment

by:TSFLLC
ID: 18790270
I guess my mind had gotten a little fuzzy.  Actually 'accounting' is suppose to be 'ar'.  We're working with only ONE table.  I said in my initial post that I simplified the separate select statements of b,c,d,e and f.  They each have nested select statements where they pull records and the balance is a grouped field, hence Current, Zero30...etc.

In using INNER JOIN what if no record meets the criteria set forth in one of the select statements?  Do the contact_id, Current values come back Null or would it cause the Select a.contact_id, b.Current, c.Zero30......to come back with no record?

I have to have it come back Null else a valuable A/R record would not be included in the select.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 18795541
I strongly suspect you can calculate all the totals at once, rather than in several different queries and then joining them.

You probably have the Current|0-30|31-60|... in WHERE clauses for each statement, with the rest of the query -- joins, etc. -- the same.  Instead, you can do this:

SELECT contact_id,
    SUM(CASE WHEN ? <= 0 THEN amt ELSE 0 END) AS Current,
    SUM(CASE WHEN ? BETWEEN 1 AND 30 THEN amt ELSE 0 END) AS Zero30,
    SUM(CASE WHEN ? BETWEEN 31 AND 60 THEN amt ELSE 0 END) AS Thirtyone60,
    ...
FROM tableName
WHERE ...other conditions excluding # of days...
GROUP BY contact_id
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:TSFLLC
ID: 18795770
Thanks for the reply Scott.
I think I'm going to be sick.  I think I just realized something about SQL that I never knew and I truly feel nauseous.  This shows that I have alot to learn.

I have always been using the word HAVING ....  after my GROUP BY for the conditional word instead of using WHERE before the GROUP BY.

Because I have always been using HAVING... it has required me to create nested select statements I would not have ordinarily had to create?  For example the records below would yield two different results depending on the two subsequent SQL statements.

ContactID        DueDate            InvoiceAmt
1                     12/20/2006           150.00
1                     12/22/2006           150.00
1                     12/24/2006           150.00

SELECT ContactID, SUM(InvoiceAmt) AS InvAmt FROM file WHERE DueDate >= CONVERT(datetime,'12/20/2006') GROUP BY ContactID

Results would be (which is what I want):
ContactID         InvAmt
1                       450.00

Whereas the SQL statement
SELECT ContactID, SUM(InvoiceAmt) AS InvAmt FROM file WHERE DueDate >= CONVERT(datetime,'12/20/2006') GROUP BY ContactID
would return an error about DueDate not being part of the aggregate grouping... and I would have to do create an idiotic nested select like this.
SELECT a.ContactID, SUM(a.InvoiceAmt) AS InvAmt FROM (SELECT ContactID, InvoiceAmt FROM file WHERE DueDate >= CONVERT(datetime,'12/20/2006')) a GROUP BY a.ContactID

If I'm on the right track....
Is it safe to say that I can SUM on multiple fields and at the same time include any number of conditions through using WHERE without having to include those conditional fields as output?
0
 

Author Comment

by:TSFLLC
ID: 18795795
I screwed up.  I copied and pasted without making the change to the following:

Whereas the SQL statement
SELECT ContactID, SUM(InvoiceAmt) AS InvAmt FROM file GROUP BY ContactID HAVING DueDate >= CONVERT(datetime,'12/20/2006')
would return an error about DueDate not being part of the aggregate grouping... and I would have to do create an idiotic nested select like this.
SELECT a.ContactID, SUM(a.InvoiceAmt) AS InvAmt FROM (SELECT ContactID, InvoiceAmt FROM file WHERE DueDate >= CONVERT(datetime,'12/20/2006')) a GROUP BY a.ContactID
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 350 total points
ID: 18795891
You should always use WHERE for conditions that apply to *each row individually*.  Use HAVING only for conditions that apply to *groups of rows*.

>> Is it safe to say that I can SUM on multiple fields and at the same time include any number of conditions through using WHERE without having to include those conditional fields as output? <<
Yes, I think that is true, if I understand the q correctly.

Note, though, that I am also talking about using CASE statements to generate multiple, different totals from the same SELECT.  For example, typically aging is based on due_date.  So, to calc Current|1-30|31-60|61-90|..., the obvious way to total these is with individual SELECTs, like so:

SELECT id, SUM(amt) AS Current
FROM tableName
...joins...
WHERE due_date >= CONVERT(CHAR(8), GETDATE(), 112)  --today only
...other conditions...
--UNION ALL
SELECT id, SUM(amt) AS OneTo30
FROM tableName
...joins...
WHERE due_date >= DATEADD(DAY, -30, CONVERT(CHAR(8), GETDATE(), 112)) AND
    due_date < CONVERT(CHAR(8), GETDATE(), 112) --1 to 30 days old
...other conditions...
--UNION ALL
SELECT id, SUM(amt) AS ThirtyOneTo60
FROM tableName
...joins...
WHERE due_date >= DATEADD(DAY, -60, CONVERT(CHAR(8), GETDATE(), 112)) AND
    due_date < DATEADD(DAY, -30, CONVERT(CHAR(8), GETDATE(), 112)) --31 to 60 days old
...other conditions...
...other UNIONS and SELECTs...

However, instead of that, you can compute all the aging SUMs in one query like so:
SELECT id,
    SUM(CASE WHEN due_date >= CONVERT(CHAR(8), GETDATE(), 112) THEN amt ELSE 0 END) AS Current,
    SUM(CASE WHEN due_date >= DATEADD(DAY, -30, CONVERT(CHAR(8), GETDATE(), 112)) AND
    due_date < CONVERT(CHAR(8), GETDATE(), 112) THEN amt ELSE 0 END) AS OneTo30,
    SUM(CASE WHEN due_date >= DATEADD(DAY, -60, CONVERT(CHAR(8), GETDATE(), 112)) AND due_date < DATEADD(DAY, -30, CONVERT(CHAR(8), GETDATE(), 112)) THEN amt ELSE 0 END) AS ThirtyOneTo60,
    SUM(...), ...
FROM tableName
...joins...
WHERE ...other conditions...
GROUP BY id
ORDER BY id
0
 

Author Comment

by:TSFLLC
ID: 18796107
I understand what you're saying.  As I was waiting (hoping) for another quick post from you, I modified my original select with WHERE making it more efficient.  I have one other issue that I will have to deal with but this has helped immensely.

Now I get to re-review other SQL statements where I use HAVING and not WHERE.  All part of the learning curve.

dbeneit understood and helped me out with the original issue but your postings had value I wasn't expecting but welcome.  Split points.

Thanks all !!  
Sincerely,
Phil
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

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

13 Experts available now in Live!

Get 1:1 Help Now