Solved

Nested  MS SQL QUERY / JOIN?

Posted on 2013-10-23
20
243 Views
Last Modified: 2013-10-28
Hi Experts,

Bumped into a query I'm not sure how to write.
I have the following SP:

@nominal varchar(10)
AS
BEGIN

SELECT i.ItemID, i.ItemDescription, i.InventoryNo, i.SerialNo, i.Location, i.Disposals, i.UnitCost, i.VAT, i.Total, o.InvoiceNo, o.OrderDate, s.SupplierName
	FROM Orders o 
	Right Join 
                  OrderItems i 
	ON o.OrderID = i.OrderID
	Right Join 
                  SupplierDetails s
                 ON o.SupplierID = s.SupplierID 
                 Where i.nominal = @nominal 
END

Open in new window


I want to pull in a new column from a third table (ItemType - holding CategoryID & Category) - I want Category.

The Join would be between OrderItems.ItemType and ItemType.CategoryID.

How would I write this given that my primary table is Orders?
0
Comment
Question by:forsters
  • 11
  • 9
20 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39593806
Can u try with this

SELECT i.ItemID, i.ItemDescription, i.InventoryNo, i.SerialNo, i.Location, i.Disposals, i.UnitCost, i.VAT, 
		i.Total, o.InvoiceNo, o.OrderDate, s.SupplierName
FROM SupplierDetails s 
	 LEFT OUTER JOIN OrderItems i ON s.SupplierID = i.SupplierID 
	 LEFT OUTER JOIN Orders o ON o.OrderID = i.OrderID
	 LEFT OUTER JOIN ItemType IT ON I.ItemType = IT.ItemType 
								AND I.CategoryID = IT.CategoryID 
WHERE i.nominal = @nominal 

Open in new window

0
 

Author Comment

by:forsters
ID: 39593890
Hi,

Thanks for code, there's no SupplierID in OrderItems unfortunately, that only exists in Orders hence the difficulty.

Orders is effectively a parent table to OrderItems, as such it has the relationship with Suppliers.

However ItemType is (as the name suggests) Item specific so the relationship in that instance is between OrderItems and ItemType.

I have worked around the problem by creating a view of the above SP adding in i.typeID and i.nominal to my select (but omitting the nominal parameter) and the creating a new SP that selects all from the original and then joins to ItemType to get the Category and I reapply the nominal parameter - it works but I am just curoius now to know whether the same thing can be achieved via a nested query or something.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39593925
hi,

ideally your query should be

SELECT 
FROM 
( 
 select o.orderId, o.InvoiceNo, o.OrderDate, s.SupplierName
 from Orders o inner join  SupplierDetails s
 on o.SupplierID = s.SupplierID
)as O Right outer join OrderItems i ON o.OrderID = i.OrderID 
LEFT OUTER JOIN ItemType IT ON I.ItemType = IT.ItemType 
								AND I.CategoryID = IT.CategoryID 

Open in new window

this is basic one, you may need to change.
let us know if you face any issue
0
 

Author Comment

by:forsters
ID: 39594029
Hi,

I've included '*' after the primary Select - otherwise I get a syntax error near the word FROM, but I still get an error:
The multi-part identifier o.OrderID could not be bound, I'm also getting an invalid column name 'SupplierID' which I don't follow.
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 500 total points
ID: 39594061
SELECT  o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierID, s.SupplierName
FROM 
( 
 select o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierID, s.SupplierName
 from Orders o inner join  SupplierDetails s
 on o.SupplierID = s.SupplierID
)as O Right outer join OrderItems i ON o.OrderID = i.OrderID 
LEFT OUTER JOIN ItemType IT ON I.ItemType = IT.ItemType 
								AND I.CategoryID = IT.CategoryID 
					

Open in new window


hi,

basically, what we are doing is...
running query based upon output of inner query which is
select o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierID, s.SupplierName
 from Orders o inner join  SupplierDetails s
 on o.SupplierID = s.SupplierID

Open in new window


Whichever fields you wanted to display from order and supplierdetail then you need to give in select of above query.

now check and let us know
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 39594068
if its not work then send your updated query
0
 

Assisted Solution

by:forsters
forsters earned 0 total points
ID: 39594166
Ah got there, code below now runs perfectly - Thank You!

SELECT        i.ItemID, i.ItemDescription, i.InventoryNo, i.SerialNo, i.Location, i.Disposals, i.UnitCost, i.VAT, i.Total, O_1.InvoiceNo, O_1.OrderDate, O_1.SupplierName, 
                         IT.Category
FROM            (SELECT        o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierName
                          FROM            Orders AS o INNER JOIN
                                                    Suppliers.dbo.SupplierDetails AS s ON o.SupplierID = s.SupplierID) AS O_1 RIGHT OUTER JOIN
                         OrderItems AS i ON O_1.OrderID = i.OrderID LEFT OUTER JOIN
                         ItemTypes AS IT ON i.TypeID = IT.CategoryID

Open in new window

0
 

Author Comment

by:forsters
ID: 39594296
Actually, I don't understand why it works....

That final LEFT OUTER JOIN is being created between O_1 & ItemTypes, but the join is ON OrderItems & ItemTypes where OrderItems is not part of the inner select O_1

So how does that work?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594333
hi,

if you see the query, OrderItems is a part of O_1.

ideally your query should not be Right Join.

Because for every order items there should be an entry in Order table.

use following query with INNER JOIN.

SELECT        i.ItemID, i.ItemDescription, i.InventoryNo, i.SerialNo, i.Location, i.Disposals, i.UnitCost, i.VAT, i.Total, O_1.InvoiceNo, O_1.OrderDate, O_1.SupplierName, 
                         IT.Category
