Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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 ?

share|improve this answer
    
Thanks so much! 22k vs. 106 :-) Give me a few mins to understand... So this will run through my x sellers/pricelists from my intial request and create x LEFT JOIN OR statements? That would be awesome! Can you also give me a hint on how to integrate this into my intial search query? –  frequent Jun 21 '12 at 16:40
    
You must ask yourself: What column in bigtable connects to a column in buyerlist? Once you figure that out, redo the SELECT to add a LEFT JOIN from bigtable to buyerlist. –  RolandoMySQLDBA Jun 21 '12 at 16:59
    
ok. trying that. –  frequent Jun 21 '12 at 17:40
    
sorry for the late replay. "a" should be "art" as in my original posting. The whole query is selecting articles (from a/art) with the LEFTJOIN being passed in as first "parameter" hardcoded in Coldfusion, which I'm now trying to pluck into MySQL –  frequent Jun 25 '12 at 19:59
    
to put it another way: "a" is "bigtable" ;-) –  frequent Jun 25 '12 at 20:04
add comment

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
share|improve this answer
    
Thanks. If you could also shed some light on the "on -the-how-to-do-this-then-side", you'd really make my day! –  frequent Jun 21 '12 at 16:07
    
I would look at using CASE syntax. re: dev.mysql.com/doc/refman/5.0/en/case-statement.html –  randy melder Jun 21 '12 at 16:08
    
Or, doing all the work inside a sproc, setting up the state, then running the query inside the sproc. –  randy melder Jun 21 '12 at 16:10
    
You mean do all the search query stuff inside the prep-statement? Can I pass in 20parameters? Will this be a performance drainer vs. doing it directly in MySQL? –  frequent Jun 21 '12 at 16:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.