• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Nested MS SQL QUERY / JOIN?

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
forsters
Asked:
forsters
  • 11
  • 9
3 Solutions
 
Bhavesh ShahLead AnalysistCommented:
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
 
forstersAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
forstersAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
if its not work then send your updated query
0
 
forstersAuthor Commented:
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
 
forstersAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
forstersAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
forstersAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
what exactly you didnt understand?

if its small then will complete it here only .
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
forstersAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
forstersAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
Happy to share my knowledge :-)
0
 
forstersAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now