Shirley Dodson
Shirley Dodson

Reputation: 339

I'm Having trouble joining 3 MySQL tables, How do I improve my Syntax?

I'm attempting to join 3 different tables in my MySQL database. I've managed to join 2 tables before on limited columns, but I'm attempting to access all the columns in these tables so I can post the resulting data to a webpage. I feel I've tried every possible join combination from inner joins to Left Outer Join and Right Outer Joins. I've looked into doing Unions between the tables, but I've read that you need to have an equal number of columns in order for them to work, and the tables do not have the same number of columns.

Below is information on the tables I'm joining, and the syntax I've used while trying to query the database.

Tables:

Breeds -> 10 columns, Breed_Name is a Unique Key and links to the other two tables.
Lifestyle -> 11 Columns, Breeds.Breed_Name is a foreign Key to the Lifestyle.Breed Column
Temperament -> 10 Columns, Breeds.Breed_Name is a foreign Key to the Temperament.Breed Column

MySQL Syntax

Select B.*, L.*, T.*
From Breeds as B
Right Outer Join Lifestyle as L on B.`Breed_Name` = L.`Breed`
Right Outer Join Temperament as T on B.`Breed_Name` = T.`Breed`
Where B.`Breed_Name` = "German Shepherd Dog"

I managed to figure this out somewhat before, but then I changed something (don't even know what) and it all stopped working, and I haven't been able to get it back. But the odd thing was I was able to get information out of Breeds and Temperament using a Right Outer Join, but couldn't get anything out of Lifestyle regardless of what join I was using. And of course now none of it is working.

I don't have a firm understanding on the join types. I don't understand why we have different types. The information in my tables are different, and only match in a few columns because of foreign keys. If I could, I'd be doing Full Joins, as I think that's what I actually need, but MySQL doesn't do Full Joins (at last check). How do I decide which type of join I need when I want all information in all tables?

For Future Reference:

If I do manage to get this query to work, would I be able to scale up this query to as many different tables as I want? Just keep doing:

(join-type) *This table* on table1.`column` = table2.`column`

Or is it possible to need multiple join types during a single query? And if so, how do you decide which one to use?

EDIT:

I've played around with the query some, and if I remove the WHERE B.Breed_Name= "German Shepherd Dog" Then I get back to where I was when the code stopped working. But I still don't have access to the information in Lifestyle. What's wrong with my WHERE statement? Is it in the wrong place? I know you can do WHERE statements when you join tables . . .

EDIT 2:

(Just to give you guys an idea on how these answers are helping)

Something about the answers with single quotes in the WHERE statement is causing my whole page to disappear, which I think only happens when there's something incorrect about the syntax. If I take out the WHERE statement, Oddly Enough ALL the answers work. One answer suggests an Inner Join, one suggests a Left Join, one suggests just JOIN, and somehow, without the WHERE statement, they all work. What? One answer uses a STRCMP in the WHERE and that does seem to work, but I don't understand what that is (very well) and would prefer to use a simple WHERE statement. Is there any way to do that?

Edit 3

I don't know what happened, but now my Where statement is working, and multiple syntax versions are working. Is it possible for a Database to cache results and not show changes? I'm baffled by how before I couldn't get anything to work, but now nearly everything seems to be working? The following 3 codes all work:

MySQL

SELECT *
FROM `Breeds` as B
Left JOIN `Lifestyle` AS L ON B.`Breed_Name` = L.`Breed`
Left JOIN `Temperament` AS T ON B.`Breed_Name` = T.`Breed`
Where B.`Breed_Name` = "German Shepherd Dog"

SELECT *
FROM `Breeds` as B
inner JOIN `Lifestyle` AS L ON B.`Breed_Name` = L.`Breed`
inner JOIN `Temperament` AS T ON B.`Breed_Name` = T.`Breed`
Where B.`Breed_Name` = "German Shepherd Dog"

SELECT *
FROM `Breeds` as B
JOIN `Lifestyle` AS L ON B.`Breed_Name` = L.`Breed`
JOIN `Temperament` AS T ON B.`Breed_Name` = T.`Breed`
Where B.`Breed_Name` = "German Shepherd Dog"

While I'm glad that it works, I'm frustrated because I don't understand how. And I thought that I had to use Select B.*, L.*, T.* when trying to source multiple tables, so why does a simple Select * work? And I don't understand why my WHERE statement is suddenly working when it wasn't before. Could it be because there was a subtle change in the syntax? There are now quotes around the table names where there weren't before.

I look forward to reading further answers and I'm thankful for all help so far.

Upvotes: 0

Views: 54

Answers (3)

Keith
Keith

Reputation: 16

In MySQL this should get you there. Having no idea how your tables are actually structured, this is only an approximation.

SELECT *
FROM `Breeds` as B
JOIN `Lifestyle` AS L ON B.`Breed_Name` = L.`Breed`
JOIN `Temperament` AS T ON B.`Breed_Name` = T.`Breed`
WHERE B.`Breed_Name` = 'German Shepherd Dog';

Let me know if this gets you close.

(EDIT) Seeing your edit, use single quotes in MySQL to specify that your predicate is a string.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I have no idea what your data looks like, but right join doesn't seem correct here. You want to start with the breeds table and keep all of those, along with matching columns from the other tables. That is a left join, not a right join:

Select B.*, L.*, T.*
From Breeds B left Join
     Lifestyle L
     on B.`Breed_Name` = L.`Breed` left Join
     Temperament T
     on B.`Breed_Name` = T.`Breed`
Where B.`Breed_Name` = 'German Shepherd Dog'

Upvotes: 1

Daniel Waghorn
Daniel Waghorn

Reputation: 2985

You should be able to perform multiple INNER JOIN clauses to get your desired result.

Try this query:

SELECT B.*, L.*, T.*
FROM `Breeds` as B
INNER JOIN `Lifestyle` AS L
    ON B.`Breed_Name` = L.`Breed`
INNER JOIN `Temperament` AS T
    ON B.`Breed_Name` = L.`Breed`
WHERE STRCMP(B.`Breed_Name`,"German Shepherd Dog") = 0;

This should give you the results you need, if not let me know. If you're still having problems please set up a sample on SQLFiddle and add the link in to your original post so that we can test.

This image is a good reference for understanding the join types: SQL Join Venn Diagrams

Upvotes: 2

Related Questions