November 17, 2011 at 11:37 am
The below query is giving me 4 rows instead of 1 row i.e I am getting the same answer 4 times in my result set.
Ex:If my result set should return 4 rows it is returning 16 rows(each row is showing up 4 times)
select
(
case
when q_afftypeid = '1'
then
(
case
when m.datasource = 'M.MDB'
then
P_LN + ',' + P_FN
else
P_LN
end
)
else
'N/A'
end
)
as P_FullName,
rg.DDescr as G_DN,
ri.DDescr as I_DN,
c.qcat_Descr as QACat,
sc.qsubcat_Descr as QASubCat,
* from
d_m m
left join
[ref].[dbo].[D_HDS] rg on
rg.HDCode = m.G_DID
left join
[ref].[dbo].[D_HDS] ri on
ri.HDCode = m.I_DID
left join l_Qcat c on
c.id = m.q_qcatd
left join l_Qsubcat sc on
sc.id = m.q_qsubcatd
where
CONVERT(CHAR(8),a_datetime,112)
=
CONVERT(CHAR(8),getdate(),112)
or
CONVERT(CHAR(8),a_datetime,112)
=
CONVERT(CHAR(8),getdate() - 1,112)
ORDER BY
a_datetime desc
Thanks
November 17, 2011 at 11:42 am
More than likely it is due to the tables joined together having 2 or more rows that link together.
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 17, 2011 at 11:54 am
As mentioned, the cause of this is duplicate key data in multiple tables.
from
d_m m
left join
[ref].[dbo].[D_HDS] rg on
rg.HDCode = m.G_DID
left join
[ref].[dbo].[D_HDS] ri on
ri.HDCode = m.I_DID
left join l_Qcat c on
c.id = m.q_qcatd
left join l_Qsubcat sc on
sc.id = m.q_qsubcatd
The easiest way to deal with this is to do a SELECT COUNT(*) from this structure with the intended where clause and include each table 1 by one. Once you've found out where you've got duplicated keys, you can track down if you need to use a larger join condition or if you need to do a data cleanup.
I should also mention, this where clause:
CONVERT(CHAR(8),a_datetime,112)
=
CONVERT(CHAR(8),getdate(),112)
or
CONVERT(CHAR(8),a_datetime,112)
=
CONVERT(CHAR(8),getdate() - 1,112)
... is NEVER going to be able to do anything but scan the indexes. You'd be better off with something like:
-- Sometime yesterday or today
a_datetime >= dateadd( dd, datediff( dd, 0, getdate()), -1) and a_datetime < dateadd( dd, datediff( dd, 0, getdate()), 1)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 18, 2011 at 9:18 am
Hi sqlserver12345
Your query seems have more records of same IDs in the left outer joins means if there is ID=1 in base table and 3records in left table then it gonna show you 3records each for left table.if you sure that it have to be one record then make sure all left join table have unique ID.
Or you can go with a small testing which i usually do
add one left table join and see the result and go with 2nd left table and see the result with this way u will get to know at which level your getting more records.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply