January 18, 2008 at 10:55 am
I need to write a query that pulls rows from multiple tables, but I don't know how to group them. Basically, each 'Order' from table A can have multiple 'Actions' which are found in table B. Each 'Action' can have multiple 'Parts' associated with it. Is it possible to group the results of the query in such a way as to display:
Order
Action
Part
Part
Action
Part
Action
Action
Part
Part
etc....
Would I have to use multiple queries/tables and join them?
January 18, 2008 at 11:41 am
That's not a whole lot of info to go on, but - yes you could. If I understand your request correctly - you'd want to explore something like a UNION query with the all predicate.
End result would look something like
select *
from (
select OrderID, Null as actionID, Null as partid, 0 as ranking
from MyOrders
UNION ALL
select OrderID,ActionID, Null as PartID, 1 as ranking
from MyOrderActions
UNION ALL
select moa.orderID,map.ActionID, map.PartID, 2 as ranking
from MyOrderActions moa
inner join MyActionParts map on moa.actionID=map.actionID
) as T
order by OrderID,ranking
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2008 at 11:57 am
I thought that you couldn't use UNION if you were pulling different numbers of columns from the various tables. Is this the case? Could I simply pad the other tables with empty columns to resolve this?
The end result should look like this:
Order 1
Action 1
Part 1
Part 2
Action 2
Action 3
Part 1
Order 2
Action 1
Action 2
Part 1
Action 3
Action 4
Part 1
etc...
I won't know before hand how many parts are associated with each action, or how many actions are associated with each order. Does this complicate things?
January 18, 2008 at 12:02 pm
you're right about the same number of columns...which is why the first SELECT of mine was padding with NULLS (as you said). Just make the number of columns the same, and make sure col #1 in each is the same data type, col # 2 is the same ,etc...
And no - the number within each "level" isn't important.
I just caught something - the effect I was trying to achieve with the RANKING column isn't going to work, so change the ORDER BY to be:
ORDER BY ORDERID, ACTIONID, PARTID --so that the relative order you want is maintained.
Ditch the RANKING - it's now useless.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2008 at 12:03 pm
The easiest way to do this would be to do inner joins between the tables, select the columns you want, and then have the front end application do the layout for you.
For example, Access reports can do the layout your looking for from a single select with the necessary joins. Crystal Reports and SQL Reporting Services can also do this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2008 at 12:08 pm
You would need to add ActionID between OrderID and Ranking in Matt's example, in the Order By statement. Otherwise, you'll get all the Actions and then all the Parts, instead of them being nested.
In which case, you should have a column in the Actions query which has a "partID" lower than any of the actual part IDs (negative number maybe?), and order by that. Then you can dump the Ranking column, and put PartID in the Order By.
(The complexity of getting Unions to display data in the nested sequence you want is why I recommend having a front-end application do that kind of thing for you. They're good at it, and T-SQL is limited in this regard.)
If you want the parts displayed in a certain sequence, you might need to use a CTE instead of a sub-query (derived table), and add an Order By to the CTE, and a RowNumber, then order the final query by the RowNumber. Of course, that's SQL 2005 only, and only matters if you want the parts in a specific sequence. If not, you'll get the part IDs in numeric order.
Again, a front-end app can sort the parts and actions by something other than the ID number, if you want that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2008 at 12:10 pm
And, of course, Matt posted the same correction to his Order By while I was writing mine. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2008 at 12:38 pm
I'm slow but I still get there at times:)
It's that time of the week where I need the "hands bypass" mechanism. The thought's there - but the execution misses the mark. It's a "reverse PEBCAK" issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 21, 2008 at 6:24 am
I'm using ActiveReports by DataDynamics for the front end, and for some reason it isn't breaking the multiple Actions apart within the Orders. I don't know if this is a fault of the report or of the sql. The action id for each part is clearly different, but that doesn't seem to be enough. I'm not sure how I'm going to handle this.
January 22, 2008 at 2:18 pm
Unfortunately, I'm not familiar with that reporting application. Can't help you on that one.
You're probably stuck with one of the Union queries outlined earlier in this discussion.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply