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??

define "efficiently" like a nice SQL query or performance or both..Can you post SHOW CREATE TABLE table structures also for every table.. — Raymond Nijland
– Raymond Nijland, Commented Feb 24, 2019 at 20:09

Randi Vertongen · Accepted Answer · 2019-02-24 18:16:47Z

0

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

answered Feb 24, 2019 at 18:16

Randi Vertongen

16.6k4 gold badges36 silver badges64 bronze badges

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.

Bipul Roy
– Bipul Roy

2019-02-25 04:15:52 +00:00
Commented Feb 25, 2019 at 4:15
@Sharecorn then you don't want the two queries to be run everytime? You would have to add extra logic to check for that

Randi Vertongen
– Randi Vertongen

2019-02-25 19:36:13 +00:00
Commented Feb 25, 2019 at 19:36

Add a comment |

Rick James · Accepted Answer · 2019-03-06 04:17:22Z

0

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).

answered Mar 6, 2019 at 4:17

Rick James

80.8k5 gold badges52 silver badges119 bronze badges

Add a comment |

betelgeuce · Accepted Answer · 2023-12-08 10:29:35Z

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

Stack Exchange Network

Two mysql select queries into one query

3 Answers 3

Your Answer

Hot Network Questions

Two mysql select queries into one query

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions