The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MySQL Help
|
Problems with nested queries and syntax errors
Discuss Problems with nested queries and syntax errors in the MySQL Help forum on Dev Shed.
Problems with nested queries and syntax errors MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
February 2nd, 2013, 04:56 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 5
Time spent in forums: 1 h 32 sec
Reputation 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.
|
February 2nd, 2013, 06:25 PM
|
 |
Lord of the Dance
|
|
|
|
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:
 Original
- 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;
|
February 2nd, 2013, 06:54 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 5
Time spent in forums: 1 h 32 sec
Reputation 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?
Quote: | 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:
| Original
- 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;
|
|
February 2nd, 2013, 10:19 PM
|
|
Lord of the Dance
|
|
|
|
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?
|
February 2nd, 2013, 10:25 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 5
Time spent in forums: 1 h 32 sec
Reputation 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.
Quote: | 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? |
|
February 4th, 2013, 04:25 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 5
Time spent in forums: 1 h 32 sec
Reputation 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?
Quote: | 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. |
|
February 4th, 2013, 05:51 PM
|
|
Lord of the Dance
|
|
|
|
|
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.
|
February 4th, 2013, 08:02 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 5
Time spent in forums: 1 h 32 sec
Reputation 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.
Quote: | 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. |
|
February 5th, 2013, 04:04 AM
|
|
Lord of the Dance
|
|
|
|
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.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
