CODEWITHSUNDEEP
mysqlsql
YosiFZ
YosiFZ

Reputation: 7900

MySql Wrong command

I have two tables in the sql:

client
id   first
1    david
2    jenna
3    ben

rating
id  clientid userid rating ratetext         date
1   1        3      4      Very good        12/4/2012
2   3        6      3      Simple bla bla   5/3/2013

And i want to get all the rating for a userid so i try something like:

SELECT rating,ratetext,date,first FROM rating r 
INNER JOIN client c ON r.userid = 3;

But i always get the rows with other rows that i don't need to get. any idea what is wrong with my command?

Upvotes: 1

Views: 54

Answers (4)

Mike Brant
Mike Brant

Reputation: 71422

I believe this is what you are looking for:

SELECT r.rating, r.ratetext, r.date, c.first
FROM rating AS r
INNER JOIN client AS c
  ON r.clientid = c.id
WHERE r.userid = 3

You should join on the client id columns of the two tables and then use userid in where clause to filter.

Make sure you have indexes on r.clientid and r.userid.

Upvotes: 1

Stephen Fischer
Stephen Fischer

Reputation: 2546

Your joins should describe what two pieces of information connects the two tables. You want something along the lines of:

SELECT * FROM rating r, client c WHERE r.clientid=c.id AND r.userid=3

Upvotes: 1

Vaibhav Desai
Vaibhav Desai

Reputation: 2728

SELECT rating, ratetext, date, first 
FROM   rating r INNER JOIN client c 
ON     r.clientid = c.id
WHERE  r.userid = 3;

Upvotes: 3

Matt Ball
Matt Ball

Reputation: 360026

Presumably you want to select ratings with the explicitly specified ID, and the corresponding clients because on the clientid stored in the rating record:

SELECT rating,ratetext,date,first FROM rating r 
INNER JOIN client c ON c.id = r.clientid
WHERE r.userid = 3;

Upvotes: 3

Related Questions