How do I do this ridiculous SQL multiple join statement?

Question

Ok, I'm scrapping the old since the code has changed a lot. Here's what I now have:

SELECT
   article.articleId,
   article.articleName,
   article.articleStoryTypeId,
   artIndustry.industryName,
   author.userPrefix,
   author.userMiddleName,
   article.articleStatus,
   article.articleCreateDate,
   article.articleModifyDate
FROM
   users
LEFT JOIN clickTrack
 ON users.userId = clickTrack.clickUserId
LEFT JOIN article
 ON clickTrack.clickDocumentId = article.articleId
LEFT JOIN users author
 ON author.userId = article.articleAuthorId
LEFT JOIN industry artIndustry
 ON artIndustry.industryId =    substring(article.articleIndustryId,0,charindex(',',article.articleIndustryId)-1)
WHERE ((clickTrack.clickDocumentTable = 'breaking')
     OR (clickTrack.clickDocumentTable = 'article'))
  AND article.articleCreateDate > '1/1/2008 0:00:00 AM'
  AND ((users.userindustryId = '1') 
    OR (users.userindustryId LIKE '%,1') 
    OR (users.userindustryId LIKE '%,1,%') 
    OR (users.userindustryId LIKE '1,%'))
GROUP BY
   article.articleId,
   article.articleName,
   article.articleStoryTypeId,
   artIndustry.industryName,
   author.userPrefix,
   author.userMiddleName,
   article.articleStatus,
   article.articleCreateDate,
   article.articleModifyDate
Order By article.articleId

Data returned looks like:

8332, The Ideal Situation, Breaking News, NULL, NULL, NULL, Prod, 2011-07-25 14:48:01.203, 2011-08-09 07:41:29.373

I added the commas. The 3 null fields are the industry name, user's first name, and user's last name. (Yes, someone named the table fields wrong.) I'm extremely tired, so I don't know if this is enough info or not. Just let me know if you need more.

EDIT

It's working now. I had to take the "-1" out of the left join on clause. Oh yeah, and I'm just scrapping the name fields. That's still not working. >.< But I'm done.

First problem i see is that you can't decide whether you want the table alias to be `users2` or `user2`. — cHao, Oct 11 '11 at 00:12
That is almost completely uinintelligible.. How about trying to describe what you're trying to do? — StevieG, Oct 11 '11 at 00:19
The errors are what you will see if you have fields in the SELECT portion but not in GROUP BY. — Jamie F, Oct 11 '11 at 00:54

Jamie F · Accepted Answer · 2011-10-11T00:35:44.160

0

Is the author list really the same table as the user list? What are you trying to query for?

One problem is that you are trying to return fields that aren't in your group by.

Try this:

SELECT
   article.articleId,
   article.articleName,
   article.articleStoryTypeId,
   artIndustry.industryName,
   author.userPrefix,
   author.userMiddleName,
   article.articleStatus,
   article.articleCreateDate,
   article.articleModifyDate,
FROM
   users
LEFT JOIN clickTrack
 ON users.userId = clickTrack.clickUserId
LEFT JOIN article
 ON clickTrack.clickDocumentId = article.articleId
LEFT JOIN users author
 ON author.userId = article.articleAuthorId
LEFT JOIN industry artIndustry
 ON artIndustry.industryId = article.articleIndustryId
WHERE ((clickTrack.clickDocumentTable = 'breaking')
     OR (clickTrack.clickDocumentTable = 'article'))
  AND article.articleCreateDate > '1/1/2008 0:00:00 AM'
  AND users.industryId = 1
GROUP BY
   article.articleId,
   article.articleName,
   article.articleStoryTypeId,
   artIndustry.industryName,
   author.userPrefix,
   author.userMiddleName,
   article.articleStatus,
   article.articleCreateDate,
   article.articleModifyDate,
Order By article.articleId

edited Oct 11 '11 at 00:35

answered Oct 11 '11 at 00:12

Jamie F

23,189
5
61
77

Oh, I didn't realize that they had to be in the group by. I'm used to mysql, and they don't have to be included. I'll try that. – James Oct 11 '11 at 00:15
Thanks, but that didn't work either. (Yes, I corrected for my initial users2 mistake.) – James Oct 11 '11 at 00:16
Well I don't know if there are errors or not since I'm working with ASP and built my own query window. But no results, and no visible errors. – James Oct 11 '11 at 00:28
You'll need to do some more troubleshooting yourself. First remove the new columns from the results. Then remove on of the new joins, then the other. When do you see results? (I think something else is happening in this query, because the left joins shouldn't remove rows. Did you change the where clause?) – Jamie F Oct 11 '11 at 00:37
No, it hasn't been changed. I just added the errors I'm receiving though, and will try your suggestion as well. – James Oct 11 '11 at 00:39
Yes, the users are the creators of the articles. As for trying it, it was very close I believe. I got Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '6,' to data type int. Looking at the data, it seems that the article industryId field can have multiple values comma delimited... – James Oct 11 '11 at 01:01
If it wasn't for your code, I wouldn't have been able to finish. Thank you. – James Oct 11 '11 at 01:59

StevieG · Answer 2 · 2011-10-11T00:54:01.887

0

Ah, ok, try this:

SELECT 
  article.articleId,
  article.articleName, 
  article.articleStoryTypeId, 
  a.industryName, 
  a.userPrefix, 
  a.userMiddleName, 
  article.articleStatus, 
  article.articleCreateDate, 
  article.articleModifyDate,
  count(*)
FROM (select * from users
      INNER JOIN industry ON industry.industryId = users.userIndustryId 
                          AND industry.industryId = 1) a
LEFT OUTER JOIN clickTrack ON users.userId = clickTrack.clickUserId 
                     AND clickTrack.clickDocumentTable IN ('breaking','article')
LEFT OUTER JOIN article ON clickTrack.clickDocumentId = article.articleId 
                  AND article.articleCreateDate > '1/1/2008 0:00:00 AM' 
GROUP BY 
  article.articleId,
  article.articleName, 
  article.articleStoryTypeId, 
  a.industryName, 
  a.userPrefix, 
  a.userMiddleName, 
  article.articleStatus, 
  article.articleCreateDate, 
  article.articleModifyDate
ORDER BY 
  article.articleId

edited Oct 11 '11 at 00:54

answered Oct 11 '11 at 00:29

StevieG

8,639
23
31

Thanks, but still nothing returned. Also, in this scenario, the author would have had to have clicked on the link to go to their own article in order to show up. – James Oct 11 '11 at 00:34
Without that ',' before ORDER BY, I get a null return set, but no errors. – James Oct 11 '11 at 00:53
sorry, forgot to add the outer keyword.. try again? – StevieG Oct 11 '11 at 00:57
Thank you, but nothing is still returned. I appreciate the attempts though. – James Oct 11 '11 at 01:15

How do I do this ridiculous SQL multiple join statement?

2 Answers2