CODEWITHSUNDEEP
phpmysqlsql
Bob Cavezza
Bob Cavezza

Reputation: 2850

3 Table Join with addition inside of it

    $sql_select = "SELECT p.ideaTitle, f.feedback, f.contact
    FROM ideas p
    RIGHT JOIN feedback f ON f.ideaSlug = p.ideaSlug
    ORDER BY p.ideaDateTime, f.dateTime
     ";

This is what I'm working with on the new table. I want it to display the ideaTitle, all feedback for each ideaTitle (which this gets above), and then the total number of Votes (both boolean 1 & 0) that we get in the sql code below. I'm just having a hell of a time combining them in the correct way. Would it be better to break this up into 2 separate sql commands? 

    SELECT "p.ideaTitle,
          SUM(CASE WHEN v.vote = '1' THEN 1 ELSE 0 END) AS yesCount,
          SUM(CASE WHEN v.vote = '0' THEN 1 ELSE 0 END) AS noCount
         FROM ideas p
    LEFT JOIN votes v ON v.ideaSlug = p.ideaSlug
     GROUP BY p.ideaSummary
     ORDER BY yesCount DESC
     LIMIT 20";

Current Code I'm using to Display the data. 

                $result = mysql_query($sql_select,$link) or die("Insertion Failed:" . mysql_error());   
                $x=0;
                while ($row = mysql_fetch_array($result)) {
                    if ($row['ideaTitle'] != $previousTitle) {
                        ?> <h3> <?php echo stripslashes($row['ideaTitle']); ?> </h3>
                       <h4> Yes: <?php // echo $number of yes votes; ?> </h4>
                       <h4> No:  <?php // echo $number of no votes; ?></h4>
                    <?php } ?>
                    <blockquote>
                    <p><em> <?php echo stripslashes($row['feedback']); ?> </em> </p></blockquote>
                    <?php 
                    $previousTitle = $row['ideaTitle']; ?>
                <?php }

Upvotes: 0

Views: 87

Answers (1)

Mark Byers
Mark Byers

Reputation: 838376

I'd do this as two separate queries. You can do it in one query, but there's no real purpose in doing so.

If you really want to try to do it in one query it would probably look something like this:

SELECT p.ideaTitle, f.feedback, f.contact, p2.yesCount, p2.noCount
FROM ideas p
LEFT JOIN feedback f ON f.ideaSlug = p.ideaSlug
LEFT JOIN (
    SELECT
        p.ideaSlug,
        SUM(CASE WHEN v.vote = '1' THEN 1 ELSE 0 END) AS yesCount,
        SUM(CASE WHEN v.vote = '0' THEN 1 ELSE 0 END) AS noCount
    FROM ideas p
    LEFT JOIN votes v ON v.ideaSlug = p.ideaSlug
    GROUP BY p.ideaSlug
) p2
ON p.ideaSlug = p2.ideaSlug

However I don't see any advantage in doing this, and I see a few disadvantages.

Upvotes: 1

Related Questions