Nested Select Statements - SQL Server Forums

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

Other SQL Server Topics (2005)

Nested Select Statements

Author

Topic

dominican1979
Starting Member

18 Posts

Posted - 2009-06-10 : 13:27:01

Hello, thanks for taking the time to read my post. I need to include a quantity count column on my 1st sql statement below. I can get what I want using the 2nd sql statement but I haven't been able to figure out how to get them into one. To get the info I need the two related tables are:

dbo.OrdDoc (order documents): OrdDocNo -> PK
dbo.OrdDocLn (order document lines) : OrdDocNo -> FK

Note: the first sql statement has a bunch of simple nested statements in it, but I can't get the final one that uses the aggregate function to work. Thanks in advance for your help.

***1st SQL Statement***

SELECT dbo.OrdDoc.OrdNo AS OrderNumber, Actor_2.Inf4, dbo.OrdDoc.TransGr2 AS Service, dbo.OrdDoc.YrRef AS ContactName,
dbo.OrdDoc.OrdDocNo AS OrderDocNo, ISNULL
((SELECT TOP (1) CouncNo
FROM dbo.Post
WHERE (PostNo = dbo.OrdDoc.DelPNo) AND (dbo.OrdDoc.Lang = dbo.OrdDoc.Lang)), '') AS State, ISNULL
((SELECT TOP (1) Phone
FROM dbo.Actor
WHERE (ActNo = dbo.OrdDoc.DelActNo)), '') AS Phone, dbo.OrdDoc.CustNo AS CustomerNumber, ISNULL
((SELECT TOP (1) ISO
FROM dbo.Ctry
WHERE (CtryNo = dbo.OrdDoc.DelCtry)), '') AS ISOCountry, ISNULL
((SELECT TOP (1) MailAd
FROM dbo.Actor AS Actor_1
WHERE (CustNo = dbo.OrdDoc.CustNo)), '') AS Email, ISNULL
((SELECT TOP (1) Nm
FROM dbo.Ctry AS Ctry_1
WHERE (CtryNo = dbo.OrdDoc.DelCtry)), '') AS CountryName, dbo.OrdDoc.DelAd1 AS Address1, dbo.OrdDoc.DelAd2 AS Address2,
dbo.FormatZip(dbo.OrdDoc.DelPNo, dbo.OrdDoc.Ctry) AS Zip, dbo.OrdDoc.DelPArea AS City, dbo.OrdDoc.DelNm AS CompanyName,
dbo.OrdDoc.InvoNo AS InvoiceNo, ISNULL
((SELECT TOP (1) Txt
FROM dbo.Txt AS Txt_2
WHERE (Lang = dbo.OrdDoc.Lang) AND (TxtTp = 59) AND (TxtNo = dbo.OrdDoc.TransGr2)), '') AS OrderType
FROM dbo.OrdDoc INNER JOIN
dbo.Actor AS Actor_2 ON Actor_2.CustNo = dbo.OrdDoc.CustNo
WHERE (dbo.OrdDoc.DocTp = 1)

***2nd SQL Statement***

SELECT SUM(dbo.OrdDocLn.NoInvoAb) AS Qty, dbo.OrdDoc.InvoNo
FROM dbo.OrdDocLn INNER JOIN
dbo.OrdDoc ON dbo.OrdDocLn.OrdDocNo = dbo.OrdDoc.OrdDocNo
WHERE (dbo.OrdDoc.DocTp = 1) AND (dbo.OrdDocLn.ProdTp = 1 OR
dbo.OrdDocLn.ProdTp = 2)
GROUP BY dbo.OrdDoc.InvoNo

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 15:26:13

Maybe you mean this?

