Select w/ Joins returning to many rows... - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Select w/ Joins returning to many rows...

Select w/ Joins returning to many rows...

Options

Previous Topic · Next Topic

Mark Haggarty

Posted: Monday, September 10, 2007 7:41:26 AM

Rank: Newbie

Joined: 9/7/2007
Posts: 2
Points: 6
Location: Providence RI

Hello Scott (et Al):

Today I find myself struggling with a report request from a database that I've just been introduce to and could really use some help/assistance/direction if you have a moment.

Although I have been able to render a report similar to what the requesting party is asking for it isn't exactly what they want. At this time my select statement pulls data from 6 tables (dbo.TBClient, dbo.TBClientRoles, dbo.TBClientDealTypes, dbo.TBDealTypes, dbo.TBClientIndustry and dbo.TBIndustry) however my JOIN to TBIndustry is producing 23 rows of data for each client.

Request/Problem: Instead of 23 rows of data for each TBClient.ClientID, return 1 row of data for each TBClient.ClientID and have a column for each "IndustryLabel" in TBIndustry and populate each "IndustryLabel" column with either an "X" if the client has a record in TBClientIndustry table for that "Industry" or else just leave the column blank.

--Currently returns 23 rows for each Client record as there are 23 "Industry Labels" in the Industry table,
--Needing to have a column for each "Industry Label" (23 at this point...could/will grow with the addition of industries) and population of "X" or <blank> if the client has that Industry selection or not...

--*************** Start of SQL ***************

SELECT c.ClientID, c.ClientName, c.IsAnnounced, cr.RoleName as [Client Role], c.NameOfOtherParty,
c.DescriptionOfDeal, c.DateAnnounced, c.TypeOfDate, c.DealValue, c.CrossBorderInfo,
c.EAPDLawyers, c.Comments, c.ParentID, c.Cancel, c.CreateDate, c.CreateUser,
c.UpdateDate, c.UpdateUser, c.CancelUser, c.CancelDate, dt.dtypename as [Deal Type], c.TombstoneApprovalCode, c.TombstoneApprovalFile,
i.IndustryLabel, replace(replace(ci.value,-1,'X'),0,'') Value
FROM TBClient c INNER JOIN
TBClientIndustry ci ON c.ClientID = ci.ClientID INNER JOIN
TBIndustry i ON ci.IndustryID = i.IndustryId INNER JOIN
TBClientRoles cr ON c.RoleOfClient = cr.RoleID INNER JOIN
TBClientDealTypes cdt ON c.ClientID = cdt.ClientID INNER JOIN
TBDealTypes dt ON cdt.DealTypeID = dt.DTypeID
WHERE c.cancel=0 and i.cancel=0 and cr.cancel=0

--*************** End of SQL ***************

Do I need to have a Cursor(s) a loop of some sort or a temp Table for Industry data???...I don't know....color me flummoxed Confused

I'm not sure what is the most efficient way to do this...if you could lend some help/suggestions/direction I would be extremely appreciative.
Thanks, -Mark

fairDmaster

Posted: Saturday, November 17, 2007 12:37:53 AM

Rank: Newbie

Joined: 11/16/2007
Posts: 2
Points: 12
Location: INDIA

Hi Mark,

Try with the below one i used to do like this way, i hope it will solve your problem
Roll Eyes (Sarcastic)

first create this seperate fuction for generate the X value..

step 1:

create function fn_replaceXvalue
as
( @clientId int --use your datatype here

return varchar

begin

declare @val varchar(1)
if exists(select industrylabel from TBIndustry where IndustryId =
(select IndustryID from TBClientIndustry where ClientID = @clientId) )
set @val = 'X'
else
set @val = ''

return @val

end

step 2:

then call the function like below from the select query....

SELECT c.ClientID,
c.ClientName,
c.IsAnnounced,
cr.RoleName as [Client Role],
c.NameOfOtherParty,
c.DescriptionOfDeal,
c.DateAnnounced,
c.TypeOfDate,
c.DealValue,
c.CrossBorderInfo,
c.EAPDLawyers,
c.Comments,
c.ParentID,
c.Cancel,
c.CreateDate,
c.CreateUser,
c.UpdateDate,
c.UpdateUser,
c.CancelUser,
c.CancelDate,
dt.dtypename as [Deal Type],
c.TombstoneApprovalCode,
c.TombstoneApprovalFile,
i.IndustryLabel,
-- replace(replace(ci.value,-1,'X'),0,'') Value

-- Below function return the X or '' from the seperate scalar valued function.
--each row it call the function and return the value
(dbo.fn_replaceXvalue (ci.clientId)) as IndustryLabelValue

FROM TBClient c
INNER JOIN TBClientIndustry ci ON c.ClientID = ci.ClientID
INNER JOIN TBIndustry i ON ci.IndustryID = i.IndustryId
INNER JOIN TBClientRoles cr ON c.RoleOfClient = cr.RoleID
INNER JOIN TBClientDealTypes cdt ON c.ClientID = cdt.ClientID
INNER JOIN TBDealTypes dt ON cdt.DealTypeID = dt.DTypeID
WHERE c.cancel=0 and i.cancel=0 and cr.cancel=0

---------------------------

Tip:1

If you want all the rows in the table try with Left Join i/o InnerJoin for the
tables othern than exact match.

Tip:2

you can also try with CASE without using seperate function withthin that select query itself.

Hope this will help you................

fair....trying 2 be a Master

Best Regards,

fair...trying2...b...a...Master
--------------------------------------------

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.