CODEWITHSUNDEEP
mysql
Ben
Ben

Reputation: 687

mySQL : How to code query

I have 2 tables clients and grades where clients has a grade column with indices from 1 to 10. Those indices correspond to the ID's in the grades table (of course). Let's say a client has the indices 1,5,8 in the clients.grade column than I want to fetch * from those rows in the grades table.

My first approach was to use a grade field of type char(20) in the client table and fill it with indices separated by commas like 1,5,8 or others numbers. Then reading those values from one client from the client table I explode (PHP) the result and getting an array with 3 (or more) single values. Then in a following query I select all those rows in the grade table .

Now one question is wether to use a char field or an enumeration field and the other (general) question is how to make a simple, clever query for the entire task.

I did not code it yet. The grades table will have max. 10 rows, where as the client table has around 2.000 rows growing up to max. 5.000 rows. I hope I could describe my approach understandable.

Any suggestions are welcome

Upvotes: 1

Views: 61

Answers (1)

DaveyBoy
DaveyBoy

Reputation: 2924

Split the grade column into a new table (client_grade?) containing clientId and grade, and have multiple records for each client. The grades for a client can then be retrieved using a JOIN from the clients to the clients_grade table on the client field

Each field in a table should hold a single piece of information and comma-separating values will cause you extra processing. Using a separate table will also aid you if you want to find out who got a particular grade etc

Upvotes: 1

Related Questions