Mysql – Two thesql select queries into one query

MySQL

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

Best Answer

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

Related Solutions

Mysql – execute a prepared statement inside a MySQL SELECT

From the question, it looks like what you have written quasi-resembles what Oracle calls an anonymous code block. MySQL does not facilitate such a mechanism.

With regard to what you want to accomplish, you do not need a stored procedure.

Try assembling the SQL statement like this:

@stmt = 'SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs FROM buyerList AS b ';
@stmt = CONCAT(@stmt,'LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass ');
@stmt = CONCAT(@stmt,'LEFT JOIN pricelists p ON ');
SELECT GROUP_CONCAT(CONCAT('(p.sid = a.sid AND p.preisliste = ',sid,' AND p.ean = a.ean AND p.iln = ',pricelist',)') SEPARATOR ' OR ')
INTO @LeftJoinClause FROM preislisten;
@stmt = CONCAT(@stmt,@LeftJoinClause,' WHERE b.bid = ?');
SELECT @stmt\G

This will print out the desired query

If it is the desired query, execute it

PREPARE sql FROM @stmt;
EXECUTE sql using @param_iln;
DEALLOCATE PREPARE sql;

Give it a Try !!!

If you actually want the query to be small without hardcoding every value just write the code with a more straightforward LEFT JOIN setup

SELECT p.sid, ifnull(p.pricelist,"BASE"), count(*) AS recs FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
LEFT JOIN pricelists p ON
    (p.sid = a.sid AND p.preisliste = a.sid AND p.ean = a.ean)
WHERE b.bid = ?
GROUP BY p.sid,p.pricelist;

I also just noticed you have a.sid and a.ean, what table has an alias of a ?

MySQL Query Optimization – Indexes & Joins 12 million+ records

With the disclaimer that I'm not specifically a MySQL expert:

My guess is that either it's failing to whittle down the huge number of rows that result from such as huge join, or it can't index the title well because the LIKE starts with a wildcard.

Testing the second is easy, see if it runs better without a leading wildcard. If this is the case we can look for solutions.

For the first (and potentially the second as well), I would suggest breaking it up by doing a smaller join into a temporary table, then joining that with the other tables.

For example, if we can assume that there are foreign rows in all tables for a given track, we can SELECT, ORDER and LIMIT based on track_title alone into a temporary table. Then join in all the other tables.

Best Answer

Related Solutions

Mysql – execute a prepared statement inside a MySQL SELECT

MySQL Query Optimization – Indexes & Joins 12 million+ records

Related Question