Is it possible to perform a dynamic SQL join?

SQL: How to Join tables with Composite Key?

HI, I have a table called pages which has a composite key of (pageId, dateScanned) and this goes as a foreign key to the statuses table ... I want to join these two tables + more ... Here is the complete query im running but the results are not correct. In each column of the result is the total number of rows. Im quite confident that this is because of the composite key and I dont know how to join with composite keys... Please Help its Urgent SELECT pages.pageId, pages.dateScanned, COUNT(commentTags.tag LIKE '%positive%') AS positive, COUNT(commentTags.tag LIKE '%negative%') AS negative, COUNT(comments.gender = 'female') AS females, COUNT(comments.gender = 'male') AS males, COUNT(commentTags.tag = 'strong_positive') AS strongPositives, COUNT(commentTags.tag = 'positive') AS positives, COUNT(commentTags.tag = 'weak_positive') AS weakPositives, COUNT(commentTags.tag = 'neutral') AS neutrals, COUNT(commentTags.tag = 'weak_negative') AS weakNegatives, COUNT(commentTags.tag = 'negative') AS negatives, COUNT(commentTags.tag = 'strong_negative') AS strongNegatives, COUNT(statuses.statusId) AS totalStatuses, SUM(statuses.likesCount) AS totalLikesCount, SUM(statuses.sharesCount) AS totalSharesCount, COUNT(comments.commentId) AS totalComments, COUNT(DISTINCT comments.userName) AS uniqueUsers FROM pages JOIN statuses ON pages.pageId = statuses.pageId JOIN comments ON comments.statusID = statuses.statusId JOIN commentTags ON comments.commentId = commentTags.commentId WHERE pages.pageId = '120821348649' AND pages.dateScanned = '2013-05-26'
Answer:

To join a table based on a compound key you just have to add an AND clause to your JOIN criteria: ...JOIN statuses ON pages.PageId = statuses.pageid AND pages.datescanned = statuses.datescanned...

Grant Fritchey at Quora Visit the source

Was this solution helpful to you?

Other answers

I updated the query with the help of , Thank you... The new query that works is as follows: SELECT pages.pageId, pages.dateScanned, SUM(commentTags.tag LIKE '%positive%') AS positive, SUM(commentTags.tag LIKE '%negative%') AS negative, SUM(comments.gender = 'female') AS females, SUM(comments.gender = 'male') AS males, SUM(commentTags.tag = 'strong_positive') AS strongPositives, SUM(commentTags.tag = 'positive') AS positives, SUM(commentTags.tag = 'weak_positive') AS weakPositives, SUM(commentTags.tag = 'neutral') AS neutrals, SUM(commentTags.tag = 'weak_negative') AS weakNegatives, SUM(commentTags.tag = 'negative') AS negatives, SUM(commentTags.tag = 'strong_negative') AS strongNegatives, COUNT(DISTINCT statuses.statusId) AS totalStatuses, SUM(DISTINCT statuses.likesCount) AS totalLikesCount, SUM(DISTINCT statuses.sharesCount) AS totalSharesCount, COUNT(DISTINCT comments.commentId) AS totalComments, COUNT(DISTINCT comments.userName) AS uniqueUsers FROM pages JOIN statuses ON pages.pageId = statuses.pageId AND pages.dateScanned = statuses.dateScanned JOIN comments ON comments.statusID = statuses.statusId JOIN commentTags ON comments.commentId = commentTags.commentId WHERE pages.pageId = '120821348649' AND pages.dateScanned = '2013-05-28' This gives correct results

Haseeb Khan

Related Q & A:

How To Join Cbi As An Informer?Best solution by Quora
How To Join The New World Order?Best solution by Yahoo! Answers
How to write Join Query for two tables without foreign key in Yii2?Best solution by Stack Overflow
How do you take the flip key blade out of a vw key fob?Best solution by Yahoo! Answers
I have create yahoo Profile to join a yahoo group, but i unable to join any group, How to Join a group.Best solution by Yahoo! Answers

Just Added Q & A:

How many active mobile subscribers are there in China?Best solution by Quora
How to find the right vacation?Best solution by bookit.com
How To Make Your Own Primer?Best solution by thekrazycouponlady.com
How do you get the domain & range?Best solution by ChaCha
How do you open pop up blockers?Best solution by Yahoo! Answers

For every problem there is a solution! Proved by Solucija.

Got an issue and looking for advice?
Ask Solucija to search every corner of the Web for help.
Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.