Getting duplicated items in my chart

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)

Getting duplicated items in my chart

Author

Topic

twill227
Starting Member

8 Posts

Posted - 2014-05-28 : 16:33:21

I am getting the information that I need, but in some charts like the one seeing if a channel has items, it gives me multiple combinations. I understand the error behind my ways because it's like having 3 different people named John with different last names, it's hard to decide what to group by. However here is my code and I can't figure out why it's wrong

SELECT DISTINCT company.companyid
,userdata.firstname
,userdata.lastname
,userdata.username
,userdata.userid
,company.name AS [company name]
,carrier.name AS [integration]
,userdata.lastlogindate AS [last login]
,CASE WHEN (productsaleschannel.availablequantity) >= 1 THEN 'YES' ELSE 'NO' END AS ChannelHasItems
,CASE WHEN (product.quantityonhand) >= 1 THEN 'YES' ELSE 'NO' END AS UserAddedProd

FROM company

INNER JOIN companycarrier
on company.companyid = companycarrier.companyid
INNER JOIN carrier
on carrier.carrierid = companycarrier.carrierid
INNER JOIN userdata
on userdata.companyid = company.companyid
INNER JOIN saleschannel
on company.companyid = saleschannel.companyid
INNER JOIN productsaleschannel
on productsaleschannel.saleschannelid = saleschannel.saleschannelid
INNER JOIN product
on product.companyid = company.companyid

/* company as c on c.companyid = userdata.userid
INNER JOIN (
select SUM(availablequantity) as channelHasItems
from productsaleschannel
INNER JOIN company as c on c.companyid = userdata.userid group by availablequantity

) as mydata on mydata.availablequantity = productsaleschannel.availablequantity
*/
WHERE (companycarrier.carrierid = 4 OR companycarrier.carrierid = 5)

ORDER BY userdata.userid ASC

RESULTS
30 Darrin Kidd darrin.kidd@ltdsoftware.com 38 Darrin's Test Company 111 USPS (Endicia) 2014-05-18 21:52:30.000 YES NO
30 Darrin Kidd darrin.kidd@ltdsoftware.com 38 Darrin's Test Company 111 USPS (Stamps.com) 2014-05-18 21:52:30.000 YES NO
30 Darrin Kidd darrin.kidd@ltdsoftware.com 38 Darrin's Test Company 111 USPS (Endicia) 2014-05-18 21:52:30.000 NO NO
30 Darrin Kidd darrin.kidd@ltdsoftware.com 38 Darrin's Test Company 111 USPS (Stamps.com) 2014-05-18 21:52:30.000 NO NO
30 Darrin Kidd darrin.kidd@ltdsoftware.com 38 Darrin's Test Company 111 USPS (Endicia) 2014-05-18 21:52:30.000 YES YES
30 Darrin Kidd darrin.kidd@ltdsoftware.com 38 Darrin's Test Company 111 USPS (Stamps.com) 2014-05-18 21:52:30.000 YES YES

What Im Looking For
30 Darrin Kidd darrin.kidd@ltdsoftware.com 38 Darrin's Test Company 111 USPS (Endicia) 2014-05-18 21:52:30.000 YES YES
30 Darrin Kidd darrin.kidd@ltdsoftware.com 38 Darrin's Test Company 111 USPS (Stamps.com) 2014-05-18 21:52:30.000 YES YES

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-28 : 16:37:13

You have told us your query doesn't get the results you want, but you didn't tell us what you want. So, we need more information. Please see the link below for how to post your sample data and expected output so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

twill227
Starting Member

8 Posts

Posted - 2014-05-28 : 16:43:40

Updated. I'm looking for a way to break down my query even further but I can't figure out how.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-28 : 16:51:30

You haven't told us the rules for rules for breaking it down, so hard to say. It looks like you would want to drop the distinct, add more columns to your group by clause and then apply some aggregate functions to your selected columns. Alternatively, you could use a windows ranking function like ROW_NUMBER().

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources