Joining three tables w/user input

Question

I have three tables, A, B, C. Table B has foreign keys into both A & C. A & C don't have keys into each other.

I'm trying to write a query where I get every hit from a column in Table C whose primary key (foreign key in B) matches a primary key from Table A (also a foreign key in B). A wrinkle is that I am trying to get this info based on a user input, which will be a separate column in Table A.

Example:

TABLE A
-------
aID    aName
1      Squeakers
2      Formations
3      Hutsis
4      Pms

TABLE B
-------
bID    aID    cID
1      1      27930
2      1      21405
3      1      24013
4      2      21111
5      1      34100
6      1      20841
7      4      30001

TABLE C
-------
cID      cName
21405    Silver Flyer
27930    Babs McGee
30001    Jimmy McGill
24013    Fletcher Mason
21111    Spike Fester
34100    Hope Sisco
20841    Ellis Traeger

So I want a user to put in ‘Squeakers’ and then get something that looks like:

Squeakers – Babs McGee, Silver Flyer, Fletcher Mason, Hope Sisco, Ellis Trager

I've been stuck on this for multiple days and can't figure out the syntax. I'm not sure if I need a sub-query or if it's not all INNER JOINS?

Possible duplicate of [Joining three tables using MySQL](https://stackoverflow.com/questions/3709560/joining-three-tables-using-mysql) — dtasev, Apr 27 '18 at 10:07

Saharsh Shah · Answer 1 · 2017-03-30T14:04:11.340

0

You can use JOIN and GROUP_CONCAT() to join all tables and get all cName in one row.

Try this:

SELECT GROUP_CONCAT(C.cName) AS cNames
FROM TableA A 
INNER JOIN TableB B ON A.aID = B.aID 
INNER JOIN TableC C ON B.cID = C.cID
WHERE A.aName = 'Squeakers';

edited Mar 30 '17 at 14:04

answered Mar 30 '17 at 14:03

Saharsh Shah

28,687
8
48
83

That's incorrrect solution - the expected result is 1 row – nimdil Mar 30 '17 at 14:04
@nimdil User can use GROUP_CONCAT() function to get in one row Please check the updated answer – Saharsh Shah Mar 30 '17 at 14:06
I know that's my solution actually – nimdil Mar 30 '17 at 14:08

score 0 · Answer 2 · answered Mar 30 '17 at 14:06

SELECT aName, GROUP_CONCAT(cName SEPARATOR ', ') as cNames
FROM A 
INNER JOIN B 
ON A.aID = B.aID
INNER JOIN C
ON B.cID = C.cID
GROUP BY aName, A.aID

You will get two columns - one with aName and other with aggregated cNames, comma separated. I don't have MySQL at my current PC but it should more or less work like this.

score 0 · Answer 3 · answered Mar 30 '17 at 14:12

A - Author (one row per author) Author_Id, Author_Name, Author_Country

B - Author_Books (relationship table between author and books, one row per relation) AB_ID, Author_ID, Book_ID

C - Books (One row per book) Book_ID, Book_Name

Now, the query would be

Select a.Author_Name,b.Book_Name
from Author a, Author_Books B, Books C
where a.author_id =b.Author_ID
and B.Book_ID = C.Book_ID
and a.Author_Country = ? //This is your squeaker clause

Joining three tables w/user input

3 Answers3