Can I execute a prepared statement inside a MySQL SELECT?

Question

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 
<< need to left join here >>
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?

Thanks for being easy on me... first time prep-statement, 2nd week MySQL...

EDIT:
So this is what I came up with:

# bigtable query
SELECT count(a.id) AS gesamt_datensaetze, a.nos, a.nos_anzeige
    FROM artikelstammdaten AS a

    #JOIN colors
    LEFT JOIN farbenzuordnung AS zu 
        ON a.farbe = zu.farbe 
        AND ( ( param_filter = '' ) AND (1=1) OR ( zu.ILN = param_filter ) )

  # ========== NEW PART ==========
  LEFT JOIN preislisten AS p
    ON (        p.iln = a.iln 
            AND p.EAN = a.EAN
            AND p.preisliste = (  #NEW GET pricelists      
                                                        SELECT IFNULL(klhs.preisliste, "-Standard-") AS pricelistID
                    FROM kundenliste_haendler AS klhd

                    LEFT JOIN kundenliste_hersteller AS klhs
                        ON klhs.iln = klhd.iln_verkaeufer
                        AND klhs.plz = klhd.plz
                        AND klhs.cid= klhd.cid
                                                WHERE klhd.iln_kaeufer = param_iln
                    GROUP BY pricelistID
                              )
           AND p.iln = ( #GET seller 
                  SELECT klhd.iln_verkaeufer AS sellerID
          FROM kundenliste_haendler AS klhd

                   LEFT JOIN kundenliste_hersteller AS klhs
                     ON klhs.iln = klhd.iln_verkaeufer
                     AND klhs.plz = klhd.plz
                     AND klhs.cid= klhd.cid

                     WHERE klhd.iln_kaeufer = param_iln
                     GROUP BY pricelistID
                     )
    )
    # active
    WHERE   a.aktiv = "ja"
    # more criteria

Stil trying to find out if this works correctly. If anyone can shed some insights on whether I can combine both my (identical) nested selects into a single nested select, it would be greatly appreciated!

Answer 1

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 ?

Answer 2

Focusing on the technical question of running sprocs inside SELECTS, no.

I created a test to demonstrate that the contexts of inline SQL and Stored Procedures are different animals:

mysql> delimiter $$
mysql> CREATE PROCEDURE test123 ()
    -> begin 
    -> SELECT NOW();
    -> end;
    -> $$
Query OK, 0 rows affected (1.04 sec)
mysql> delimiter ;
mysql> call test123();
+---------------------+
| NOW()               |
+---------------------+
| 2012-06-21 08:58:15 |
+---------------------+
1 row in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> SELECT t.* FROM ( call test123() ) t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call test123() ) t' at line 1
mysql> SELECT ( call test123() ) AS t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call test123() ) AS t' at line 1