CODEWITHSUNDEEP
mysqlperformanceselectjoinmultiple-tables
Arsylum
Arsylum

Reputation: 523

How to make an efficient SQL Select in this situation?

I have a table structure like this in a MySQL database:

#data_table
(id) | user_id | (more fields...)

#relations_table
(id) | user_id | user_coach_id | (...)

I would like to select all rows in relation_table that have a given user_coach_id and for each of those all rows in data_table that match the user_id. Ideally in one query, even more ideally in a way that will make processing of the data not too complicated.

My SQL skills are a bit rusty so I hope someone can help me out or at least point in the right direction.

(Note: The framework I will be using in this case is the wordpress wpdb class. But I think it's not really relevant.)

Edit: Thank you, I got to work what I have requested, but now I've run into a more complex scenario, so I made a more specific question for that.

Upvotes: 0

Views: 53

Answers (3)

Rick James
Rick James

Reputation: 142208

Relations_table is a many:many table? Leave out the id; it is a waste, and slows things down. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

The framework is relevant if it won't let you do what is optimal.

Upvotes: 1

AVI
AVI

Reputation: 5703

Just simply put a Join(for an example "Left Join")

SELECT data_table.id ,data_table.user_id ,data_table.(more_fields...), relations_table.user_id 
FROM data_table
LEFT JOIN relations_table
ON data_table.id =relations_table.user_id 
ORDER BY data_table.id ;

For more Reference : W3Schools SQL Tutorial in Join

Upvotes: 2

JBrooks
JBrooks

Reputation: 10013

"for a given user_coach_id" makes it: 

DECLARE @User_Coach_Id = 100

SELECT rt.user_id, ...
FROM #data_table dt
INNER JOIN #relations_table rt
ON dt.user_id = rt.user_id
WHERE rt.User_Coach_Id = @User_Coach_Id

Upvotes: 1

Related Questions