So ive started work on a web app type thing. Anywhoo I have linked all of my tables for my databases using relations where needed.

My database structure is

 

Teams-Team_ID, Team, League_ID, Country_ID

Leagues-League_ID, League

Country-Country_ID, Country

Badges-Badge_ID, Badge, Team_ID

 

Below i have the SQL query which is kinda working.

 

SELECT teams.team, teams.country_ID

FROM teams

INNER JOIN country

ON teams.country_ID = country.country_ID

WHERE country.country = 'England'

LIMIT 0 , 92")

 

Not i wont lie, i have no idea what that means, i just changed it until it worked *facepalm*

currently it echos out Team_ID-->Team-->_country_ID

what i want it to echo out is not the country_ID, but the country name itself and the league name. the way in which it kinda works is because the WHERE statement is equal to England and it correctly returns all teams with a country ID of 6 (which is england)

 

Would really appreciate someone who knows a thing or two to re-write the little snippet of code for me.

 

thanks

try

SELECT teams.team, country.country, leagues.league FROM teams INNER JOIN country USING (country_ID) INNER JOIN leagues USING league_ID LIMIT 0, 92;

 

It looks like you're trying to get data from 3 tables so you have to use 2 joins.

nope that didn't work im afraid. I wish I could be more helpful to myself but atm its confusuing my brain

what didn't work - did you get an error message or did it not output what you wanted? make sure your code matches exactly how the fields in your tables are coded i.e check for upper/lower case and hyphens etc. Sorry if this is stating the obvious but it is an easy mistake to make.

what didn't work - did you get an error message or did it not output what you wanted? make sure your code matches exactly how the fields in your tables are coded i.e check for upper/lower case and hyphens etc. Sorry if this is stating the obvious but it is an easy mistake to make.

 

sorry yer wasnt very helpful then, i just got an error...ill try again with caps etc

i get this error

Query to show fields from table failed

Riight, its now outputting what i want, but i think that just because ive told it too. Its clearly not showing relations tho because the results are wrong. An example is this:

 

Chelsea-->Barclays Premier League-->England = Correct

Southampton-->Barclays Premier League-->England = wrong

 

 

 

("SELECT teams.Team_ID, teams.Team, leagues.League, country.Country_ID, country.Country

FROM teams

INNER JOIN country, leagues

WHERE teams.Country_ID = '6'

AND leagues.League_ID = '6'

AND country.Country = 'england'

LIMIT 0 , 92");

It doesn't look like you are actually joining the tables anywhere. Somewhere you need to have a key in one table equal to a key in another table to join the tables. Try (you will need to alter the WHere clause to select using the data you want.

SELECT teams.Team, country.Country, leagues.League
FROM teams
INNER JOIN country 
ON teams.country_ID = country.Country_ID
INNER JOIN leagues
ON leagues.League_ID=teams.League_ID
WHERE teams.Country_ID=6
AND teams.League_ID=6

you got it working thank you , i can now start phase 2 (processing over 500 images :/ )