Subquery - 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 2008 Forums

Transact-SQL (2008)

Subquery

Author

Topic

swirls
Starting Member

5 Posts

Posted - 2010-06-09 : 20:02:33

I’m hoping someone can help me out here. I’m trying to build a query in order to locate all items in a quote where the item has QRLI.ManufName as ‘ABC’ and the associated logistics line in the same quote (QRLI.ManufName as ‘logistics’).

For example:
If a Quote Number 123 has the following items:

ManufName MfrPartNum
--------- ----------
CIS 112233
ABC 223344
ABC 334455
XYZ logistics

The query should return:
QuoteNumber ManufName MfrPartNum
----------- --------- ----------
123 ABC 223344
123 ABC 334455
123 XYZ logistics

In the below query, I've been able to select all the lines in the quote with ManufName = ABC for QuoteType = 'Full' and 'Self'. However, I'm not sure how/where I need to include the subquery to return the associated logistics line.

SELECT
QRL.QuoteNumber,
QRLI.ManufName,
QRLI.MfrPartNum,

FROM dbo.QuoteReqLeaseItem AS QRLI
INNER JOIN dbo.QuoteReqLease AS QRL ON QRLI.OrderID = QRL.OrderID
LEFT JOIN dbo.RelatedQuote AS RQ ON QRL.OrderID = RQ.RelatedQuoteID

WHERE
QRLI.ManufName = 'ABC'
AND QRL.QuoteType = 'Full'

UNION

SELECT
QRL1.QuoteNumber,
QRLI1.ManufName,
QRLI1.MfrPartNum,

FROM dbo.QuoteReqLeaseItem AS QRLI1
INNER JOIN dbo.QuoteReqLease AS QRL1 ON QRLI1.OrderID = QRL1.OrderID
LEFT JOIN dbo.RelatedQuote AS RQ1 ON QRL1.OrderID = RQ1.OrderID

WHERE
QRLI1.ManufName = 'ABC'
AND QRL1.QuoteType = 'Self'
AND RQ1.OrderID IS NULL

If anyone can help to shed some light on this that would be great!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-10 : 08:59:39

This is a start, you don't need to do a union.


SELECT 
QRL.QuoteNumber,
QRLI.ManufName,
QRLI.MfrPartNum,

FROM dbo.QuoteReqLeaseItem AS QRLI 
INNER JOIN dbo.QuoteReqLease AS QRL ON QRLI.OrderID = QRL.OrderID
LEFT JOIN dbo.RelatedQuote AS RQ ON QRL.OrderID = RQ.RelatedQuoteID 

WHERE 
QRLI.ManufName = 'ABC'

AND
  ( 
    QRL.QuoteType = 'Full' or (QRL1.QuoteType = 'Self' and RQ1.OrderID IS NULL)
  )

But if I understand you correctly, this won't return the record
123 XYZ logistics. Is the only criteria to return this record that the QuoteNumber was returned by the above query?

Jim

Everyday I learn something that somebody else already knew

swirls
Starting Member

5 Posts

Posted - 2010-06-10 : 18:44:13

I would need the current union in order to obtain the different quote types (both have slightly different joins). The current query I have will only return all quotes with ManufName = 'ABC'.
I.e.
QuoteNumber ManufName MfrPartNum
----------- --------- ----------
123 ABC 223344
123 ABC 334455

However, the part I'm not entirely sure about is how to include a query that would return the associated 'logistics' row also. I'm thinking it the query should check the quote numbers returned in the existing query and return the logistics line along with all ABC lines?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources