CODEWITHSUNDEEP
phpmysql
Anuj
Anuj

Reputation: 333

MySQL query for merging two tables to get a single result

I have two tables with a single common field. Here is how these two table structures are

Table 1

+--ID--+--Title----+----others---+
| 123  | Title 1   | other values|
| 124  | Title 2   | other values|
| 125  | Title 3   | other values|
| 126  | Title 4   | other values|
+------+-----------+-------------+

Table 2

+--ID--+--Tag ID--+----others---+
| 123  |   11     | other values|
| 123  |   12     | other values|
| 123  |   13     | other values|
| 123  |   14     | other values|
| 124  |   15     | other values|
| 124  |   16     | other values|
| 125  |   17     | other values|
| 126  |   18     | other values|
+------+----------+-------------+

I want to show that Article ID 123 have 4 tags i.e 11,12,13 & 14 like the table below

+--Article ID--+--Article Title--+--Tags--+--Tag IDs------+
|     123      |    Title 1      |    4   | 11, 12, 13, 14|
|     124      |    Title 2      |    2   |     15, 16    |
|     125      |    Title 3      |    1   |        17     |
|     126      |    Title 4      |    1   |        18     |
+--------------+-----------------+--------+---------------+

I'm very new to PHP and MySQL and trying to learn it.

Someone please help me to know how I can get the desired result.

Upvotes: 2

Views: 806

Answers (5)

Vimalnath
Vimalnath

Reputation: 6463

SELECT t1.id AS 'Article ID',
t1.title AS 'Article Title', 
count( t2.tag_id ) AS 'Tags', 
GROUP_CONCAT( t2.Tag_Id order by t2.Tag_id ASC) AS `Tag IDs`
FROM table1 t1
JOIN table2 t2 ON ( t1.id = t2.id )
GROUP BY t1.id;

Hope this works!

Upvotes: 1

Bud Damyanov
Bud Damyanov

Reputation: 31839

Merging tables is done by using JOIN (LEFT, RIGHT INNER) keyword. For example:

SELECT T1.*, T2.*, COUNT(T2.ID) AS TAG_COUNT, GROUP_CONCAT(T2.ID) AS TAG_IDS
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 
ON T1.TAG_ID = T2.ID

This type of joining structure suggest, that you have a common filed, by which the join itself is made. In your table structure I assume, that first table T1 is Article's table, second one is Tags table. In this case you need to have a field in your T1 table, which corresponds to the field in T2, this is most probably ID.

Edit: As final step you need to concatenate the results in order to achieve the desired structure, as many of the answers said - use GROUP_CONCAT() function.

Upvotes: 0

Jrgns
Jrgns

Reputation: 25188

This query should work (with some tweaking).

SELECT `ID` AS `t1`.`Article ID`, `t2`.`Title` AS `Article Title`, COUNT(`t2`.`ID`) AS `Tags`,
GROUP_CONCAT(`t2`.`ID`) AS `Tag IDs` FROM `Articles` AS `t1`
LEFT JOIN `Tags` AS `t2` ON `t1`.`ID` = `t2`.`ID`
GROUP BY `t1`.`ID`

There's a couple of other options to the GROUP_CONCAT function, but the defaults should work fine for what you want.

Upvotes: 2

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

SELECT table1.*, 
       table2.tagidgroup 
FROM   table1 
       INNER JOIN (SELECT id, 
                          Group_concat(tagid SEPARATOR ','  ) AS tagidgroup 
                   FROM   table2 
                   GROUP  BY id) Table2 
               ON table1.id = Table2.id

Upvotes: 0

Donatas Olsevičius
Donatas Olsevičius

Reputation: 1350

As somebody already mentioned in the comments, you should use GROUP_CONCAT(). Here are some examples and options - http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Upvotes: 0

Related Questions