CODEWITHSUNDEEP
mysqlsqljoinmariadb
Karlom
Karlom

Reputation: 14834

How to make join between 2 tables using 2 values?

In Mariadb database I want to make a query to join two tables: joke and jokevote to get all jokes which are upvoted by a certain user.

Here are the tables:

joke

+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| id          | int(6) unsigned  | NO   | PRI | NULL              | auto_increment |
| title       | varchar(255)     | NO   |     | None              |                |
| content     | mediumtext       | NO   |     | NULL              |                |
| user_id     | int(10) unsigned | YES  |     | 0                 |                |
| username    | varchar(32)      | NO   |     | Unknown           |                |
...

jokevote:

+---------+------------+------+-----+---------+----------------+
| Field   | Type       | Null | Key | Default | Extra          |
+---------+------------+------+-----+---------+----------------+
| id      | int(11)    | NO   | PRI | NULL    | auto_increment |
| joke_id | int(11)    | NO   |     | NULL    |                |
| user_id | int(11)    | NO   |     | NULL    |                |
| vote    | tinyint(4) | NO   |     | NULL    |                |
+---------+------------+------+-----+---------+----------------+

The content of jokevote is: enter image description here

Some values in joke table (related to user 6): enter image description here

One of the query that I tried is:

    SELECT * FROM joke LEFT JOIN jokevote 
     ON joke.user_id=6  AND joke.id=jokevote.joke_id 
     AND jokevote.vote=1;

The query should return only 2 jokes, but it returns all of the jokes. I've also tried other queries like this:

SELECT * FROM joke LEFT JOIN jokevote 
ON joke.user_id=6  AND joke.id=jokevote.joke_id AND 
jokevote.vote=1 AND joke.user_id=jokevote.user_id AND ;

(again, returns all of the jokes)

So I'm left clueless and wondering what's the correct query in this case?

Upvotes: 0

Views: 68

Answers (1)

Rahul
Rahul

Reputation: 77876

I think you meant to do a INNER JOIN instead saying

SELECT * FROM joke INNER JOIN jokevote 
 ON joke.id=jokevote.joke_id 
 WHERE jokevote.vote=1
 AND joke.user_id=6;

Upvotes: 3

Related Questions