Can I execute a prepared statement inside a MySQL SELECT?

I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all authorized/unlocked sellers from a 2nd table (any number of sellers).

I have the basic search query working, but I can't get the "dynamic LEFT JOIN" to work. I was told to do this in a prep statement, which I'm (a) struggling mightly (first time) and which I don't know where to put when working.

My search query will look like this:

SELECT articles AS art 
>
FROM bigtable AS bt
WHERE
  a lot of other criteria

This is the prep statement I came up with:

SET @sql_text := '
    DECLARE strCount   INT DEFAULT 1;

    SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs
    FROM buyerList AS b
    LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
    WHERE b.bid = ?

    SET @string = "LEFT JOIN preislisten AS p";

    lj:
      LOOP

        SET @string = CONCAT( @string, "ON (p.iln = a.iln AND p.preisliste = sid AND p.ean = a.ean AND p.iln = pricelist ) OR");

        SET strCount = strCount+1;
        IF strCount = recs
        THEN LEAVE lj;
        END IF;

    END LOOP lj;

    SET @string = CONCAT( @string,"(1=0)")
    ';

SET @param_iln = param_iln;
PREPARE stmt FROM @sql_text;
EXECUTE stmt using @param_iln;
DEALLOCATE PREPARE stmt;

So I'm basically finding all sellers & pricelist-name (default BASE) and then try to construct a string like this:

LEFT JOIN pricelists p ON
(p.sid = a.sid AND p.pricelist = "foo" AND p.ean = a.ean AND p.iln = 23467 ) OR 
(p.sid = a.sid AND p.pricelist = "bar" AND p.ean = a.ean AND p.iln = 99999) OR
... 
(1=0)

My questions:

(1) If there are 25 sellers, I will do 25 loops, but will the correct seller and pricelist be inserted into my string like this?

(2) How do I insert this into my actual search query? If I execute, I may get a string back but I can't just put the string into the query, can I?

Thank

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 ?

StackExchange Database Administrators Q#19670, answer score: 7