Joining two select Statements without temptable

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

Transact-SQL (2005)

Joining two select Statements without temptable

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-22 : 01:56:35

Hi,
If any one could suggest if Is it possible to make a JOIN of two different select statements.

SELECT PartNumber,SUM(Value) FROM dbo.List_Parts where PartNumber like '%TX'

To be Joined with

SELECT PartNumber,COUNT(PartNumber) FROM dbo.List_Orders where Part Number like '%TX'

The result should be:
PartNumber , SUM(Value),COUNT(PartNumber)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 02:32:06

[code]SELECT t1.PartNumber,t1.PartSum,t2.PartCount
FROM (
SELECT PartNumber,SUM(Value) AS PartSum
FROM dbo.List_Parts
where PartNumber like '%TX'
)t1
INNER JOIN
(
SELECT PartNumber,COUNT(PartNumber) AS PartCount
FROM dbo.List_Orders
where Part Number like '%TX'

)t2
ON t2.PartNumber=t1.PartNumber[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 08:36:21

Doesn't this need a GROUP BY?

SELECT PartNumber,SUM(Value) FROM dbo.List_Parts where PartNumber like '%TX'

SELECT PartNumber,SUM(Value) FROM dbo.List_Parts where PartNumber like '%TX' GROUP BY PartNumber

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 08:39:10

So visakh16's query should (I think) be...

SELECT t1.PartNumber,t1.PartSum, t2.PartCount
FROM (
SELECT PartNumber,SUM(Value) AS PartSum
FROM dbo.List_Parts 
where PartNumber like '%TX'
GROUP BY PartNumber
)t1
INNER JOIN
(
SELECT PartNumber,COUNT(PartNumber) AS PartCount
FROM dbo.List_Orders 
where PartNumber like '%TX'
GROUP BY PartNumber
)t2
ON t2.PartNumber=t1.PartNumber

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 08:43:34

You might want a left or a full outer join too?...

SELECT t1.PartNumber, t1.PartSum AS PartSum, ISNULL(t2.PartCount, 0) AS PartCount
FROM (
SELECT PartNumber,SUM(Value) AS PartSum
FROM dbo.List_Parts 
where PartNumber like '%TX'
GROUP BY PartNumber
)t1
LEFT OUTER JOIN
(
SELECT PartNumber,COUNT(PartNumber) AS PartCount
FROM dbo.List_Orders 
where PartNumber like '%TX'
GROUP BY PartNumber
)t2
ON t2.PartNumber=t1.PartNumber


SELECT ISNULL(t1.PartNumber, t2.PartNumber), ISNULL(t1.PartSum, 0) AS PartSum, ISNULL(t2.PartCount, 0) AS PartCount
FROM (
SELECT PartNumber,SUM(Value) AS PartSum
FROM dbo.List_Parts 
where PartNumber like '%TX'
GROUP BY PartNumber
)t1
FULL OUTER JOIN
(
SELECT PartNumber,COUNT(PartNumber) AS PartCount
FROM dbo.List_Orders 
where PartNumber like '%TX'
GROUP BY PartNumber
)t2
ON t2.PartNumber=t1.PartNumber

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 08:46:08

Hmm. Maybe this is cleaner?...

SELECT PartNumber, SUM(Value) AS PartSum, SUM(PartCount) AS PartCount FROM (
    SELECT PartNumber, Value, 0 AS PartCount FROM dbo.List_Parts
    UNION ALL
    SELECT PartNumber, 0, 1 FROM dbo.List_Orders) a
WHERE PartNumber like '%TX'
GROUP BY PartNumber

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources