[person4659]

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

[gadgetgirl]

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.

[person4659]

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

[gadgetgirl]

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.

[person4659]

gadgetgirl, on 16 January 2012 - 12:51 PM, said:

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

[person4659]

i get this error
Query to show fields from table failed

[person4659]

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");

[simplypixie]

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

[person4659]

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