Need help with a query

[huxphlux]

Hello!

 

I haveing a bit of a struggle with this one, i appriciate all help i get!

 

I got a value thats an id. It's inside a session so ive have fetched it into a variable called $mId

 

I got two database tables

 

First one called: users (Contains field mem_id and player_id)

Second one: players (Contains field name, player_id)

 

So what im trying to do is to get the player_id from the users table, to find the playert_id i want to use the $mId

variable. Because mId$ is the same as mem_id database field. When i got the player_id i want to get the name from players with my player_id i fetched from the users table.

 

[ejaboneta]

SELECT p.name AS name FROM players p LEFT JOIN users u ON u.player_id = p.player_id WHERE u.mem_id = '$mId'

[awjudd]

There is no need for a LEFT JOIN here ... you will be mostly eliminating the benefits with your where clause anyways (original set that is returned will be much larger than it needs to be) ...

 

SELECT `p`.`name` FROM `players` p JOIN `users` u ON `p`.`player_id` = `u`.`player_id` WHERE `u`.`mem_id` = $mId

 

Or you could move the restriction of the member id to the ON clause so it has more conditions (returning less results from the beginning of the join).

 

SELECT `p`.`name` FROM `players` p JOIN `users` u ON ( `p`.`player_id` = `u`.`player_id` AND `u`.`mem_id` = $mId )

 

~juddster

[caedmon_m]

I'm having a similar problem and it's driving me mad... basically I have a simple forum held in two table, one for topics and once for posts related to those topics.

 

I need to list the topics according to the date of the most recent post, this is what I've got at the moment:

 

SELECT VHS_pub_topics.* FROM VHS_pub_topics JOIN VHS_pub_posts ON VHS_pub_topics.topicID = VHS_pub_posts.topicID ORDER BY VHS_pub_posts.date;

 

However this gets multiple results for each topic as it counts the instances of a topic according to the number of related posts.

 

How do I get it to simply sort the VHS_pub_topics table by the VHS_pub_posts.date ?

 

I'm sure it's simple enough, but I'm going around in circles at the moment!