Hi guys. Thanks to Wanna here on codingforums I was able to get my results from my mysql database with LEFT JOIN.
However I noticed that I had duplicated records in my output.

So basically i'll try to explane it the best I can.
I have three tables in my database.


CREATE TABLE IF NOT EXISTS `game_table` (
`g_id` int(11) NOT NULL AUTO_INCREMENT,
`g_title` varchar(255) NOT NULL,
PRIMARY KEY (`g_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

INSERT INTO `game_table` (`g_id`, `g_title`) VALUES
(1, '4-in-1 Fun Pack'),
(2, 'Kirby''s Dream Land'),
(3, 'Super Mario Land'),
(4, 'Super Mario Land 2: 6 Golden Coins'),
(5, 'Donkey Kong'),
(7, 'The Final Fantasy Legend'),
(8, 'Race Days: 2 Full Games on 1 Cartridge'),
(9, 'Final Fantasy Adventure');



CREATE TABLE IF NOT EXISTS `genre_table` (
`g_id` int(2) NOT NULL AUTO_INCREMENT,
`g_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`g_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

INSERT INTO `genre_table` (`g_id`, `g_name`) VALUES
(1, 'Action'),
(2, 'Adventure'),
(3, 'Beat''em Up'),
(4, 'Board Game'),
(5, 'Editor'),
(6, 'Fighting'),
(7, 'Gambling'),
(8, 'Game Show'),
(9, 'Platformer'),
(10, 'Puzzle'),
(11, 'RPG'),
(12, 'Racing'),
(13, 'Simulation'),
(14, 'Sports'),
(15, 'Strategy'),
(16, 'Misc');



CREATE TABLE IF NOT EXISTS `track_genre` (
`game_id` int(11) NOT NULL,
`genre_id` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `track_genre` (`game_id`, `genre_id`) VALUES
(1, 15),
(5, 10),
(5, 9);



Now what I tried to do was use LEFT JOIN in my php script query.
To select the genre names and display them in a list next to the game titles.
However some games have more genres then one and some have null values.
And when I add a extra field in track table eq to the genre with the same id as the game I get Duplicated Records.

Is there a way to solve this?
My current query looks like this:


SELECT `game_table`.`g_title`, `genre_table`.`g_name`
FROM `game_table`
LEFT JOIN `track_genre`
ON `game_table`.`g_id`=`track_genre`.`game_id`

LEFT JOIN `genre_table`
ON `track_genre`.`genre_id`=`genre_table`.`g_id`


This returns Donkey Kong two times because it has two genres.
Is there a way to solve this?

I dont think it returns duplicat because of thta, since you dont have track_genre.genre_id
in select part.

try


SELECT DISTINCT `game_table`.`g_title`, `....

Hi I tried DISTINCT but still the same result.
Heres is my qeury:


<?php

mysql_connect('localhost', 'root', 'password');
mysql_select_db('db');

$sql = 'SELECT `game_table`.`g_title`, `genre_table`.`g_name`,
FROM `game_table`

LEFT JOIN `track_genre`
ON `game_table`.`g_id`=`track_genre`.`game_id`

LEFT JOIN `genre_table`
ON `track_genre`.`genre_id`=`genre_table`.`g_id`';

$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result))
{
echo '<table width="660">';
echo '<tr>';
echo '<td>' . $row['g_title'] . '</td><td>' . $row['g_name'] . '</td>';
echo '</tr>';
echo '</table>';
}
?>


This returns:

Donkey Kong Platformer
Donkey Kong Puzzle


The problem is puzzle and platformer have the same game_id in the track_genre table.
But there has to be a way to tell the query that Donkey Kong has two genres platformer and puzzle.

SELECT `game_table`.`g_title`, group_concat(`genre_table`.`g_name`),
FROM `game_table`

LEFT JOIN `track_genre`
ON `game_table`.`g_id`=`track_genre`.`game_id`

LEFT JOIN `genre_table`
ON `track_genre`.`genre_id`=`genre_table`.`g_id`'

group by `game_table`.`g_title`;


See the result and tell if it is ok.


I don't know why people are stuffing php here, it just annoys me (-:
So you have to add .,' and stuff, at quantities needed, yourself in upper sample.

+ note , you can and should do:

SELECT gt.g_titlle, gt.x, gt.y,...
FROM game_table gt left join table2 t2 on gt.id = t2.id ... etc

This returns:

Donkey Kong Platformer
Donkey Kong Puzzle


Which is exactly correct.

And if you were using most any DB other than MySQL, that is what you would have to live with. You would use PHP code to change the *presentation* of the data to something like

Donkey Kong Platformer
Puzzle

or whatever you wanted.

But fortunately for you, you are using MySQL and you can indeed use GROUP_CONCAT as Bubikol has shown you.

Hi I tried to group the results together with GROUP BY.
However now it returns one game title with only one genre.
The output im looking for is this:


Donkey Kong Platformer / Puzzle


Also sorry if I posted this in the wrong forum but last time I asked about a php query from mysql the said I should post it here.

Im starting to think that i should just store the genre next to the games in one table.
I was trying to do database normalization on my tables.
But its harder then I thought.

Edit *

Bad example. see Old Pedant post.

it just came to me I should just store the genre ids like an array in one field in my database: 1,5,2 etc
A truly terrible idea, from any even moderate database design standpoint.

It makes it virtually impossible to do many useful queries.

I VERY VERY STRONGLY disagree with your decision. But it's yours.

If you had been patient, the answer to what you wanted is easy. Given your original *CORRECT* database design:

SELECT game_table.g_title, group_concat(genre_table.g_name SEPARATOR ' / ' ),
FROM game_table
LEFT JOIN track_genre ON game_table.g_id=track_genre.game_id
LEFT JOIN genre_table ON track_genre.genre_id=genre_table.g_id
GROUP BY game_table.g_title


If you'd just looked in the documentation you would have seen that:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

If you look at that, you'll see that you could even do

SELECT game_table.g_title, group_concat(genre_table.g_name ORDER BY g_name SEPARATOR ' / ' ),

to ensure that the genre names are in alphabetical order, if you want.

MySQL is powerful. But when you do things like store multiple values in a single field, you destroy its ability to do some very useful operations.

Here's an example: Given your "new" design, how would you answer a question such as this:

How many games belonging to 3 or more genres have at least 2 genres in common?

In your original design, the SQL for that is easy. In your current design it's essentially impossible.

Maybe you will never need such capabilities. If so, fine. But I hope this serves as a warning to other readers.

I'll try to be more patient next time.
Its just I thought I found the answer to my question because it displayed the result I wanted.
Thanks for the advice i'll also try to take closer look at the MSYQL documentation next time.