MySql Join 3 Tables?

scm22ri

Hi Everyone,

How would I go about joining 3 tables at once? My database design/setup is at the below URL. I want the mysql statement to say "1992 Ford Explorer"

http://whatsmyowncarworth.com/class-work/sign3/database/database-display.php

Thanks Everyone!

bradgrafelman

scm22ri;11017755 wrote:
How would I go about joining 3 tables at once?

You'd go about it in exactly the same way you would go about joining two tables, only you'd add another join to include a third.

scm22ri

Hi bradgrafelman,

Thanks for the reply. This is what I got so far but I can't figure out the year, make and model ....

This is where I get confused. Would I need to use a left outer join?

SELECT car_years.name, car_makes.name
FROM car_makes, car_years
WHERE car_years.id=car_year_id

year, make

SELECT car_makes.name, car_models.name
FROM car_makes, car_models
WHERE car_makes.id=car_make_id

make, model

SELECT car_years.name, car_models.name
FROM car_years, car_models
WHERE car_years.id = car_year_id

SELECT years.name, models.name
FROM car_years AS years, car_models AS models
WHERE years.id = car_year_id

year, model

sneakyimp

The code you are writing is using implicit joins. You might find it helpful to make these explicit. OUTER joins are insanity. I don't think I've ever used one in practice. All of my joins have been INNER or LEFT joins. An INNER join only includes records that are in both tables. A LEFT join includes records that are in the first table whether or not any exist in the second table. Try googling LEFT JOIN or something to learn more. Using Aliases in your code is also really helpful.

In your case, think about something like this:

SELECT y.name AS year, m.name AS make, m2.name AS model
FROM car_years y
LEFT JOIN car_makes m
ON (m.car_year_id = y.id)
LEFT JOIN car_models m2
ON (m2.car_year_id = y.id AND m2.car_make_id = m.id)

Notice how I use the alias AS to rename "name" from each table to something more useful. Also notice how I alias each table with a more helpful name -- this makes the SQL more brief and tends to clarify which table you are talking about.

scm22ri

Hi,

Thanks for the response. I appreciate the help but I can't follow the syntax you sent. I figured out how to join three tables but the "Models" (Notice how the "Enclave") is interjecting itself against all years, models. All of the makes don't have a model called "Enclave".

My goal is to have the correct models corresponding with the correct makes. In the below sql statement it isn't.

Here's my sql command. Any pointers?

SELECT bhph_car_years.name AS Years, bhph_car_makes.name AS Makes, bhph_car_models.name as Models
FROM bhph_car_years, bhph_car_models
INNER JOIN bhph_car_makes

sneakyimp

I haven't seen your entire database, so I don't know what your data looks like. That could be the problem. Did you even try my query? No offense, but your last query kind of sucks.

dalecosp

Scm22ri, the first rule of joins is "avoid Cartesian Joins by including N-1 join conditions, where N is the number of tables to be joined". Or something like that.

