CODEWITHSUNDEEP
phpmysqlsqlarrays
hawkhorrow
hawkhorrow

Reputation: 475

Combining Three Tables Output using another to cross-reference

Background

Being the mySQL n00b I am, the best query I can come up with to match three tables, is run a comparison between two tables, output a variable, and then use that variable to select my final output from a cross reference table. After that, I would run yet another query to output from my third table...

Now I KNOW there is a way to select all my rows needed with only one single statement, but for the life of me I cannot piece it together. Can someone please help me structure the query I need correctly?

I must output a result set in php using information from 3 tables, and use a separate one as the actual link between two table's ids. Thank you!!

Tables

name: table_one
-----------------------------------------------------------
id | user_id | o_id | num | likes | dislikes | .... | ... |
-----------------------------------------------------------
 1 | 765     | 1    | 100 |android| cats                  |
 2 | 765     | 2    | 100 | birds | mySQL queries         |
 3 | 765     | 3    | 100 | php   | iPhones               |
 4 | 765     | 2    |  2  |oranges| bananas               |
-----------------------------------------------------------

name: table_two                            
------------------------------------------------------------|
id |first_name| location | num_times | diploma | why        |
------------------------------------------------------------|
1 | ABC     | here     | 0         | none    | because      |
2 | BCD     | there    | 5         | BS      | no reason    |
3 | Sally   | nowhere  | 194384    | DR      | no reason    |
4 | Jack    | overthere| 3         | none    | failure      |
5 | Bob     | Mars     | 0         | random  | in training  |
-------------------------------------------------------------

name: table_agency         |
---------------------------|
id | name | address        | 
---------------------------|
1  | A    | 123x           | 
2  | B    | 234y           | 
3  | C    | 456z           | 
----------------------------
name: table_link 
-----------------------------
          rel_a | rel_b           
---------------------------------
           1   |    1            |
           1   |    4            |
           1   |    5            |
           2   |    1            |
           2   |    4            |
           2   |    5            |
           3   |    2            |
           3   |    3            |
           4   |    3            |
---------------------------------

Output/PHP

$results = $class->runQuery($query);    //basically a fetchAll
foreach ($results as $result) { 
echo id_table_one ($result['id']);              
echo $result['name'];
echo $result['num'];
echo $result['likes'];
echo other_rows...basically table_one.*
echo all_first_names&num_times that correspond in the table link;
}

    //ACTUAL Printout(echo doesn't have the ,'s):
    //here should be the output:

-------------------------------------------------
1 | A     | 100  | android  | ABC-0/Jack-3/Bob-0|
2 | B     | 100  | birds    | ABC-0/Jack-3/Bob-0|
3 | C     | 100  | php      | BCD-5/Sally-194384|
4 | B     | 2    | oranges  | Sally-194384      |
----------------------------------------------

Side Notes

1) There will be at least 1 entry of cross-reference-items to correlate to first names, to a maximum of 7 entries (doesn't have to be in query, but for reference)

2) There will only be the 3 entries of items in table_agency

3) The result set MUST be qualified with: user_id=".$variable

4) My original query, more or less... however since there is no commonality between the additional information I am trying to pull, I was forced to create a function which would make the tie,.... this should lend an idea of what I am trying to achieve: 

$query = "select
          a.*,
          b.name,
          b.id   as agency_id
      from table_one a,  table_agency b
      where a.agency_id = b.id
      and a.user_id = ".$variable;

now with the new addition of which first_names actually correspond to the $result['name']

5) I can use a nested foreach( to actually output the final result set of ...nested array for first_name???

6) Please let the query(response) be commented, so that I may learn from all your hard-work! I am not attempting to solely have a question answered, but also learn on the steps and methodology!

7) Thank you in advance to all your help...this is definately a headscratcher. Thanks!!!

Upvotes: 2

Views: 893

Answers (1)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can get the output with this query

SELECT
    ta.id,
    ta.name,
    to.num,
    to.likes,
    GROUP_CONCAT(tt.first_name SEPARATOR '-') AS `names`
FROM table_agency AS ta
LEFT JOIN table_one AS `to` ON to.o_id = ta.id
LEFT JOIN table_link AS tl ON tl.rel_a = to.id
LEFT JOIN (SELECT id , first_name FROM table_two) AS tt ON tt.id = tl.rel_b
WHERE to.user_id = 765
GROUP BY to.id

You can replace the user_id in your php code with variable. And for the ABC-0/Jack-3/Bob-0 you can simple replace this GROUP_CONCAT(tt.first_name SEPARATOR '/') AS names

SQL Fiddle Demo

Output

| ID | NAME | NUM |   LIKES |        NAMES |
--------------------------------------------
|  1 |    A | 100 | android | ABC-Jack-Bob |
|  2 |    B | 100 |   birds | Bob-ABC-Jack |
|  3 |    C | 100 |     php |    BCD-Sally |
|  2 |    B |   2 | oranges |        Sally |

EDITS:

Here is edited query. You can use MySQL Concat function 

SELECT
    ta.id,
    ta.name,
    to.num,
    to.likes,
    GROUP_CONCAT(tt.first_name SEPARATOR '/') AS `names`
FROM table_agency AS ta
LEFT JOIN table_one AS `to` ON to.o_id = ta.id
LEFT JOIN table_link AS tl ON tl.rel_a = to.id
LEFT JOIN (
          SELECT 
          id , 
          CONCAT(first_name,'-',num_times) as first_name 
          FROM table_two
        ) AS tt ON tt.id = tl.rel_b
GROUP BY to.id;

Demo

OUTPUT

| ID | NAME | NUM |   LIKES |              NAMES |
--------------------------------------------------
|  1 |    A | 100 | android | ABC-0/Jack-3/Bob-0 |
|  2 |    B | 100 |   birds | ABC-0/Jack-3/Bob-0 |
|  3 |    C | 100 |     php | BCD-5/Sally-194384 |
|  2 |    B |   2 | oranges |       Sally-194384 |

Upvotes: 3

Related Questions