SELECT dbo.orddoc.ordno                                  AS ordernumber, 
       actor_2.inf4, 
       dbo.orddoc.transgr2                               AS SERVICE, 
       dbo.orddoc.yrref                                  AS contactname, 
       dbo.orddoc.orddocno                               AS orderdocno, 
       Isnull((SELECT TOP ( 1 ) councno 
               FROM   dbo.post 
               WHERE  (postno = dbo.orddoc.delpno) 
                      AND (dbo.orddoc.lang = dbo.orddoc.lang)), 
              '') AS state, 
       Isnull((SELECT TOP ( 1 ) phone 
               FROM   dbo.actor 
               WHERE  (actno = dbo.orddoc.delactno)),'') AS phone, 
       dbo.orddoc.custno                                 AS customernumber, 
       Isnull((SELECT TOP ( 1 ) iso 
               FROM   dbo.ctry 
               WHERE  (ctryno = dbo.orddoc.delctry)),'') AS isocountry, 
       Isnull((SELECT TOP ( 1 ) mailad 
               FROM   dbo.actor AS actor_1 
               WHERE  (custno = dbo.orddoc.custno)),'') AS email, 
       Isnull((SELECT TOP ( 1 ) nm 
               FROM   dbo.ctry AS ctry_1 
               WHERE  (ctryno = dbo.orddoc.delctry)),'') AS countryname, 
       dbo.orddoc.delad1                                 AS address1, 
       dbo.orddoc.delad2                                 AS address2, 
       dbo.Formatzip(dbo.orddoc.delpno,dbo.orddoc.ctry)  AS zip, 
       dbo.orddoc.delparea                               AS city, 
       dbo.orddoc.delnm                                  AS companyname, 
       dbo.orddoc.invono                                 AS invoiceno, 
       Isnull((SELECT TOP ( 1 ) txt 
               FROM   dbo.txt AS txt_2 
               WHERE  (lang = dbo.orddoc.lang) 
                      AND (txttp = 59) 
                      AND (txtno = dbo.orddoc.transgr2)), 
              '') AS ordertype,
       d.qty                                             AS quantity
FROM   dbo.orddoc 
       INNER JOIN dbo.actor AS actor_2 
         ON actor_2.custno = dbo.orddoc.custno 
       INNER JOIN (SELECT   Sum(dbo.orddocln.noinvoab) AS qty, 
                   dbo.orddoc.invono 
                   FROM     dbo.orddocln 
                   INNER JOIN dbo.orddoc 
                   ON dbo.orddocln.orddocno = dbo.orddoc.orddocno 
                   WHERE    (dbo.orddoc.doctp = 1) 
                   AND (dbo.orddocln.prodtp = 1 
                   OR dbo.orddocln.prodtp = 2) 
                   GROUP BY dbo.orddoc.invono) as d
         ON d.invono = dbo.orddoc.invono
WHERE  (dbo.orddoc.doctp = 1)

No, you're never too old to Yak'n'Roll if you're too young to die.

dominican1979
Starting Member

18 Posts

Posted - 2009-06-11 : 10:57:58

Hey webfred,

Thank you soooooooo much, that's amazing, it works perfectly. I really really appreciate your help on this :-)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-11 : 11:15:13

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 12:51:08

just be aware that subqueries will give you a random column value which satisfies the given condition as you have not specified a order by clause and you're taking top 1.

dominican1979
Starting Member

18 Posts

Posted - 2009-06-12 : 16:31:06

Hi visakh16, thank you very much for the observation, I will take a look into that as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 02:05:10

welcome

dominican1979
Starting Member

18 Posts

Posted - 2009-06-17 : 11:36:52

Hello again,

I just ran into a little problem with this part of the code

WHERE (dbo.orddoc.doctp = 1)
AND (dbo.orddocln.prodtp = 1
OR dbo.orddocln.prodtp = 2)

basically, dbo.orddocln.prodtp = 1 OR dbo.orddocln.prodtp = 2 looks at the order lines to see what type of product it is. I'm only interested in getting a quantity if it either of these product types, otherwise show 0 as the quantity. Right now if its another product type it just won't show the record at all because doesn't meet the condition. How could I have it still show the records with 0 when the conditions are not met? thanks a lot for your help!

dominican1979
Starting Member

18 Posts

Posted - 2009-06-17 : 12:20:52

Hello,

I think I found a solution hopefully. I changed the 2nd inner join after the from clause to a left outer join, now it gives me the records and just null on the quantity for those what don't meet the conditions.

FROM dbo.orddoc
INNER JOIN dbo.actor AS actor_2
ON actor_2.custno = dbo.orddoc.custno
LEFT OUTER JOIN(SELECT Sum(dbo.orddocln.noinvoab) AS qty,
dbo.orddoc.invono
FROM dbo.orddocln
INNER JOIN dbo.orddoc
ON dbo.orddocln.orddocno = dbo.orddoc.orddocno
WHERE (dbo.orddoc.doctp = 1)
AND (dbo.orddocln.prodtp = 1
OR dbo.orddocln.prodtp = 2)
GROUP BY dbo.orddoc.invono) as d
ON d.invono = dbo.orddoc.invono
WHERE (dbo.orddoc.doctp = 1)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-17 : 12:41:27

Yes that is correct.
And if you don't want NULL to be displayed you can do
isnull(d.qty,0) AS quantity
in your select list

No, you're never too old to Yak'n'Roll if you're too young to die.

dominican1979
Starting Member

18 Posts

Posted - 2009-06-17 : 13:05:36

Hi webfred,

Thank you so much for your help again, I really appreciate it :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources