Inner Join w/ Multiple Results display in one row

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

SQL Server Development (2000)

Inner Join w/ Multiple Results display in one row

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-31 : 10:58:45

I have two tables and I am having a lot of trouble figuring this out. One table has investor information and the other one has transactions with dates. What I am trying to do is if there is just one date then to show the date, but if there are multiple dates to show the word various and only show that on one row. I have it kind of working now, but the way it returns the results now is if there are 5 dates it returns 5 lines with various on it instead of just one line with various.

SELECT     CASE WHEN COUNT(t3.dContribution) <= 1 THEN CAST(t3.dContribution AS varchar(20)) ELSE 'Various' END AS DateSold1
				                            FROM          dbo.vew_DSG_BlackBookDetail AS t3
				                            WHERE      t3.pkInvestmentId = dbo.vew_DSG_BlackBookDetail.pkInvestmentId AND 
				                                                   t3.sCRMContactFullName = dbo.vew_DSG_BlackBookDetail.sCRMContactFullName AND DATEPART(yyyy, t3.dContribution

Any help would be greatly appreciated.

Imukai
Starting Member

29 Posts

Posted - 2008-03-31 : 11:16:12

This might not be very elegant (I'm sure someone will come along with something cleaner than this), but you could use a union:


SELECT     t3.pkInvestmentID, CONVERT(varchar,t3.dContribution)
FROM       dbo.vew_DSG_BlackBookDetail AS t3
GROUP BY   t3.pkInvestmentID
HAVING     COUNT(*) = 1

UNION

SELECT     t3.pkInvestmentID, 'VARIOUS'
FROM       dbo.vew_DSG_BlackBookDetail AS t3
GROUP BY   t3.pkInvestmentID
HAVING     COUNT(*) > 1

ORDER BY 1

I'm either still not awake or your query has a where clause that's comparing fields to.. themselves.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-31 : 11:49:25

Here is the full query.

SELECT     TOP 100 PERCENT pkInvestmentId AS InvestmentID, pkDealId, sCRMAccountName AS Security, '1' AS Quantity, sCRMContactFullName, 
                      SUM(nDistributionAmt_Capital) AS DisCap, SUM(nDistributionAmt_Income) AS DisInc, SUM(nDistributionAmt_Recall) AS DisRec, fkInvestorId,
                          (SELECT     CASE WHEN COUNT(t2.dContribution) <= 1 THEN CAST(t2.dContribution AS varchar(20)) ELSE 'Various' END AS DateSold
                            FROM          dbo.vew_DSG_BlackBookDetail AS t2
                            WHERE      t2.pkInvestmentId = dbo.vew_DSG_BlackBookDetail.pkInvestmentId AND 
                                                   t2.sCRMContactFullName = dbo.vew_DSG_BlackBookDetail.sCRMContactFullName
                            GROUP BY dContribution) AS DateSold
FROM         dbo.vew_DSG_BlackBookDetail
WHERE     (DATEPART(yyyy, dContribution) = '2006')
GROUP BY sCRMContactFullName, pkInvestmentId, sCRMAccountName, pkDealId, fkInvestorId
ORDER BY sCRMAccountName

What I get back from this is...

"Subquery returned more than 1 value"

Imukai
Starting Member

29 Posts

Posted - 2008-03-31 : 11:55:03

Yeh when you insert a nested query as part of a select statement, it expects 1 row of information since it's running that query for every row in your result set. Rows-within-rows confuses it.

What you'll need to do then is create a User Defined Function, passing it the relevant information to run the subquery for you, returning either the date in question, or "Various" back to the row. You'd substitute that UDF in place of that nested query.. i.e.:


SELECT     TOP 100 PERCENT pkInvestmentId AS InvestmentID, pkDealId, sCRMAccountName AS Security, 
           '1' AS Quantity, sCRMContactFullName, SUM(nDistributionAmt_Capital) AS DisCap, 
           SUM(nDistributionAmt_Income) AS DisInc, SUM(nDistributionAmt_Recall) AS DisRec, fkInvestorId, 
           udf_SelectDate(pkInvestmentID,'2006') AS DateSold
FROM       dbo.vew_DSG_BlackBookDetail
WHERE      (DATEPART(yyyy, dContribution) = '2006')
GROUP BY   sCRMContactFullName, pkInvestmentId, sCRMAccountName, pkDealId, fkInvestorId
ORDER BY   sCRMAccountName

Are you going to need help with that UDF?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:02:02

SELECT TOP 100 PERCENT m.pkInvestmentId AS InvestmentID, m.pkDealId, m.sCRMAccountName AS Security, '1' AS Quantity, m.sCRMContactFullName,
SUM(m.nDistributionAmt_Capital) AS DisCap, SUM(m.nDistributionAmt_Income) AS DisInc, SUM(m.nDistributionAmt_Recall) AS DisRec, m.fkInvestorId,MAX(t.DateSold)
FROM dbo.vew_DSG_BlackBookDetail m
INNER JOIN
(SELECT dContribution, CASE WHEN COUNT(t2.dContribution) <= 1 THEN CAST(t2.dContribution AS varchar(20)) ELSE 'Various' END AS DateSold
FROM dbo.vew_DSG_BlackBookDetail AS t2
WHERE t2.pkInvestmentId = dbo.vew_DSG_BlackBookDetail.pkInvestmentId AND
t2.sCRMContactFullName = dbo.vew_DSG_BlackBookDetail.sCRMContactFullName
GROUP BY dContribution)t
ON m.dContribution=t.dContribution
WHERE (DATEPART(yyyy, m.dContribution) = '2006')
GROUP BY m.sCRMContactFullName, m.pkInvestmentId, m.sCRMAccountName, m.pkDealId, m.fkInvestorId
ORDER BY m.sCRMAccountName

Imukai
Starting Member

29 Posts

Posted - 2008-03-31 : 13:04:58

Or that... knew there was more than one way to do this.

=)

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-31 : 13:45:40

Visakh16,

Thank you for the post. I am trying to understand everything going on it it, but am having issues getting it to work. It is telling me that "The colum prefix 'dbo.vew_DSG_BlackBookDetail' does not match with a table name or alias name used in the query" I tried changing the instance to m instead and get the same error except with the m in its place. Both exists so I am not sure why it would say that. Any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:59:07

what about this:-

SELECT TOP 100 PERCENT m.pkInvestmentId AS InvestmentID, m.pkDealId, m.sCRMAccountName AS Security, '1' AS Quantity, m.sCRMContactFullName, 
SUM(m.nDistributionAmt_Capital) AS DisCap, SUM(m.nDistributionAmt_Income) AS DisInc, SUM(m.nDistributionAmt_Recall) AS DisRec, m.fkInvestorId,MAX(t.DateSold)
FROM dbo.vew_DSG_BlackBookDetail m
INNER JOIN 
(SELECT pkInvestmentId,sCRMContactFullName,dContribution, CASE WHEN COUNT(t2.dContribution) <= 1 THEN CAST(MAX(t2.dContribution) AS varchar(20)) ELSE 'Various' END AS DateSold
FROM dbo.vew_DSG_BlackBookDetail AS t2
GROUP BY pkInvestmentId,sCRMContactFullName,dContribution)t
ON m.dContribution=t.dContribution
AND t.pkInvestmentId = m.pkInvestmentId 
AND t.sCRMContactFullName = m.sCRMContactFullName
WHERE (DATEPART(yyyy, m.dContribution) = '2006')
GROUP BY m.sCRMContactFullName, m.pkInvestmentId, m.sCRMAccountName, m.pkDealId, m.fkInvestorId
ORDER BY m.sCRMAccountName

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-31 : 14:12:51

Visakh that worked perfectly. I am reading through trying to understand this and may have a question or two for you in a bit so I can better understand what you did, but I wanted to say thank you very much for your help!!! You are truly a Flowing fount of Yak Knowledge!!! :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 14:25:30

You are always welcome

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-04-01 : 07:27:19

Imukari, I apologize that I did not thank you personally for responding to the post, but I was trying to fix a problem and visakh16 is the one that eventually helped me fix it. I do appreciate anyone trying to help out in the forums though and I am sorry if you were offended by not receiving a personal thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 07:37:56

If you want to use any present index for dContribution column, drop the DATEPART thingy and use

		AND m.dContribution >= '20060101'
		AND m.dContribution < '20070101'

instead.

E 12°55'05.25"
N 56°04'39.16"

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-04-07 : 14:00:24

Okay, I had one small bug with this and I think I need to add DISTINCT to my count to fix it, but it isn't doing exactly what I thought it would.

SELECT     TOP 100 PERCENT m.pkInvestmentId AS InvestmentID, m.pkDealId, m.sCRMAccountName AS Security, '1' AS Quantity, m.sCRMContactFullName, 
                      SUM(m.nDistributionAmt_Capital) AS DisCap, SUM(m.nDistributionAmt_Income) AS DisInc, SUM(m.nDistributionAmt_Recall) AS DisRec, m.fkInvestorId, 
                      MAX(t.DateSold) AS DateSold
FROM         dbo.vew_DSG_BlackBookDetail m INNER JOIN
                          (SELECT     pkInvestmentId, sCRMContactFullName, dContribution, CASE WHEN COUNT(DISTINCT t2.dContribution) 
                                                   <= 1 THEN CAST(MAX(t2.dContribution) AS varchar(20)) ELSE 'Various' END AS DateSold
                            FROM          dbo.vew_DSG_BlackBookDetail AS t2
                            WHERE      t2.dContribution >= '01-01-2008' AND t2.dContribution <= '03-31-2008' AND t2.nContributionAmt = 0
                            GROUP BY pkInvestmentId, sCRMContactFullName, dContribution) t ON m.dContribution = t.dContribution AND 
                      t.pkInvestmentId = m.pkInvestmentId AND t.sCRMContactFullName = m.sCRMContactFullName
WHERE     (DATEPART(yyyy, m.dContribution) = '2008')
GROUP BY m.sCRMContactFullName, m.pkInvestmentId, m.sCRMAccountName, m.pkDealId, m.fkInvestorId
ORDER BY m.sCRMAccountName

The problem I am having is that sometimes they have multiple dContribution on the same day and they want those to show as one date. So, say for instance, they had two matches with 3/28/2008 and no other items. They want that to show as 3/28/2008. But if they have 2/28/2008 and 3/28/2008 then show as various. It works if there is only one per day, but if there are two on one day it shows various. So, I added distinct, but now nothing shows as various. Just the dates. I get the results, but no various after addedind DISTINCT.

Thank you for any help on this.

ybechar
Starting Member

8 Posts

Posted - 2008-05-11 : 10:59:10

Hi, I have a similar question.
I have one table USER and another USR_PHONES I want to show only the first phone for each user (the unique identifier is CPI). So far I have been trying this query:

SELECT U.CPI, U.DIVISION_ID, U.LASTNAME, UP.PHONE, UP.TYPES, UP.SEQ
FROM dbo.USER AS U INNER JOIN
USR_PHONES AS UP ON UP.CPI =
(SELECT TOP (1) CPI
FROM USR_PHONES AS UP1
WHERE (CPI = U.CPI)
ORDER BY SEQ)

So far, this query is giving me ALL phone numbers instead of just the first one.....

Thanks in advance for your help....
ybechar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-11 : 12:56:58

quote:

Originally posted by dprichard

Okay, I had one small bug with this and I think I need to add DISTINCT to my count to fix it, but it isn't doing exactly what I thought it would.

SELECT     TOP 100 PERCENT m.pkInvestmentId AS InvestmentID, m.pkDealId, m.sCRMAccountName AS Security, '1' AS Quantity, m.sCRMContactFullName, 
                      SUM(m.nDistributionAmt_Capital) AS DisCap, SUM(m.nDistributionAmt_Income) AS DisInc, SUM(m.nDistributionAmt_Recall) AS DisRec, m.fkInvestorId, 
                      MAX(t.DateSold) AS DateSold
FROM         dbo.vew_DSG_BlackBookDetail m INNER JOIN
                          (SELECT     pkInvestmentId, sCRMContactFullName, dContribution, CASE WHEN COUNT(DISTINCT t2.dContribution) 
                                                   <= 1 THEN CAST(MAX(t2.dContribution) AS varchar(20)) ELSE 'Various' END AS DateSold
                            FROM          dbo.vew_DSG_BlackBookDetail AS t2
                            WHERE      t2.dContribution >= '01-01-2008' AND t2.dContribution <= '03-31-2008' AND t2.nContributionAmt = 0
                            GROUP BY pkInvestmentId, sCRMContactFullName, dContribution) t ON m.dContribution = t.dContribution AND 
                      t.pkInvestmentId = m.pkInvestmentId AND t.sCRMContactFullName = m.sCRMContactFullName
WHERE     (DATEPART(yyyy, m.dContribution) = '2008')
GROUP BY m.sCRMContactFullName, m.pkInvestmentId, m.sCRMAccountName, m.pkDealId, m.fkInvestorId
ORDER BY m.sCRMAccountName

try like this:-

SELECT TOP 100 PERCENT m.pkInvestmentId AS InvestmentID, m.pkDealId, m.sCRMAccountName AS Security, '1' AS Quantity, m.sCRMContactFullName, 
SUM(m.nDistributionAmt_Capital) AS DisCap, SUM(m.nDistributionAmt_Income) AS DisInc, SUM(m.nDistributionAmt_Recall) AS DisRec, m.fkInvestorId,MAX(t.DateSold)
FROM dbo.vew_DSG_BlackBookDetail m
INNER JOIN 
(SELECT pkInvestmentId,sCRMContactFullName, CASE WHEN COUNT(DISTINCT t2.dContribution) <= 1 THEN CAST(MAX(t2.dContribution) AS varchar(20)) ELSE 'Various' END AS DateSold
FROM dbo.vew_DSG_BlackBookDetail AS t2
GROUP BY pkInvestmentId,sCRMContactFullName)t
ON  t.pkInvestmentId = m.pkInvestmentId 
AND t.sCRMContactFullName = m.sCRMContactFullName
WHERE (DATEPART(yyyy, m.dContribution) = '2006')
GROUP BY m.sCRMContactFullName, m.pkInvestmentId, m.sCRMAccountName, m.pkDealId, m.fkInvestorId
ORDER BY m.sCRMAccountName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-11 : 13:00:13

quote:
Originally posted by ybechar

Hi, I have a similar question.
I have one table USER and another USR_PHONES I want to show only the first phone for each user (the unique identifier is CPI). So far I have been trying this query:

SELECT U.CPI, U.DIVISION_ID, U.LASTNAME, UP.PHONE, UP.TYPES, UP.SEQ
FROM dbo.USER AS U INNER JOIN
USR_PHONES AS UP ON UP.CPI =
(SELECT TOP (1) CPI
FROM USR_PHONES AS UP1
WHERE (CPI = U.CPI)
ORDER BY SEQ)

So far, this query is giving me ALL phone numbers instead of just the first one.....

Thanks in advance for your help....
ybechar

Please dont hijack treads. Can you please post this as a new thread along with your table structures too.

shijobaby
Starting Member

44 Posts

Posted - 2009-08-20 : 03:04:51

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources