#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0

    Problems with nested queries and syntax errors


    I am designing a MySQL query which consists of 3, possibly 4 nested layers. The two innermost layers run just fine and return correct results, but I'm having trouble getting the syntax of the 3rd layer correct. I believe the problem stems from the fact that I need to alias the derived table `CC1` as `Mirror2` in order to join it to itself, but I can't figure out the syntax error I am getting. Here's what I have:

    Code:
    SELECT DISTINCTROW CC1.PK, CC1.MDPK, CC1.ODPK,
               CC1.Catalog, CC1.CN, CC1.URL, CC1.CC, 
               CC1.FNK, CC1.DPT, CC1.ProductManager, CC1.PartNo
     FROM 
    
      (
       (
         SELECT DISTINCT Dup1.PK, PM.MDPK, PM.ODPK,  
                     Catalog.catalog, Catalog.CN, PM.URL,
                     Dup1.CC, Dup1.FNK, PM.PartNo, PM.DPT,  
                     Catalog.ProductManager
          FROM 
           (
            (
             SELECT cpc2.PK, cpc2.CC, cpc2.PT, cpc2.FNK, 
                        cpc2.Market
              FROM cpc2
              AS mirror
              INNER JOIN cpc2 ON cpc2.Market = mirror.Market 
                    AND cpc2.FNK = mirror.FNK 
                    AND cpc2.PT = mirror.PT 
                    AND cpc2.CC = mirror.CC
              WHERE cpc2.PK<>mirror.PK AND cpc2.CC>1
             )
    
              AS Dup1
            INNER JOIN PM ON PM.PK = Dup1.PK
           )
            INNER JOIN Catalog ON PM.Catalog = Catalog.Catalog
       )
            AS CC1
     )
       AS Mirror2
    
      INNER JOIN CC1 ON CC1.DPT = Mirror2.DPT 
             AND CC1.FNK =  Mirror2.FNK 
             AND Mirror2.CC = CC1.CC AND CC1.URL = Mirror2.URL 
             AND Mirror2.CN = CC1.CN 
             AND CC1.Catalog = Mirror2.Catalog 
             AND Mirror2.ODPK = CC1.ODPK 
             AND CC1.MDPK = Mirror2.MDPK
    
      WHERE  CC1.PK<>Mirror2.PK AND CC1.CC>1;
    But when I run this I get a syntax error, seemingly in this area `AS Mirror2` and I can't seem to resolve it. As far as I can tell (and I'm very, very new to this) all the parenthesis are closed and for the life of me I can't figure out where the issue is. I need to take the derived table generated by the middle layer query and then join it to itself (requiring an alias). I've tested the two inner queries and they run fine, so the problem is definitely with the outer layer.

    Can anyone help? I've been looking at this for hours but can't figure it out.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,612
    Rep Power
    1945
    Related to the mirror2 error, uou should not create mirror2 as its own table, but instead use it as an alias for the inner join table.
    Similar to what you did to join the cpc2 table (untested):
    sql Code:
     
    SELECT DISTINCTROW CC1.PK
    	, CC1.MDPK
    	, CC1.ODPK
    	, CC1.Catalog
    	, CC1.CN
    	, CC1.URL
    	, CC1.CC
    	, CC1.FNK
    	, CC1.DPT
    	, CC1.ProductManager
    	, CC1.PartNo
     FROM 
    (
    	SELECT DISTINCT Dup1.PK
    				, PM.MDPK
    				, PM.ODPK
    				, Catalog.catalog
    				, Catalog.CN
    				, PM.URL
    				, Dup1.CC
    				, Dup1.FNK
    				, PM.PartNo
    				, PM.DPT
    				, Catalog.ProductManager
    	FROM 
    	(
    		(
    			SELECT cpc2.PK
    				, cpc2.CC
    				, cpc2.PT
    				, cpc2.FNK
    				, cpc2.Market          
    			FROM cpc2 AS mirror
    			INNER JOIN cpc2 ON cpc2.Market = mirror.Market 
    								AND cpc2.FNK = mirror.FNK 
    								AND cpc2.PT = mirror.PT 
    								AND cpc2.CC = mirror.CC
    			WHERE cpc2.PK<>mirror.PK 
    				AND cpc2.CC>1
    		) AS Dup1
    		INNER JOIN PM ON PM.PK = Dup1.PK
    	)
    	INNER JOIN Catalog ON PM.Catalog = Catalog.Catalog
    ) AS CC1 AS Mirror2
    INNER JOIN CC1 ON CC1.DPT = Mirror2.DPT 
    	 AND CC1.FNK =  Mirror2.FNK 
    	 AND Mirror2.CC = CC1.CC AND CC1.URL = Mirror2.URL 
    	 AND Mirror2.CN = CC1.CN 
    	 AND CC1.Catalog = Mirror2.Catalog 
    	 AND Mirror2.ODPK = CC1.ODPK 
    	 AND CC1.MDPK = Mirror2.MDPK
    WHERE  CC1.PK<>Mirror2.PK AND CC1.CC>1;
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    Thanks! Although that is what I had originally, and it produces the same error when I test it. I need this table to be aliased as CC1 in one query, then as Mirror2 in another. This is because my outermost query is joining this derived table to itself.

    Maybe a better way to explain it is that I am deriving tableX. I need tableX to be called up by either of two aliases, in order to join it with itself. I had hoped by closing a parenthesis after the first AS this would cause MySQL to consider this table as two separate objects as opposed to just re-aliasing.

    Are the parenthesis not needed to separate the two AS statements?


    Originally Posted by MrFujin
    Related to the mirror2 error, uou should not create mirror2 as its own table, but instead use it as an alias for the inner join table.
    Similar to what you did to join the cpc2 table (untested):
    sql Code:
     
    SELECT DISTINCTROW CC1.PK
    	, CC1.MDPK
    	, CC1.ODPK
    	, CC1.Catalog
    	, CC1.CN
    	, CC1.URL
    	, CC1.CC
    	, CC1.FNK
    	, CC1.DPT
    	, CC1.ProductManager
    	, CC1.PartNo
     FROM 
    (
    	SELECT DISTINCT Dup1.PK
    				, PM.MDPK
    				, PM.ODPK
    				, Catalog.catalog
    				, Catalog.CN
    				, PM.URL
    				, Dup1.CC
    				, Dup1.FNK
    				, PM.PartNo
    				, PM.DPT
    				, Catalog.ProductManager
    	FROM 
    	(
    		(
    			SELECT cpc2.PK
    				, cpc2.CC
    				, cpc2.PT
    				, cpc2.FNK
    				, cpc2.Market          
    			FROM cpc2 AS mirror
    			INNER JOIN cpc2 ON cpc2.Market = mirror.Market 
    								AND cpc2.FNK = mirror.FNK 
    								AND cpc2.PT = mirror.PT 
    								AND cpc2.CC = mirror.CC
    			WHERE cpc2.PK<>mirror.PK 
    				AND cpc2.CC>1
    		) AS Dup1
    		INNER JOIN PM ON PM.PK = Dup1.PK
    	)
    	INNER JOIN Catalog ON PM.Catalog = Catalog.Catalog
    ) AS CC1 AS Mirror2
    INNER JOIN CC1 ON CC1.DPT = Mirror2.DPT 
    	 AND CC1.FNK =  Mirror2.FNK 
    	 AND Mirror2.CC = CC1.CC AND CC1.URL = Mirror2.URL 
    	 AND Mirror2.CN = CC1.CN 
    	 AND CC1.Catalog = Mirror2.Catalog 
    	 AND Mirror2.ODPK = CC1.ODPK 
    	 AND CC1.MDPK = Mirror2.MDPK
    WHERE  CC1.PK<>Mirror2.PK AND CC1.CC>1;
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,612
    Rep Power
    1945
    Can see I wasn't fully concentrated.
    What if you replace this
    Code:
    ) AS CC1 AS Mirror2
    INNER JOIN CC1 ON CC1.DPT = Mirror2.DPT
    with this:
    Code:
    ) AS CC1 
    INNER JOIN CC1 AS Mirror2 ON CC1.DPT = Mirror2.DPT
    Can you post (copy/paste) the exact error message you receive?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    Oh wow you don't know how happy I am right now. I think that works. I'm still getting an error message, but that's because I don't have access to the source database at the moment. Now I get a 'table not found' error instead of a syntax error which I'm taking as a good sign.

    Thank you so much! This was driving me nuts enough that I'm working on it on the weekend. I had a sneaking suspicion that it had something to do with that join as well, but as this is my first foray into SQL at all I didn't know exactly what was going on.



    Originally Posted by MrFujin
    Can see I wasn't fully concentrated.
    What if you replace this
    Code:
    ) AS CC1 AS Mirror2
    INNER JOIN CC1 ON CC1.DPT = Mirror2.DPT
    with this:
    Code:
    ) AS CC1 
    INNER JOIN CC1 AS Mirror2 ON CC1.DPT = Mirror2.DPT
    Can you post (copy/paste) the exact error message you receive?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    Maybe I spoke too soon. Now I'm getting:

    Syntax error in FROM clause

    and it highlights the "SELECT" from
    Code:
     FROM (SELECT DISTINCT Dup1.PK, PM.MDPK, PM.ODPK, Catalog.catalog,
    section in the second layer of query.

    I know this runs fine without the outer-most layer so I'm not sure what to make of this. Maybe the "SELECT DISTINCTROW" can't pull from a derived table?


    Originally Posted by Leon1917
    Oh wow you don't know how happy I am right now. I think that works. I'm still getting an error message, but that's because I don't have access to the source database at the moment. Now I get a 'table not found' error instead of a syntax error which I'm taking as a good sign.

    Thank you so much! This was driving me nuts enough that I'm working on it on the weekend. I had a sneaking suspicion that it had something to do with that join as well, but as this is my first foray into SQL at all I didn't know exactly what was going on.
  12. #7
  13. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,612
    Rep Power
    1945
    As i see it, you are doing following:
    Self-Join cpc2 with cpc2 to get Dup1
    Then join the result with PM
    Then join this result with Catalog and get the distinct result
    Then make a new self-join of this result (CC1) and get the distinct result .

    Maybe you can explain why you need the outer-most layer join (CC1).
    It looks a bit strange from my point of view.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    Well, the more I think about it there must be a better way, but this is my first foray into anything like this so I'm basically trying to translate a series of make-table queries that I had in Access to one pass-through query.

    I joined cpc2 on itself to essentially find records where all fields but PK were the same. Finding duplicates, essentially. However, the information I need to determine whether a pair of PK fields are truly duplicates is stored in several tables and joining them all at the start was just too much for Access to handle with massive linked tables. The first cpc2=cpc2 join was to get a manageable amount of results for processing speed.

    The second layer (where I join Dup1 to Catalog and PM tables) is just to associate the various PK values in question with data from Catalog and PM.

    The third layer is to repeat the process of the first layer; joining the table to itself to isolate duplicates across several fields.

    I might be able to do this in 2 steps as joining cpc2 to itself, PM and Catalog should run more efficiently as a pass though, but I will still need to join that resulting table to itself to find the duplicate records, so I'm not sure if it will solve the problem.

    By the way, I found my FROM clause error, I just can't use SELECT DISTINCT or SELECT DISTINCTROW the way I was trying to. I'll have to try to use GROUP BY instead. When I run this with all SELECT statements it fixes that problem, but won't recognize "CatCross" as an alias anymore and I get a table not found message. This tells me there is still something wrong with aliasing the results of the second query somehow.


    Originally Posted by MrFujin
    As i see it, you are doing following:
    Self-Join cpc2 with cpc2 to get Dup1
    Then join the result with PM
    Then join this result with Catalog and get the distinct result
    Then make a new self-join of this result (CC1) and get the distinct result .

    Maybe you can explain why you need the outer-most layer join (CC1).
    It looks a bit strange from my point of view.
  16. #9
  17. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,612
    Rep Power
    1945
    IMO, you nested select does look a bit strange even when you said it is working.
    Try look at the below query, where I have added what I think is missing (marked with bold).
    SELECT * will have to be changed to the fields you want to get.
    Code:
        FROM
        (
            SELECT *
            FROM 
            (
                SELECT cpc2.PK
                    , cpc2.CC
                    , cpc2.PT
                    , cpc2.FNK
                    , cpc2.Market         
                FROM cpc2 AS mirror
                INNER JOIN cpc2 ON cpc2.Market = mirror.Market
                                    AND cpc2.FNK = mirror.FNK
                                    AND cpc2.PT = mirror.PT
                                    AND cpc2.CC = mirror.CC
                WHERE cpc2.PK<>mirror.PK
                    AND cpc2.CC>1
            ) AS Dup1
            INNER JOIN PM ON PM.PK = Dup1.PK
        ) AS yourAlias
    Regarding the use of DISTINCT(ROW): if you select any primary/unique (PK?) fields, then the result will already be DISTINCT by design and you will not gain any different of adding it.

    Using GROUP allow you to specify which fields to group together.

IMN logo majestic logo threadwatch logo seochat tools logo