CODEWITHSUNDEEP
phpmysqldatabasejoin
ryuutatsuo
ryuutatsuo

Reputation: 4006

Join two tables with one table has multiple rows matching

Trying to figure out if it is possible to create a query where you join tables, table one is smaller than table two, table two has multiple references matching table one entries, the query would output a joining where table one length is preserved but you just add more columns. Not sure if that makes sense so here is a example of what I am after 

Table One                                  Table two
+-----------------------------+           +-----------------------------+
| id | english  |  definition |           | id | word_id  |  sentence   |
+-----------------------------+           +-----------------------------+
|1   |    A1    |    blah     |           |1   |    1    |   blahblah1  |
|2   |    B4    |    blah2    |           |2   |    1    |   blahblah2  |
+-----------------------------+           |3   |    1    |   blahblah3  |
                                          |4   |    2    |   blahblah4  |
                                          |5   |    2    |   blahblah5  |
                                          +-----------------------------+

********* Query should return something like *****************

+----------------------------------------------------------------+
| id | english  |  definition | sentence | sentence2 | sentence3 | 
+----------------------------------------------------------------+
|1   |    A1    |    blah     | blahblah1|  blahblah2| blahblah3 |
|2   |    B4    |    blah2    | blahblah4|  blahblah5|           |
+----------------------------------------------------------------+

My Current query looks like this and results in:

$query = "SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.word_id";

Resulting in:

+----------------------------------------+
| id | english  |  definition | sentence |
+----------------------------------------+
|1   |    A1    |    blah     | blahblah1|
|1   |    A1    |    blah     | blahblah2|
|1   |    A1    |    blah     | blahblah3| 
|2   |    B4    |    blah2    | blahblah4|
|2   |    B4    |    blah2    | blahblah5|
+----------------------------------------+

I am working with PHP and MySql.

UPDATE!!

Staying with my original query and manipulating the results with PHP getting good performance too. Let me know if you need me to post my code.

Upvotes: 1

Views: 3991

Answers (2)

David Lin
David Lin

Reputation: 13353

The answer is NO. Simply because there's unknown number of columns (say, if you add one more sentence to the top word you could be adding one more column to the result set) and each column is not well defined ( Why blahblah4 should be in column sentence instead of sentence2 ?)

IMO, SQL is used to tell what you want to get, but in this case, SQL is unable to tell exactly what you want.

Even if this can be done ( I would love to learn ), I believe the complexity offsets any benefit and handling this in PHP is a better option.

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7133

You might be looking for GROUP_CONCAT()

SELECT T1.id, T1.english,T1.definition,
GROUP_CONCAT(T2.sentence ORDER BY T2.ID SEPARATOR '|') 
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.id = T2.word_id
Group by word_id

Sample fiddle

Upvotes: 2

Related Questions