MySQL Three-Table Join Problem

I have 3 database tables as follows:

http://img175.imageshack.us/img175/9233/dbschema.jpg

I have an SQL command as follows:

SELECT r.id, r.title,
DATE_FORMAT(r.date, '%d/%m/%Y %H:%i') AS date,
c_c.recipe_id, c_c.cat_id, c.name
FROM recipe r
LEFT JOIN recipe_cat_choice c_c
ON r.id = c_c.recipe_id
LEFT JOIN recipe_cat c
ON c_c.cat_id = c.id
ORDER BY date DESC

…which outputs the following table:

http://img88.imageshack.us/img88/8498/tbloutput1.jpg

But instead of individual records being output, I’d like each recipe appear on 1 line with a tick in its category column should it have that category. If that makes sense.

Any suggestions/help?

Regards

Ha :slight_smile: yes it does! Which is why I was in two minds where to post the initial thread!

Anyway, the code which I originally has was:


	<?php foreach ($recipes as $recipe): ?>
		<tr>
		    <td class="title"><?php echo $recipe->title; ?></td>
		    <td class="algn-ctr">
			<?php if ($recipe->name == 'Gluten-Free') { echo '<img src="/assets/cms/images/tick.png" alt="Yes" />'; } else { echo ''; } ?>		    
		    </td>
		    <td class="algn-ctr">
			<?php if ($recipe->name == 'Dairy-Free') { echo '<img src="/assets/cms/images/tick.png" alt="Yes" />'; } else { echo ''; } ?>		    
		    </td>
		    <td class="algn-ctr">
			<?php if ($recipe->name == 'Wheat-Free') { echo '<img src="/assets/cms/images/tick.png" alt="Yes" />'; } else { echo ''; } ?>		    
		    </td>
		    <td class="algn-ctr">
			<?php if ($recipe->name == 'Ultra Low Fat') { echo '<img src="/assets/cms/images/tick.png" alt="Yes" />'; } else { echo ''; } ?>		    
		    </td>
		    <td class="algn-ctr">
			<?php if ($recipe->name == 'Vegetarian') { echo '<img src="/assets/cms/images/tick.png" alt="Yes" />'; } else { echo ''; } ?>		    
		    </td>
		    <td><?php echo $recipe->date; ?></td>
		    <td class="algn-ctr"><input type="radio" name="rad_rec_of_wk" value="1" /></td>
		    <td class="algn-ctr">
			<a href="#" title="Edit Recipe"><img src="/assets/cms/images/pencil.png" alt="Edit Recipe" /></a>
			<a href="#" title="Delete Recipe"><img src="/assets/cms/images/bin.png" alt="Delete Recipe" /></a>
		    </td>
		</tr>
	    <?php endforeach; ?>

But things may change if I use jOOOL’s SQL query.

That makes it a PHP question from now on… :wink:

Do you have some code already, from which we can start?

jOOOL, that SQL query was spot on, thank you kindly.

I am using PHP with this and I see the logic in what you are suggesting…implementing it is a different matter :wink: any help with that or are you purely an SQL whizz?

You will have to use your serverside language to achive that. You look at the r.id for the first row and save whatever box was ticked. Then you go to the next row, if it is the same id then again add whatever box was ticked. When r.id changes you ouput all of the previous row and restart the process.

You might find the result of this query easier to handle, but it doesn’t solve the problem:

SELECT 
  r.id, 
  r.title,
  DATE_FORMAT(r.DATE, '&#37;d/%m/%Y %H:%i') AS DATE,
  GROUP_CONCAT(c_c.cat_id) AS cat_ids, 
  GROUP_CONCAT(c.name) AS cat_names
FROM 
   recipe r
LEFT JOIN 
   recipe_cat_choice c_c ON r.id = c_c.recipe_id
LEFT JOIN 
   recipe_cat c   ON c_c.cat_id = c.id
GROUP BY
   r.id
ORDER BY DATE DESC