Marx Posted January 12, 2014 Share Posted January 12, 2014 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted January 12, 2014 Share Posted January 12, 2014 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Marx Posted January 12, 2014 Author Share Posted January 12, 2014 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. Quote Link to comment Share on other sites More sharing options...
Solution kicken Posted January 12, 2014 Solution Share Posted January 12, 2014 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. Quote Link to comment Share on other sites More sharing options...
partsbaseie Posted January 13, 2014 Share Posted January 13, 2014 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 13, 2014 Share Posted January 13, 2014 What you need is WHERE trade_directory_id IN (12, 25) And don't hijack other peoples posts Quote Link to comment Share on other sites More sharing options...
partsbaseie Posted January 13, 2014 Share Posted January 13, 2014 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted January 13, 2014 Share Posted January 13, 2014 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. Quote Link to comment Share on other sites More sharing options...
partsbaseie Posted January 14, 2014 Share Posted January 14, 2014 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.