Newbie to Sql Subqueries
Newbie to Sql Subqueries
(OP)
I am new to SQL and I'm having a hard time with getting the right results from my subquery.
I am reporting off of three tables, Vendors, Invoices and Line Items and I am trying to get the results for each invoice that has more than one line item in the InoviceLines Table.
Here is my code so far but for some reason, I'm only getting four records instead of the six that I should be getting. (I tested it in Excel). What am I doing wrong? I've tried changing my joins but nothing seems to work.
SELECT Vendors.VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItems.InvoiceLineItemAmount
FROM Vendors INNER JOIN
Invoices ON Vendors.VendorID = Invoices.VendorID RIGHT OUTER JOIN
InvoiceLineItems ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
WHERE InvoiceLineItems.InvoiceSequence IN
(SELECT InvoiceLineItems.InvoiceSequence
FROM InvoiceLineItems
WHERE InvoiceLineItems.InvoiceSequence >1)
I am so frustrated right now. Any help would be greatly appreciated!
Jewel~
I am reporting off of three tables, Vendors, Invoices and Line Items and I am trying to get the results for each invoice that has more than one line item in the InoviceLines Table.
Here is my code so far but for some reason, I'm only getting four records instead of the six that I should be getting. (I tested it in Excel). What am I doing wrong? I've tried changing my joins but nothing seems to work.
SELECT Vendors.VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItems.InvoiceLineItemAmount
FROM Vendors INNER JOIN
Invoices ON Vendors.VendorID = Invoices.VendorID RIGHT OUTER JOIN
InvoiceLineItems ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
WHERE InvoiceLineItems.InvoiceSequence IN
(SELECT InvoiceLineItems.InvoiceSequence
FROM InvoiceLineItems
WHERE InvoiceLineItems.InvoiceSequence >1)
I am so frustrated right now. Any help would be greatly appreciated!
Jewel~
Talk To Other Members
RE: Newbie to Sql Subqueries
Just guessing here, but what happens if you replace the subquery part with
WHERE InvoiceLineItems.InvoiceID IN
(SELECT InvoiceLineItems.InvoiceID
FROM InvoiceLineItems
WHERE InvoiceLineItems.InvoiceSequence >1)
BTW, I can't see why you need to do that RIGHT OUTER JOIN, would a regular INNER JOIN give you the same result?
RE: Newbie to Sql Subqueries
CODE
, Invoices.InvoiceID
, InvoiceLineItems.InvoiceSequence
, InvoiceLineItems.InvoiceLineItemAmount
FROM Vendors
INNER
JOIN Invoices
ON Invoices.VendorID = Vendors.VendorID
AND EXISTS
( SELECT *
FROM InvoiceLineItems
WHERE InvoiceID = Invoices.InvoiceID
AND InvoiceSequence > 1 )
INNER
JOIN InvoiceLineItems
ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Newbie to Sql Subqueries
Jewel