Inner Join problems

[Marx]

Hey guys,

 

I'm not very used to Mysql and this is the first INNER JOIN i need to do.

 

I have 2 tables:

 

1st: Teams 

 

ID - 1

Team - Chicago Bulls

Tag - chicago_bulls

 

ID - 2 

Team - Oklahoma City Thunder

Tag - oklahoma_city_thunder

 

ID - 3 

Team - Philadelphia 76ers

Tag - philadelphia_76ers

 

ID - 4

 

Team - New York Knicks

Tag - new_york_knicks

 

2nd: Matches

 

ID - 1

Home - Chicago Bulls

Away - Oklahoma City Thunder

 

ID - 2

Home - Philadelphia 76ers

Away - New York Knicks

 

i'm trying to do a query that retrieves the names of the teams that will play against each other taken from the Matches table but this one just shows the name of the first of them:

 

ID - Team - Tag

 

1 - Oklahoma City Thunder - oklahoma_city

 

SELECT Matches.ID, Matches.Home, Matches.Away, Teams.Team FROM Teams INNER JOIN Matches ON Matches.Home = Matches.Tag
WHERE Tag = 'oklahoma_city_thunder' LIMIT 0,1

Any idea?

 

Thanks for the help.

 

 

 

 

 

[Barand]

Why the join?

 

You are storing the team names in the matches table according to that data. You should be storing the team ID values

Edited January 12, 2014 by Barand

[Marx]

Yes but i'm getting the tag from the url, so its being generated dinamically. For each of the season matches without having to enter them myself.

[kicken]

As mentioned, you should be storing your team IDs, not their names, in the matches table. So rather than

ID - 1

Home - Chicago Bulls

Away - Oklahoma City Thunder

 

ID - 2

Home - Philadelphia 76ers

Away - New York Knicks

 

you would have

ID - 1
Home - 1
Away - 2
 
ID - 2
Home - 3
Away - 4

Then you would need two inner joins, one for the away team and one for the home team. Both joins would link back to the teams table, but with a different condition.

SELECT 
   matches.ID as matchId
   , home.Team as homeTeamName
   , home.Tag as homeTeamTag
   , away.Team as awayTeamName
   , away.Tag as awayTeamTag
FROM matches
INNER JOIN teams as home ON home.ID=matches.Home
INNER JOIN teams as away ON away.ID=matches.Away

The first INNER JOIN connects with the teams table finding the row which matches the Home ID. This is aliased to home so we can reference it within the select list.

 

The second INNER JOIN is the same as the first, except it matches the Away ID and is aliased to away.

[partsbaseie]

Hi new to (and self taught) on php; sql; phtml etc. I hope Marx query was answered. I have a similar problem. I have multiple tables; I have code which works to join both tables by user id. I needed to write same as the code sends an email based on type of User BUT unfortunately the Type of User appeared in a table with no email address but luckily both tables had a User ID which obviously match.

 

Here is the Code which works -

 

$sql=mysql_query("select parts_tbl_shop.user_id,parts_tbl_users.email from parts_tbl_users,parts_tbl_shop where parts_tbl_users.user_id=parts_tbl_shop.user_id and parts_tbl_shop.trade_directory_id='12'"); 

 

The above Code effectively sends an email to Shop Owners on my Site who are trade_directory_id='12'  (which is in the case =Bike Parts Supplier)

 

I need the Code to work so as to send the EMail not only to trade_directory_id='12'  but also to a number or other Trade Directory ID's - for example trade_directory_id='25' (which happens to be New Parts Suppliers) - I may also need to send same to more ID's

 

Basically therefore I need to have the correct code to send the email to not just 1 ID but 2 or 3 or 4 etc

 

Heres the latest attempt at the Code - Didnt work (I have tried multiple different changes/scripts)

 

$sql=mysql_query("select parts_tbl_shop.user_id,parts_tbl_users.email from parts_tbl_users,parts_tbl_shop where parts_tbl_users.user_id=parts_tbl_shop.user_id and parts_tbl_shop.trade_directory_id='12','25'");

 

So I think its just the Italics 'Part' which I'm floundering on - As stated Ive tried repeating code; inserting AND etc etc

 

Please help

 

Regards Mark

[Barand]

What you need is

 

WHERE trade_directory_id IN (12, 25)

 

 

And don't hijack other peoples posts

[partsbaseie]

Apologies for hijacking and Im probably doing it agin by replying - I note the above and now have

 

$sql=mysql_query("select parts_tbl_shop.user_id,parts_tbl_users.email from parts_tbl_users,parts_tbl_shop where parts_tbl_users.user_id=parts_tbl_shop.user_id WHERE parts_tbl_shop.trade_directory_id IN (12,25)");

 

As I need to keep the .user_id JOIN at parts_tbl_users.user_id=parts_tbl_shop.user_id

 

Still not working I assume I have misinterpretated where to insert your piece of code?

[Barand]

You had two occurrences of "WHERE" in the query.

SELECT s.user_id
    , u.email 
FROM parts_tbl_users u
    INNER JOIN parts_tbl_shop s ON u.user_id = s.user_id 
WHERE s.trade_directory_id IN (12,25)

I restructured the query layout and used table aliases to improve readability. I also used an explicit JOIN syntax to separate the query structure from the selection criteria in the WHERE clause.

[partsbaseie]

Well Done...not only did it work...the Emails hit my 2 test accounts instantly...there seemed to be a lapse with the query I previously had...even though it worked

 

I really appreciate it...I assume your a web designer (at a minimum)... if so drop me a line at partsbaseie@yahoo.ie ...I would like to upgrade my site www.partsbase.ie ....I've done alot myself (but I'm a former Lending Manager & Project Manager in Banking and an Insurance Claims Manager) so no IT training only what I learn as I go along.

 

Thanks again, fantastic.

 

Mark.