In the case of your query above, you actually have 3 joins (INNER JOIN and "," are pretty much doing the same thing here), so you should have at least 2 (that's N-1) join conditions.

Perhaps something like:

SELECT bhph_car_years.name AS Years, bhph_car_makes.name AS Makes, bhph_car_models.name as Models
FROM bhph_car_years y, bhph_car_models mo, bhph_car_makes ma where y.ID = ma.car_year_id and ma.id = mo.car_make_id and mo.name = "Explorer"

dalecosp

It might also be worth noting that joins, while better than subqueries (in older MySQL, at least) can still cause performance issues, so after you get a statement you think is working, go ahead and run explain on it and see it there is anything that looks like it could be improved (for example, "using temp"). And it goes without saying that you shouldn't do your MySQL testing on a production server unless there's no other choice. (And if there isn't, go complain to your boss!)

scm22ri

Hi Everyone,

Thanks for the replies. Below I've tried every combination of mysql statements but I still can't seem to get this command to work. I've attached a few photos of my database design. Any tips/pointers would be great. Thanks!

Below is the closet statement I got work it to work but it's not %100 working.

SELECT bhph_car_years.name AS year, bhph_car_makes.name AS make, bhph_car_models.name AS Model
FROM bhph_car_years, bhph_car_models,  bhph_car_makes
WHERE bhph_car_years.id = bhph_car_models.id

years.jpg makes.jpg models.jpg

Ashley_Sheridan

Sneakyimp already told you how to join in the way that you want, but you seem to be completely ignoring him and continuing in your own way and then telling us it doesn't work. Try sneakyimps way and see if that works.

scm22ri

I'm not ignoring anyone. I tried his method. It didn't work.

Ashley_Sheridan

But your old way doesn't work either, and yet you're still trying that :p

Try this:

SELECT y.`name` AS `year`, ma.`name` AS make, mo.`name` AS model
FROM bhph_car_models mo
LEFT JOIN bhph_car_years `y` ON y.`id`=mo.`car_year_id` AND y.`active`=1
LEFT JOIN bhph_car_makes ma ON ma.`id`=mo.`car_make_id` AND ma.`active`=1
WHERE mo.`active`=1

scm22ri

Hi Ashley,

Thanks for the response. I appreciate it. I'm grateful that it's working but now I'm just trying to make sense of your answer. The reason why I want to break it down a bit is because I want to fully understand what's going on with the sql command. If I'm incorrect in any of my below statements, please feel free to correct me! I'm very happy I got the answer but now I must understand what it means! 🙂

In the below line of syntax, how/where did you get .y name AS year etc ... I'm assuming you got that from the bhph_car_models mo. But my question is, how?

SELECT y.`name` AS  `year` , ma.`name` AS make, mo.`name` AS model

In the below line of code you left joined bhph_car_years and you converted bhph_car_years into (y) and you made bhph_car_models (aka mo) mo = car_year_id ? (In other words your extracting the information from the models table because the models table has everything (meaning, year, make, model)

LEFT JOIN bhph_car_years `y` ON y.`id` = mo.`car_year_id`

In the below line of syntax your are setting up (bhph_car_years AKA y) to be lefted joined?

AND y.`active` =1

I'm not sure where these two lines of code mean. Does the AND also meaning your LEFT JOINING with y? If I am correct, shouldn't ma. active =1 be to the left? of LEFT JOIN? Also, a side question - I'm not sure where WHERE mo. active =1 comes into play?

AND ma.`active` =1
WHERE mo.`active` =1

This is my take, your extracted all of the information from the table that has the most information (bhph_car_models aka mo). You then used LEFT JOIN to append specific information from mo to (bhph_car_years aka y) and then (bhph_car_makes aka ma)? Also, why did you include active=1?

I apol. in advance for so many questions but I want to make sure I understand everything!
Thanks Ashley and everyone!

Ashley_Sheridan

scm22ri;11018423 wrote:
In the below line of syntax, how/where did you get .y name AS year etc ... I'm assuming you got that from the bhph_car_models mo. But my question is, how?

In the FROM and JOIN parts, I assign the full table names a shorter name. It just makes it easier than writing out the full table name, and reduces the chance of a typo.

scm22ri;11018423 wrote:
In the below line of code you left joined bhph_car_years and you converted bhph_car_years into (y) and you made bhph_car_models (aka mo) mo = car_year_id ? (In other words your extracting the information from the models table because the models table has everything (meaning, year, make, model)

Pretty much. I noticed that that table had all the information, so seemed like a good place to do the joining from. It doesn't really matter though, you could do it any way, I just prefer to do my joins with the most important table first, then the the next most important and so on.

scm22ri;11018423 wrote:
In the below line of syntax your are setting up (bhph_car_years AKA y) to be lefted joined?
AND y.`active` =1
I'm not sure where these two lines of code mean. Does the AND also meaning your LEFT JOINING with y? If I am correct, shouldn't ma. active =1 be to the left? of LEFT JOIN? Also, a side question - I'm not sure where WHERE mo. active =1 comes into play?
AND ma.`active` =1
WHERE mo.`active` =1

When you join a table, you don't just only get to join it on one field. What I'm doing there with the ma.active=1 is joining only those rows that are marked as active, as you presumably won't want inactive rows to be included in the final result.

Hope that helps a little towards explaining it better?

sneakyimp

Ashley Sheridan;11018433 wrote:
In the FROM and JOIN parts, I assign the full table names a shorter name. It just makes it easier than writing out the full table name, and reduces the chance of a typo.

I believe this is referred to as using a table alias. You can use the SQL reserved key word "AS" to assign a shorter, handier name to either selected values or the tables. Aliases of selected values are handy for distinguishing which value is from one table and which is from another. For example, if you have Table1 and Table2 that both have a name field, you can distinguish which one is which in your result by aliasing your selected values:

SELECT Table1.name as name1, Table2.name as name2
FROM Table1, Table2
WHERE Table1.id=Table2.id

Far more often, I find myself aliasing table names. You don't need the AS keyword for this -- just a space. In this example, I'm using table aliases (t1 and t2) to shorten my really long table names:

Select t1.name, t2.description FROM
my_really_long_table_name_for_the_name_table t1,
my_super_duper_long_table_name_for_descriptions_table t2
WHERE t1.id=t2.id

The reason my query didn't work is probably because I used an INNER join. An inner join only returns values when there is a match between the tables being joined. There could be a problem in your data where one of the three tables did not have a matching value. A LEFT join, on the other hand, will still give you all the records in the first table in your expression even if there is no match in the second table you mention in your join. Your original efforts were using an inner join also even though you did not type "inner join". This is what is called an implicit inner join:

SELECT * FROM table1, table2 WHERE table1.id=table2.id

It's identical to this

SELECT * FROM table1 INNER JOIN table2 ON (table1.id=table2.id)

and these queries will only return records where you have any matches in both tables. If there are no matches, you get no records.

In a LEFT join, you get all the records in table1 whether or not there are any matches in table 2.

SELECT * FROM table1 LEFT JOIN table2 on table1.id=table2.id

If a record in table 1 has no match in table2, all of the table2 values have NULL in them.

scm22ri

In the FROM and JOIN parts, I assign the full table names a shorter name. It just makes it easier than writing out the full table name, and reduces the chance of a typo.

Ok, that makes sense. So the very first line of syntax has nothing to do with your next line? You just simply wanted to rename tables?

Pretty much. I noticed that that table had all the information, so seemed like a good place to do the joining from. It doesn't really matter though,you could do it any way, I just prefer to do my joins with the most important table first, then the the next most important and so on.

Ok, I think I know where your coming from. Your basically said it makes the most sense to join from tables that have the most information to tables that have the least information? (You could do it another way but it's easier this way)

LEFT JOIN bhph_car_years `y` ON y.`id`=mo.`car_year_id` AND y.`active`=1
LEFT JOIN bhph_car_makes ma ON ma.`id`=mo.`car_make_id` AND ma.`active`=1

This is where the magic is happening with this sql query. I just want to make sure I'm correct in my assessment. Each table becomes another name (y and ma). Then this is where I get confused, your taking the year and make (y and ma) id's and you equal(reference) them to their respective IDS in the bhph_car_models table aka mo viaid=mo.car_year_id AND y.active=1 and id=mo.car_make_id AND ma.active=1?

Thanks again!

scm22ri

Thanks sneakimp for the help in explain a few things. I didn't get a chance to read through your response before I submitted mine!

scm22ri

Hi Everyone again,

I'm just curious, it is at all possible to append a unique ID onto the sql statement? Meaning having a unique ID per vehicle?

Ashley_Sheridan

Yes, add some sort of unique id to an entry in one of the tables and adding that to the list of fields you're grabbing in the results.