Select join multiple tables in mysql?

Question

I have three tables I'm trying to select data from, each table has a pID which is what I want the join to be based on. When I run the following query I still end up with three pID fields.

What is wrong with my select join statement?

SELECT * FROM Player p
    LEFT JOIN AvgStats a ON a.pID = p.pID
    LEFT JOIN MisTotal m ON m.pID = p.pID;

Player Table
pID | Name | Age

AvgStats Table
pID | 3pt% | gamePoints

MisTotal Table
pID | Fouls | rebounds

I want to creat a table that returns

pID | Name | Age | 3pt% | gamePoints | Fouls | rebounds

Don't use `select *`, list the columns that you want to return. — Taryn, Jun 19 '13 at 19:42
I want to select all the columns, would doing a project on all of them be faster than *? — krizzo, Jun 19 '13 at 19:45
If you `select *`, then you will get three columns named `pId`. By actually naming the columns, you will only return the columns that you want without duplicates. You should name the columns, `select p.pid, p.name, p.age, a.3pt%, etc` — Taryn, Jun 19 '13 at 19:47
Thanks @bluefeet I understand now what you meant by not doing doing the *. — krizzo, Jun 19 '13 at 20:10
@FreshPrinceOfSO glad you get a fright sometimes, unlike you I don't deal with databases and wanted to learn a little. Which I was able to with this post. I'll think about using Will Smiths image instead of trying to be more professional in my appearance. — krizzo, Jun 19 '13 at 20:10
Actually there is a way to do this: `SELECT * FROM Player p LEFT JOIN AvgStats a USING(pID) LEFT JOIN MisTotal m USING(pID);` Still, using `SELECT *` is not recommended. — ypercubeᵀᴹ, Jun 19 '13 at 20:13
@ypercube I knew there had to be a way, thanks for the info, I'll still identify the fields but that seems a little nicer and will be helpful to know. Thanks. — krizzo, Jun 19 '13 at 20:44
[This](http://stackoverflow.com/questions/729197/) [has](http://stackoverflow.com/questions/9122/) [been](http://stackoverflow.com/questions/14253994/) [asked](http://stackoverflow.com/questions/14392126/) [plenty](http://stackoverflow.com/questions/1839075/) [of](http://stackoverflow.com/questions/9133120/) [times](http://stackoverflow.com/questions/413819/)[.](http://stackoverflow.com/questions/5152695/) — Andriy M, Jun 19 '13 at 20:46

sgeddes · Accepted Answer · 2013-06-19T19:52:39.650

4

If I'm understanding your question correctly, just remove * from your query and specify the field(s) you want -- in this case, p.pID:

SELECT p.pId FROM Player p
    JOIN AvgStats a ON a.pID = p.pID
    JOIN MisTotal m ON m.pID = p.pID;

Given your edits, this should work:

SELECT p.pID, p.Name, p.Age, a.`3pt%`, a.gamePoints, m.fouls, m.rebounds 
...

Just make sure you include the backticks around the column with the special character.

edited Jun 19 '13 at 19:52

answered Jun 19 '13 at 19:42

sgeddes

62,311
6
61
83

Sorry I don't want just the pID field, I'm trying to generate a larger table of all three tables where they are joined by the pID field. I'll add an example of what I expect as output. – krizzo Jun 19 '13 at 19:44
@LF4 -- can you provide samples? Not sure I'm understanding your requirements correctly. – sgeddes Jun 19 '13 at 19:47
I just updated the question with three short tables and what I'm trying to create with the joins. Now that I think about it maybe I'm getting a join mixed up with a union? – krizzo Jun 19 '13 at 19:48
Given your edits, just select the fields in your query -- pretty straight-forward select statement using the table aliases accordingly... – sgeddes Jun 19 '13 at 19:49
Ok I see how that would work, I just thought there were simple SQL statments which would be able to merge the tables automatically. Thank you that will give me the correct results. – krizzo Jun 19 '13 at 19:55

Select join multiple tables in mysql?

1 Answers1