Selecting with subqueries in MySQL

Question

I know this is not a good practice of creating a table, so please let those thing aside first.

I have a table, let say table A and table B

Table A :
| ID | fk_tableB_ID | title
-------------------------
| 10 | 1,2          | title 1
| 11 | 3            | title 2

Table B :
| ID | category
---------------
| 1  | cat1
| 2  | cat2
| 3  | cat3

I tried this query:

SELECT
    a.ID,
    a.title,
    (
        SELECT
            group_concat(b.category)
        FROM
            tableB B
        WHERE
            B.ID IN (a.fk_tableB_ID)
    ) as category
FROM
    tableA a

It return :

| ID | category  | title
-----------------------
| 10 | cat1      | title1
| 11 | cat3      | title2            

But that's not what i want. What i want is

| ID | category  | title
-----------------------
| 10 | cat1,cat2 | title1
| 11 | cat3      | title2

Please help, what is the correct mysql query code so i can get that table above. Thx in advance

Answer 1

A better way to do this would be to have another table in the middle called something like 'a_has_category'

Table a :
| ID | title
---------------
| 10 | title 1
| 11 | title 2

Table category :
| ID | category
---------------
| 1  | cat1
| 2  | cat2
| 3  | cat3

Table a_has_Category :
| ID | fk_a | fk_category
---------------
| 1  | 10   | 1
| 2  | 10   | 1
| 3  | 11   | 2

Then use a couple of INNER JOINS to build your query

SELECT a.title, B.category FROM a
INNER JOIN a_has_category
ON fk_a = a_ID
INNER JOIN category
ON fk_category = category_ID

Once you get your results back, concatenate the categories into a string with commas between using PHP's IMPLODE function.

Answer 2

Yea, that's pretty much how it will work if you are storing multiple values in one cell. Save yourself the trouble and design your tables so that there's only one fk_tableB_ID per one row. You can combine the values to one row in your programming language of choice, as it is not feasible in MySQL.

Something like this should work for you:

Table A :
| ID | page_ID | fk_tableB_ID | title
-------------------------
| 7  | 10      | 1            | title 1
| 8  | 10      | 2            | title 1
| 9  | 11      | 3            | title 2

Table B :
| ID | category
---------------
| 1  | cat1
| 2  | cat2
| 3  | cat3

And your queries will simplify considerably:

SELECT
    a.page_ID, 
    b.category, 
    a.title 
FROM 
    table_a a 
LEFT JOIN
    table_b b 
ON 
    a.fk_tableB_ID = b.ID

And if you happen to use PHP, do the combining in there. This should work:

$r = mysql_query('SELECT ... ');

$result = array();

while($row = mysql_fetch_assoc($r)) {
    $result[$row['page_ID']]['title'] = $row['title'];
    $result[$row['page_ID']]['categories'][] = $row['category'];
}

Which should result in something like this:

Array
(
    [10] => Array
        (
            [title] => title 1
            [categories] => Array
                (
                    [0] => cat1
                    [1] => cat2
                )

        )

    [11] => Array
        (
            [title] => title_2
            [categories] => Array
                (
                    [0] => cat3
                )

        )    
)

And if you want to use that array to build a table similar to your example, you can use this:

echo "| ID     | category            | title\n";
echo "----------------------------------------\n";
foreach($array as $page_ID => $row) {
    echo '| '.str_pad($page_ID, 7);
    echo '| '.str_pad(implode(',', $row['categories']), 20);
    echo '| '.$row['title']."\n";
}

Which will output this:

| ID     | category            | title
----------------------------------------
| 10     | cat1,cat2           | title_1
| 11     | cat3                | title_2

Which is exactly what you wanted. Of course you probably want to output a HTML table but you get the idea.

Answer 3

I know you don't want to hear this but multiple values in a table field is not a good design especially if this field references another table.
If you want to create a many-to-many relationship, you need an intermediate table:

Table A :
| ID | title
------------
| 10 | title 1
| 11 | title 2


Table B :
| ID | category
---------------
| 1  | cat1
| 2  | cat2
| 3  | cat3

Table A_B:
| fk_tableA_ID | fk_tableB_ID
-----------------------------
| 10           | 1
| 10           | 2
| 11           | 3

I don't think that it is possible to do what you want in a single query (maybe in a stored procedure) but lets see what others come up with.

Having only one value in a field is called the first normal form (1NF). It is the very first step of database optimization.

Answer 4

the problem with doing it the way you are trying to do is that the IN function needs an array. If you really need to go down this route you will have to run a function on the string to create an array and then use that in the IN statement.

an example is here http://forums.mysql.com/read.php?60,78776,242420#msg-242420

but i would recommend adding the link table instead.

Answer 5

As mentioned befor, having multiple foreign keys in one column is bad design you have to use an n:m table. See this table structure as example: (yeah i left out some indexes :)

CREATE TABLE IF NOT EXISTS `a` (   `id` int(11) NOT NULL AUTO_INCREMENT,  `title` varchar(50) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `a` (`id`, `title`) VALUES (1, 'first row'), (2, 'second row'), (3, 'third row'), (4, 'fourth row');



CREATE TABLE IF NOT EXISTS `ab` (   `a_id` int(11) NOT NULL,   `b_id` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;



INSERT INTO `ab` (`a_id`, `b_id`) VALUES (1, 1), (1, 2), (1, 4), (2, 5), (3, 5), (4, 6), (4, 5), (3, 6);


CREATE TABLE IF NOT EXISTS `b` (   `id` int(11) NOT NULL AUTO_INCREMENT,  `category` varchar(50) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;


INSERT INTO `b` (`id`, `category`) VALUES (1, 'Cat1'), (2, 'Cat2'), (3, 'Cat3'), (4, 'Cat4'), (5, 'Cat5'), (6, 'Cat6');

Then you can use this query:

SELECT a.id, a.title, group_concat( b.category )
FROM a
LEFT JOIN ab ON a.id = ab.a_id
LEFT JOIN b ON b.id = ab.b_id
GROUP BY a.id;

which results in:

id  title   group_concat(b.category)
1   first row   Cat4,Cat1,Cat2
2   second row  Cat5
3   third row   Cat5,Cat6
4   fourth row  Cat5,Cat6