Complex SELECT query using two tables.

[GarethB]

Hi guys,

 

This one has really confused me.

 

The Tables:

users (id, fname, lname, teamname, won, lost, total)

teams (id, teamname, divison, league)

 

Variables

$division="Division 2";

$league="Thursday";

 

I need to create a query that will do the following in a while loop (the bits in brackets is the bits I can't figure out):

 

SELECT * FROM users WHERE teamname=(teamname from table teams, division = division from teams and league = leagues from teams) ORDER BY won DESC, total ASC

 

 

I hope that makes sense but I really can't work it out!

 

Cheers

[GarethB]

Would this be right?

$players = "SELECT * FROM users WHERE teamname=(SELECT teamname FROM teams WHERE division='$division' AND league='$league') ORDER BY won DESC, total ASC";

[mikosiko]

this is better

 

SELECT users.* 
   FROM users 
      JOIN teams ON users.teamname = teams.teamname AND teams.division='$division' AND teams.league='$league'
ORDER BY users.won DESC, users.total ASC

 

however, I will suggest you to review your tables design; the data modeling could be improved... per. example (just a couple of small ones):

- in your table user you should be referencing/storing  the team.id and not the team.teamname (same concept could be applied to the fields division and league in your table teams).

- in your table user you should no be storing a dependent field (total field is a dependent one)... calculated values should be calculated upon retrieval.

 

[GarethB]

Thank you - that works great.

 

I have started using the id's of certain things but not for the team. Would it be best changing it now before it gets too late? I presume this is for quicker referencing?

 

As for the total thing, when the results go into the table, it simply increments total by however many have been played. Is there a reason why this is incorrect?

 

Thanks for your time - I'm still quite new and abosirbing as much as possible!

[mikosiko]

I have started using the id's of certain things but not for the team. Would it be best changing it now before it gets too late? I presume this is for quicker referencing?

You should if you want to have a good DB design, remember that the relational model is the foundation of SQL, therefore the better your model the better and easy your queries will be (Did I sound like Yoda here ?)... and no.. is not for quick referencing (but could result on).. is for proper db modeling.

 

As for the total thing, when the results go into the table, it simply increments total by however many have been played. Is there a reason why this is incorrect?

that is easily covered for any aggregation function (COUNT, SUM, etc) on retrieval... in  a good DB model you should avoid store dependent fields in your table.

 

Maybe a search and read upon "Database Normalization" will help you further

[GarethB]

Thank you very much for your time and help (Yoda! ). I will have a good read and will improve upon my current knowledge.

[GarethB]

I've had a good read on Database Normalization. Would I be alright staring a new thread to discuss the database structure?