Newbie to Sql Subqueries

Jewel142 · Oct 15, 2011

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~

JarlH · Oct 16, 2011

Without sample data and table defintions it's hard to find your problem. Which 2 rows aren't returned? How are those 2 different from the 4 rows that are returned?

Just guessing here, but what happens if you replace the subquery part with
[tt]WHERE InvoiceLineItems.InvoiceID IN
(SELECT InvoiceLineItems.InvoiceID
FROM InvoiceLineItems
WHERE InvoiceLineItems.InvoiceSequence >1)[/tt]

BTW, I can't see why you need to do that RIGHT OUTER JOIN, would a regular INNER JOIN give you the same result?

r937 · Oct 17, 2011

Code:

SELECT Vendors.VendorName
     , 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

Jewel142 · Oct 17, 2011

Thank you - it worked!

Jewel

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Newbie to Sql Subqueries

Jewel142

ISP

JarlH

Programmer

r937

Technical User

Jewel142

ISP

Similar threads

Part and Inventory Search

Sponsor