×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Newbie to Sql Subqueries

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~

RE: Newbie to Sql Subqueries

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
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

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

RE: Newbie to Sql Subqueries

(OP)
Thank you - it worked!

Jewel

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close