?
Solved

Complex Outer Joins with a Group By

Posted on 2007-10-03
6
Medium Priority
?
648 Views
Last Modified: 2008-01-09
Here are my tables:

tblWorkOrder
-----------------
   ID
   WorkOrderNum


tblVehicle
-----------------
   ID
   WorkOrderID


tblWOService
----------------
   ID
   WorkOrderID
   ServiceID


tblService
----------------
   ID
   Description



Here is my SQL statement:

SELECT tblWorkOrder.WorkOrderNum, COUNT(tblVehicle.ID), LIST(tblService.Description)
from tblWorkOrder
LEFT OUTER JOIN tblVehicle ON tblWorkOrder.ID = tblVehicle.WorkOrderID
LEFT OUTER JOIN tblWOService ON tblWorkOrder.ID = tblWOService.WorkOrderID
LEFT OUTER JOIN tblService ON tblWOService.ServiceID = tblService.ID
GROUP BY WorkOrderNum

I have 2 records in tblVehicle, and 3 records in tblServices.
COUNT(tblVehicle.ID) returns 6 and should return 2
LIST(tblService.Description) returns each description twice, resulting in 6 in the list, it should return 3

I need to figure out how to change my SQL statement to do this, but I can't figure out how!!
Any help would be much appreciated
0
Comment
Question by:kevinbenedict
  • 3
  • 3
6 Comments
 
LVL 4

Accepted Solution

by:
seameadow earned 2000 total points
ID: 20015971
Not sure what flavor of SQL you have there but you might try SELECT DISTINCT ...
along with COUNT(DISTINCT tblvehicle.ID)

0
 

Author Comment

by:kevinbenedict
ID: 20016353
seameadow:
I am using Ultralite SQL

I never thought of using DISTINCT, it was a great suggestion.  I did what you said, and it fixed the COUNT() problem, but I am still seeing 6 items from the LIST(...) section, where it should only be returning 3.   I cannot add a DISTINCT to the LIST(...) portion because it gives me an error saying that my grouped query contains more than one distinct aggregate function.
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20016471
Hi Kevin,

Ultralite is not one of my dialects, but this reference
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/ulfoen10/fo-list.html
suggests that LIST is a row-concatenating function. I'm not sure why you need it. How about

SELECT DISTINCT tblWorkOrder.WorkOrderNum, COUNT(DISTINCT tblVehicle.ID), tblService.Description
from tblWorkOrder
LEFT OUTER JOIN tblVehicle ON tblWorkOrder.ID = tblVehicle.WorkOrderID
LEFT OUTER JOIN tblWOService ON tblWorkOrder.ID = tblWOService.WorkOrderID
LEFT OUTER JOIN tblService ON tblWOService.ServiceID = tblService.ID
GROUP BY WorkOrderNum

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:kevinbenedict
ID: 20017203
I tried the SQL you provided and it returns one row for each service description, but I need each row to have a list of the service descriptions in one field.

My intent is to show the details of a work order in a small list.  The first column will be the work order number, the second column will be the number of vehicles on the work order, and the third column will be a list of the services on the work order.  Unfortunately I have all this information in 4 different tables.

0
 
LVL 4

Expert Comment

by:seameadow
ID: 20017632
How about

SELECT DISTINCT tblWorkOrder.WorkOrderNum, COUNT(DISTINCT tblVehicle.ID), LIST(tblService.Description)
from tblWorkOrder
LEFT OUTER JOIN tblVehicle ON tblWorkOrder.ID = tblVehicle.WorkOrderID
LEFT OUTER JOIN tblWOService ON tblWorkOrder.ID = tblWOService.WorkOrderID
LEFT OUTER JOIN tblService ON tblWOService.ServiceID = tblService.ID
GROUP BY WorkOrderNum
0
 

Author Comment

by:kevinbenedict
ID: 20022249
That is a no go as well.  I am going to accept your first solution, since it is the closest and my requirements have changed to no longer requiring the Service list, so your first suggestion works perfectly for that.

Thanks for the help!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

807 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