Jump to content

Inner Join problems


Marx
Go to solution Solved by kicken,

Recommended Posts

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.

 

 

 

 

 

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.