FROM            (SELECT        o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierName
                          FROM            Orders AS o INNER JOIN
                                                    Suppliers.dbo.SupplierDetails AS s ON o.SupplierID = s.SupplierID) AS O_1 INNER JOIN
                         OrderItems AS i ON O_1.OrderID = i.OrderID LEFT OUTER JOIN
                         ItemTypes AS IT ON i.TypeID = IT.CategoryID

Open in new window

0
 

Author Comment

by:forsters
ID: 39594378
Hi,

Re. if you see the query, OrderItems is a part of O_1.


Is it - I really don't follow; O_1 is the alias for the inner Select - yes?

So to me O_1 consists of the few columns selected from Orders and SupplierDetails,
OrderItems is subsequently added in the next INNER JOIN surely... which is why i'm finding it so strange that the final outer join runs.

I'm obviously applying the wrong logic to it
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594425
hi,

if you are clear then i'm happy to assist you.
if not then object the Question and re-open it.

there are lots of genius mind in this community.

surely anyone can explain it more better way....I'll try to if you are not clear
0
 

Author Comment

by:forsters
ID: 39594448
Hi,

Well both your solutions work so I have no problem with them, and I am grateful for your help, I simply do not fully understand how they work and I didn't realise that until I began to try and explain it to my colleagues and realised I couldn't actually do so.

Can you explain or would you prefer me to ask a seperate question to the community
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594483
what exactly you didnt understand?

if its small then will complete it here only .
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594513
lets byfercate the code

SELECT        i.ItemID, i.ItemDescription, i.InventoryNo, i.SerialNo, i.Location, i.Disposals, i.UnitCost, i.VAT, i.Total, O_1.InvoiceNo, O_1.OrderDate, O_1.SupplierName, 
                         IT.Category
FROM            (SELECT        o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierName
                          FROM            Orders AS o INNER JOIN
                                                    Suppliers.dbo.SupplierDetails AS s ON o.SupplierID = s.SupplierID) AS O_1 INNER JOIN
                         OrderItems AS i ON O_1.OrderID = i.OrderID LEFT OUTER JOIN
                         ItemTypes AS IT ON i.TypeID = IT.CategoryID

Open in new window



now

O_1
SELECT        o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierName
                          FROM            Orders AS o INNER JOIN
                                                    Suppliers.dbo.SupplierDetails AS s ON o.SupplierID = s.SupplierID

Open in new window



see, you have four tables to join, which are interrelated one or other way.
so I'd seperated into 2 recordset.
1 is O_1 contains (Order and SupplierDetails)
2 is OrderItems and ItemTypes
0
 

Author Comment

by:forsters
ID: 39594518
I don't understand the final join:

LEFT OUTER JOIN  ItemTypes AS IT ON i.TypeID = IT.CategoryID

I have interpreted that as:

O_1 LEFT OUTER JOIN ItemTypes AS IT ON i.TypeID = IT.CategoryID

But as I understand it O_1 is the alias for:

(SELECT o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierName
FROM Orders AS o
INNER JOIN
Suppliers.dbo.SupplierDetails AS s
ON o.SupplierID = s.SupplierID)


Which makes no reference to OrderItems i

So going back to the LEFT OUTER JOIN how are we able to join on i.TypeID when i.TypeID is not included in O_1?

Is it implicit in the
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594523
Ideally this should also works

SELECT        
		i.ItemID, i.ItemDescription, i.InventoryNo, i.SerialNo, i.Location, i.Disposals, i.UnitCost, 
		i.VAT, i.Total, O_1.InvoiceNo, O_1.OrderDate, O_1.SupplierName, IT.Category
FROM    
        Suppliers.dbo.SupplierDetails S INNER JOIN Orders AS O
ON S.SupplierID = O.SupplierID INNER JOIN OrderItems AS i 
ON O.OrderID = i.OrderID LEFT OUTER JOIN ItemTypes AS IT 
ON i.TypeID = IT.CategoryID
        

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594546
So going back to the LEFT OUTER JOIN how are we able to join on i.TypeID when i.TypeID is not included in O_1?

you are right.its not included.

But

SELECT        i.ItemID, i.ItemDescription, i.InventoryNo, i.SerialNo, i.Location, i.Disposals, i.UnitCost, i.VAT, i.Total, O_1.InvoiceNo, O_1.OrderDate, O_1.SupplierName,
                         IT.Category
FROM            (SELECT        o.OrderID, o.InvoiceNo, o.OrderDate, s.SupplierName
                          FROM            Orders AS o INNER JOIN
                                                    Suppliers.dbo.SupplierDetails AS s ON o.SupplierID = s.SupplierID) AS O_1 INNER JOIN
                         OrderItems AS i
ON O_1.OrderID = i.OrderID LEFT OUTER JOIN
                         ItemTypes AS IT
ON i.TypeID = IT.CategoryID

i.TypeID is coming from OrderItems I which is joined to ItemTypes
0
 

Author Comment

by:forsters
ID: 39594566
Ahh I see, I am so sorry I did not know that it was posssible to write a series of joins effectively relating table a to b and b to c etc. I was under the impression that all joins had to link back to table a.

No wonder I didn't follow, thank you so much...very patient :)
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39594577
Happy to share my knowledge :-)
0
 

Author Closing Comment

by:forsters
ID: 39605044
I have included my final comment in the answer so that anyone else following can see the final code.  Many thanks for your help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

19 Experts available now in Live!

Get 1:1 Help Now