INNER JOIN in SQL where one table contains multiple results

Question

I have two tables:

First table:

MEMBERS:
ID    |    FIRSTNAME    |    LASTNAME    |
1          Jack              Smith
2          Jane              Baker
3          Peter             Little

And the second table:

DETAILS:    
SUBSCRIBER_ID    |    FIELD_ID    |    FIELD_VALUE    |
1                     1                Blue
1                     2                Dogs
1                     3                March
2                     1                Pink
2                     2                Cats
2                     3                June
3                     1                Black
3                     2                Birds
3                     3                September

The FIELD_ID represents other details over the member. i.e.

1 = Favorite color
2 = Favorite animal
3 = Favorite month

I want to pull a report that contains their main information MEMBERS & their extra information from the DETAILS table where the SUBSCRIBER_ID = the ID in Members. So for Jane it would look like:

Jane
Baker
Pink
Cats
June

UPDATED:

I have used the following SQL statement and I pull the data I want, but the display is not as I am looking for:

SELECT members., details. from members INNER JOIN details ON members.id = details.subscriber_id WHERE jos_osmembership_subscribers.id = '29'

(WHERE purely reduce the record record to one member).

The result I am getting here is:

Jane | Baker | Pink
Jane | Baker | Cats
Jane | Baker | June

Whereas I am looking for

Jane | Baker | Pink | Cats | June

I'm using the following output which I know is 100% wrong :)

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Results:" . $row["ID"] . "|";
        echo "" . $row["FIRSTNAME"] . "|";
        echo "" . $row["LASTNAME"] . "|";
        echo "" . $row["field_value"] . "|";
}

Really I am looking for something that will let me output like:

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Results:" . $row["ID"] . "|";
        echo "" . $row["FIRSTNAME"] . "|";
        echo "" . $row["LASTNAME"] . "|";
        echo "" . $row["color"] . "|";
        echo "" . $row["animal"] . "|";
        echo "" . $row["month"] . "|";
}

Cheers

Cheers Matt. The issue with this format is it presents a new row for each field_value. I'll update my original question with the results I am getting. — Brenden Prazner, Apr 30 '15 at 10:13
So you want it to be displayed like you have in the question with a word on each line? — Matt, Apr 30 '15 at 10:22
Although probably not relevant to this question, please add the tag for the DBMS you are using (Postgres, Oracle, ...) — , Apr 30 '15 at 10:44
Just updated it with the preferred display result. Cheers mate. — Brenden Prazner, Apr 30 '15 at 10:53
What you want is to join the members table with the **pivot** of details table. unfortunately for you, there is no built in pivot function in MySql, so you will have to create it yourself. [see this link](http://stackoverflow.com/questions/7674786/mysql-pivot-table) for more details. **However**, you could use the inner join suggested by Matt and pivot the data in php. — Zohar Peled, Apr 30 '15 at 10:53

score 1 · Answer 1 · answered Apr 30 '15 at 08:16

1

Use and INNER JOIN

SELECT ID, FIRSTNAME, LASTNAME, SUBSCRIBER_ID, FIELD_ID, FIELD_VALUE
FROM MEMBERS m
INNER JOIN DETAILS d ON m.ID = d.SUBSCRIBER_ID

answered Apr 30 '15 at 08:16

Matt

14,906
27
99
149

score 0 · Answer 2 · answered Apr 30 '15 at 09:03

0

Which database are you using? If it is going to be oracle, try sys connect by path to have all the three details in a row per person Query should be something like below

SELECT id, 
       firstname, 
       lastname, 
       Sys_connect_by_path(field_value, ',') 
FROM   members m, 
       details d 
WHERE  m.id = d.subscriber_id 
START WITH id = 1 
CONNECT BY PRIOR subscriber_id = id 
ORDER  BY id

answered Apr 30 '15 at 09:03

omuthu

5,948
1
27
37

Using MySQL and presenting the results in PHP. – Brenden Prazner Apr 30 '15 at 10:09

score 0 · Answer 3 · edited May 23 '17 at 12:17

Basically, you have at least 3 options:

use an inner join as suggested by Matt, and take care of the formatting of the data in the presentation layer (in your case, PHP).
The main benefit of This approach is that it's easy to write and maintain, and doesn't require any changes in your database.
Create a view that will pivot your details table, and use an inner join on that table.
The main benefit is that you will select only the data you need, and can keep your database design.
Change your database structure and move the details to columns in the members table.
The main drawback of this approach is that it requires a change in your database, but the main benefit is that everything will become more simple.
Do not use this option if the details you keep are prone to changes (I mean, if you want to keep adding / removing details for each member). if this is the case, then use the first option, as it will be the simplest to write and maintaine.

read about dynamic pivot in mysql

read about when is it better to use EAV (Entity–attribute–value model)

INNER JOIN in SQL where one table contains multiple results

3 Answers3