Select Statment Using Sub Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select Statment Using Sub Query

Author

Topic

Vizulyz_KM
Starting Member

4 Posts

Posted - 2010-04-09 : 02:33:14

im trying to write a select statment that returns four columns, VendorName, InvoiceID, InvoiceSequence, and InvoiceLineItemAmount for each invoice that has more than one line item in the InvoiceLineItems table.

Tables uses
Vendors for VendorName
InvoiceLineItems for InvoiceID, InvoiceSequence, and InvoiceLineItemAmount

primary keys to foriegns keys...
Vendors Table connects to Invoices by VendorID key
InvoiceLineIems connects to Invoices by InvoiceID

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-09 : 03:23:38

Hi,

Can you post some sample data and reqd output format.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Asken
Starting Member

38 Posts

Posted - 2010-04-09 : 03:33:44

Like this?


SELECT
  V.VendorName, L.InvoiceID, I.InvoiceSequence, SUM(L.InvoiceLineItemAmount)
FROM
  InvoiceLineItems L

  INNER JOIN Vendors V ON
  L.VendorID = V.VendorID

  INNER JOIN Invoices I ON
  L.InvoiceID = I.InvoiceID
GROUP BY
  V.VendorName, L.InvoiceID, I.InvoiceSequence
HAVING COUNT(*) > 1

Reporting & Analysis Specialist

Vizulyz_KM
Starting Member

4 Posts

Posted - 2010-04-09 : 03:43:34

I am using Murach's SQL Server 2008 for developers if that helps

pk i am still quite new and am not sure what you are referancing

asken what do the I, V, L stand for in the statement

books gives me a hint and says to use a subquery that test for IvoiceSequence > 1

Invoice Sequence represents items tagged to each invoice

I.E. Invoice 12 has 4 items on the InvoiceLineItems Table thus it has 4 invoices with invoice ID of 12 with a Invoicesequence for each as follows 12.1, 12.2, 12.3, 12.4

Vizulyz_KM
Starting Member

4 Posts

Posted - 2010-04-09 : 19:17:07

bump

Vizulyz_KM
Starting Member

4 Posts

Posted - 2010-04-10 : 05:41:52

Incase anyone has this troubles too I finially sorted it out

SELECT VendorName, InvoiceLineItems.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
FROM InvoiceLineItems JOIN Invoices ON
InvoiceLineItems.InvoiceID = Invoices.InvoiceID
JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
WHERE InvoiceLineItems.InvoiceID IN
(SELECT InvoiceID
FROM InvoiceLineItems
WHERE InvoiceSequence > 1)

Asken
Starting Member

38 Posts

Posted - 2010-04-12 : 01:37:23

Just a quick explanation about the I, V and L. It's just to replace the long table name so instead of writing the above you can write:


SELECT VendorName, L.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
FROM InvoiceLineItems L JOIN Invoices I ON
L.InvoiceID = I.InvoiceID
JOIN Vendors V ON I.VendorID = V.VendorID
WHERE L.InvoiceID IN
(SELECT InvoiceID
FROM InvoiceLineItems
WHERE InvoiceSequence > 1)

Reporting & Analysis Specialist

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources