MySQL 4 - Joining Multiple Tables

I am working on a beer website that has tables for members, breweries, beers, beer reviews, etc. It allows a member to add brewery and beer infromation as well as reviews or the beers.

I am trying to create a page that shows what they have added to the site and I am trying to create one query instead of three (reviews, beers added, breweries added).

I am really new to joining tables and can use some assistance as I cannot seem to get the query to work. Here is what I have right now.

SELECT 'members.UserID', 'members.CreateDate', 'members.City', 'members.State', 'members.Country', 'beerreviews.comments', 'beerreviews.rating', 'beerreviews.review_date', 'beerreviews.fk_user_id', 'beerreviews.fk_beer_id', 'beerreviews.beer_review_id', 'beerstable.BeerID', 'beerstable.BeerName', 'beerstable.BreweryID', 'beerstable.UserID' AS 'BeersUserID', 'beerstable.BeerCreateDate', 'brewerytable.BreweryID', 'brewerytable.BreweryName', 'brewerytable.BreweryCreateDate', 'brewerytable.BreweryCountry', 'brewerytable.UserID' AS 'BreweryUserID'
FROM 'beerstable'
LEFT JOIN 'members' on ('beerstable.BeersUserID' = 'members.UserID')
LEFT JOIN 'beerreviews' on ('beerstable.BeerID' = 'beerreviews.fk_beer_id')
LEFT JOIN 'brewerytable' on ('beerstable.BreweryID' = 'brewerytable.BreweryUserID')
WHERE 'members.UserID' = '$UserID'

Here are the fields from the tables I want to use...

Table 1: "members"

UserID
CreateDate
City
State
Country


Table 2: "beerreviews"

beerreviews.comments
beerreviews.rating
beerreviews.review_date
beerreviews.fk_user_id
beerreviews.fk_beer_id
beerreviews.beer_review_id


Table 3: "beerstable"

beerstable.BeerID
beerstable.BeerName
beerstable.BreweryID
beerstable.UserID
beerstable.BeerCreateDate


Table 4: "brewerytable"

brewerytable.BreweryID
brewerytable.BreweryName
brewerytable.BreweryCreateDate
brewerytable.BreweryCountry
brewerytable.UserID

Field Relationships...
members.UserID = beerstable.UserID = brewerytable.UserID = beerreviews.fk_user_id
beerstable.BeerID - beerreviews.beer_review_id
beerstable.BreweryID - Brewerytable.BreweryID

Thank You for your time,

Stephen

What exactly is the problem you are facing with what you have?

First thing I see is that you have

but later say the relationship is

2 Separate linkages - which is correct?

Same goes for

with


You last join doesn't match up either... so which is right?

For your first observation, I used the term 'beerstable.BeersUserID' because in the Select line I thought I was changing the field to 'beerstable.UserID' AS 'BeersUserID'. Maybe this is where I am going wrong. Since UserID is in a couple tables I thought I have to make them different.

Should I leave the code as:
LEFT JOIN 'members' on ('beerstable.UserID' = 'members.UserID')

On the second one. You found part of the problem. I used beer instead of fk in fk_review_id.

There I go again trying to get another beer in there.

It looks like we are closer. I will try a couple changes and update the post again.

Thank You,

Stephen

I am back. Still no luck.

Here is the query I changed it to...
SELECT 'members.UserID', 'members.CreateDate', 'members.City', 'members.State', 'members.Country', 'beerreviews.comments', 'beerreviews.rating', 'beerreviews.review_date', 'beerreviews.fk_user_id', 'beerreviews.fk_beer_id', 'beerreviews.beer_review_id', 'beerstable.BeerID', 'beerstable.BeerName', 'beerstable.BreweryID', 'beerstable.UserID' AS 'BeersUserID', 'beerstable.BeerCreateDate', 'brewerytable.BreweryID', 'brewerytable.BreweryName', 'brewerytable.BreweryCreateDate', 'brewerytable.BreweryCountry', 'brewerytable.UserID' AS 'BreweryUserID'
FROM 'beerstable'
LEFT JOIN 'members' on ('beerstable.UserID' = 'members.UserID')
LEFT JOIN 'beerreviews' on ('beerstable.BeerID' = 'beerreviews.fk_beer_id')
LEFT JOIN 'brewerytable' on ('beerstable.BreweryID' = 'brewerytable.UserID')
WHERE 'members.UserID' = '$UserID'

Here is the error I am receiving...

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''beerstable' LEFT JOIN 'members' on ( 'beerstable.UserID' = 'm


fk_beer_id is correct and the typo was in the relashionship section.

Ok - I had to clean things up a bit so I understood what was going on - it makes things easier for me to read and understand (missed those AS because of it). Ok - lets try this ( it removes the quotes on the table names).



I personally don't quote table names - if I have to I use the mysql specific backtick instead. IIRC you need to have the SQLMODE set to ANSI_QUOTES for it to work with quotes - but I can definitely be wrong on that.

If that doesn't work - the next thing I would try is moving that last alias from the last position to somewhere else - even if it is just one above.

Lig,

I have been thinking through this all day and I am wondering if the results will even provide what I am looking for. I think I will have to create seperate queries for each. I am not sure it can all be done with one query, At least with my knowledge. Then afgter I have completed it I can show it to someone and see it they can streamline it a little.

I did try your query and it gave me funky results. Perhaps you are corect about changing the mode.

I will try the changes and present them again when I am done.

I thank you for all your help,

Stephen

so it removed the syntax error? if so then the original problem is resolved and now we need to figure out how to get the data you wish.

Feel free to make the individual queries and we can work on joining them afterwards.

Yes the syntax error is gone. Yeah. I have started on small left join queries and will work my way up slowely.