Two mysql select queries into one query

Question

user

id | name
1 | A
2 | B

article

id | title | descritption
1 | wx | xyz
2 | yz | abc

article_rating

article_id(article->id) | given_by(user->id) | given_rating
 1 2 4
 2 1 3

saved_article

article_id(article->id) | saved_by(user->id) 
 1 2 

I'm changing some buttons and icons depending on whether a user has rated/ saved an article or not. I'm using two select queries for that:

rated or not

SELECT given_rating as rating 
FROM article_rating 
WHERE given_by=? AND article_id=?

saved or not

SELECT saved_by as saved 
FROM saved_article 
WHERE saved_by=? AND article_id=?

So my question is how can i combine both into one mysql query efficiently??

• Raymond Nijland: define "efficiently" like a nice SQL query or performance or both..Can you post SHOW CREATE TABLE table structures also for every table..

Answer 1

If the article_id is the same in both tables, and you are looking for the same article in both tables, then you could use an INNER JOIN to join the two tables, using the article_id column.

SELECT ar.given_rating as rating, sa.saved_by as saved 
FROM article_rating ar
INNER JOIN saved_article sa
ON ar.article_id = sa.article_id
WHERE ar.given_by=? AND sa.saved_by=? AND ar.article_id=?;

DB-Fiddle

• Bipul Roy: thanks for the answer @Randi Vertongen. But one user may rate an article but may not save it. So inner join isn't the solution here, I think.
• Randi Vertongen: @Sharecorn then you don't want the two queries to be run everytime? You would have to add extra logic to check for that

Answer 2

INDEX(article_id, saved_by) will make it efficient. If that combo is unique, then make it the PRIMARY KEY.

Ditto for (article_id, given_by).

Answer 3

You can combine them to create one dataset using some LEFT JOINS if you know there aren't going to be some values in one of the tables:

SET @userid = 1;
SET @aid = 2;
SELECT 
 u.id as userid, a.id AS articleid, r.given_rating, sa.saved_by
FROM 
 user AS u
LEFT JOIN
 article_rating AS r ON u.id = r.given_by 
LEFT JOIN 
 article AS a ON a.id = r.article_id 
LEFT JOIN 
 saved_article AS sa ON sa.article_id = a.id
WHERE 
 u.id = @userid AND 
 (r.article_id = @aid or sa.article_id = @aid)

DBFiddle Here : https://www.db-fiddle.com/f/rb7x2DtAi76xpFBJLu5kqP/0