0

How to query to get only show where on the other table has a relation id of a field. Example My explain table is:

In Table Users

+--------+------------+
|   id   |  username  |
+--------+------------+
|    1   |zuck        |
|    2   |renold      |
|    3   |arby        |
|        |            |

In Table Class

+--------+------------+------------+
|   id   |  user_id   | class_name |
+--------+------------+------------+
|   1    |      1     | ABC        |
|   2    |      1     | EFG        |
|   3    |      1     | HIJ        |
|   4    |      2     | KLM        |
|   5    |      2     | NOP        |

And I want to get result as below:

Array
(
    [0] => Array
        (
            [id] => 1
            [username] => zuck
            [class] => Array
                (
                   [0] => array(
                          [id] => 1
                          [class_name] => ABC
                    )
                   [1] => array(
                          [id] => 2
                          [class_name] => EFG
                    )
                   [2] => array(
                          [id] => 3
                          [class_name] => HIJ
                    )
                )

        )
    [1] => Array
        (
            [id] => 2
            [username] => renold
            [class] => Array
                (
                   [0] => array(
                          [id] => 4
                          [class_name] => KLM
                    )
                   [1] => array(
                          [id] => 5
                          [class_name] => NOP
                    )
                )

        )
)

So username arby not show because he dont have a relation in table class. In table class there is not user_id 3. So will show only user zuck and renold. I try with query left join, outer join, inner join, but not success :-(

Thanks for help.

Loren Ramly
  • 1,091
  • 4
  • 13
  • 21
  • This is all about how you write the query to get the data, I would really suggest that you start by reading this Q&A that I wrote: http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables It is long, but you should be in a MUCH better position after reading it. – Fluffeh Mar 20 '14 at 09:59
  • If I know I dont ask? Are you know it? – Loren Ramly Mar 20 '14 at 10:05
  • you have said _I try with query left join, outer join, inner join, but not success_ . Can you put it here ? – KarelG Mar 20 '14 at 10:19

1 Answers1

-1
SELECT Users.username,Class.class_name FROM Users INNER JOIN Class ON Users.id = Class.user_id;
kidA
  • 1,379
  • 1
  • 9
  • 19
  • What are you talking about?! This will return only the common fields between the two tables! – kidA Mar 20 '14 at 10:28