Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query outputs errors in newer version of mysql 1

Status
Not open for further replies.
Sleidia

Sleidia

Technical User
May 4, 2001
1,284
FR
Hi,

The query below works perfectly on MySQL - 4.0.16-nt but on MySQL - 4.1.19-standard, I get the following error :

#1109 - Unknown table 't7' in order clause
Click to expand...

Does anyone has an explanation?
Thanks!

Code: 
SELECT t7. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID, '.', t6.cat_ID, '.', t7.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID
INNER JOIN mod_ecom_prod_cats AS t7 ON t7.cat_parent_ID = t6.cat_ID
AND (
t1.cat_ID = "0"
)
WHERE t1.cat_parent_ID = "0"
UNION ALL SELECT t6. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID, '.', t6.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID
AND (
t1.cat_ID = "0"
)
WHERE t1.cat_parent_ID = "0"
UNION ALL SELECT t5. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
AND (
t1.cat_ID = "0"
)
WHERE t1.cat_parent_ID = "0"
UNION ALL SELECT t4. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
AND (
t1.cat_ID = "0"
)
WHERE t1.cat_parent_ID = "0"
UNION ALL SELECT t3. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
AND (
t1.cat_ID = "0"
)
WHERE t1.cat_parent_ID = "0"
UNION ALL SELECT t2. * , CONCAT( t1.cat_ID, '.', t2.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
AND (
t1.cat_ID = "0"
)
WHERE t1.cat_parent_ID = "0"
UNION ALL SELECT t1. * , t1.cat_ID AS test
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_parent_ID = "0"
ORDER BY t7.cat_parent_ID, cat_ID
LIMIT 0 , 100
 
Sort by date Sort by votes
you have the following condition in each SELECT --

AND ( t1.cat_ID = "0" )

unfortunately, you are appending this onto the wrong JOIN condition

for example, this is wrong

INNER JOIN mod_ecom_prod_cats AS t7
ON t7.cat_parent_ID = t6.cat_ID
AND ( t1.cat_ID = "0" )

you can't test a t1 column as part of the t7-t6 join, it doesn't make sense, and by the way, it will cause an error in version 5

anyhow, getting to the error on the ORDER BY...

try this -- wrap the entire UNION query in parentheses, and then put the ORDER BY last, without referencing t7
Code: 
[red][b]([/b][/red]
SELECT t7.* , ...
UNION ALL SELECT t6.* , ...
UNION ALL SELECT t5.* , ...
UNION ALL SELECT t4.* , ...
UNION ALL SELECT t3.* , ...
UNION ALL SELECT t2.* , ...
UNION ALL SELECT t1.* , t1.cat_ID AS test
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_parent_ID = "0"
[red][b])[/b][/red]
ORDER BY cat_parent_ID, cat_ID
LIMIT 0 , 100



r937.com | rudy.ca
 
Upvote 0 Downvote

Hi Rudy,

I'm very sorry, I don't understand how to put the pieces together from the example you gave :(

 
Upvote 0 Downvote
take your entire query, exactly like you posted it here originally

add ( in front of the first SELECT

add ) in front of the ORDER BY

that hopefully will take care of your error message

regarding AND t1.cat_ID = "0", you should change this to a WHERE condition before upgrading to version 5



r937.com | rudy.ca
 
Upvote 0 Downvote
I've tried the parenthesis trick but, unfortunately, it didn't work.

Then, I've tried this wich didn't produce any error but which lacks the WHERE t1.cat_ID = "0" that I absolutely need to call on every select :

Code: 
SELECT t7. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID, '.', t6.cat_ID, '.', t7.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID
INNER JOIN mod_ecom_prod_cats AS t7 ON t7.cat_parent_ID = t6.cat_ID

UNION ALL 
SELECT t6. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID, '.', t6.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID

UNION ALL
SELECT t5. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID

UNION ALL 
SELECT t4. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID

UNION ALL
SELECT t3. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID

UNION ALL
SELECT t2. * , CONCAT( t1.cat_ID, '.', t2.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID

UNION ALL SELECT t1.* , t1.cat_ID AS test
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_parent_ID = "0"

ORDER BY cat_parent_ID, cat_ID
LIMIT 0 , 100

So, I've tried this but it threw errors again :

Code: 
SELECT t7. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID, '.', t6.cat_ID, '.', t7.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_ID = "0"
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID
INNER JOIN mod_ecom_prod_cats AS t7 ON t7.cat_parent_ID = t6.cat_ID

UNION ALL 
SELECT t6. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID, '.', t6.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_ID = "0"
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID

UNION ALL
SELECT t5. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_ID = "0"
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID

UNION ALL 
SELECT t4. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_ID = "0"
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID

UNION ALL
SELECT t3. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_ID = "0"
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID

UNION ALL
SELECT t2. * , CONCAT( t1.cat_ID, '.', t2.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_ID = "0"
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID

UNION ALL SELECT t1.* , t1.cat_ID AS test
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_parent_ID = "0"

ORDER BY cat_parent_ID, cat_ID
LIMIT 0 , 100
 
Upvote 0 Downvote
Code: 
SELECT * FROM 
(
SELECT t7.* 
     , CONCAT_WS('.', t1.cat_ID 
                    , t2.cat_ID 
                    , t3.cat_ID 
                    , t4.cat_ID 
                    , t5.cat_ID 
                    , t6.cat_ID 
                    , t7.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID
INNER JOIN mod_ecom_prod_cats AS t7 ON t7.cat_parent_ID = t6.cat_ID
WHERE t1.cat_ID = 0
  AND t1.cat_parent_ID = 0
UNION ALL
SELECT t6.*
     , CONCAT_WS('.', t1.cat_ID 
                    , t2.cat_ID 
                    , t3.cat_ID 
                    , t4.cat_ID 
                    , t5.cat_ID 
                    , t6.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID
WHERE t1.cat_ID = 0
  AND t1.cat_parent_ID = 0
UNION ALL
SELECT t5.*
     , CONCAT_WS('.', t1.cat_ID 
                    , t2.cat_ID 
                    , t3.cat_ID 
                    , t4.cat_ID 
                    , t5.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
WHERE t1.cat_ID = 0
  AND t1.cat_parent_ID = 0
UNION ALL
SELECT t4.*
     , CONCAT_WS('.', t1.cat_ID 
                    , t2.cat_ID 
                    , t3.cat_ID 
                    , t4.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
WHERE t1.cat_ID = 0
  AND t1.cat_parent_ID = 0
UNION ALL
SELECT t3.*
     , CONCAT_WS('.', t1.cat_ID 
                    , t2.cat_ID 
                    , t3.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
WHERE t1.cat_ID = 0
  AND t1.cat_parent_ID = 0
UNION ALL
SELECT t2.*
     , CONCAT_WS('.', t1.cat_ID 
                    , t2.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
WHERE t1.cat_ID = 0
  AND t1.cat_parent_ID = 0
UNION ALL
SELECT t1.* 
     , t1.cat_ID AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_parent_ID = 0
) as data
ORDER 
    BY cat_parent_ID
     , cat_ID
LIMIT 0 , 100

r937.com | rudy.ca
 
Upvote 0 Downvote
Thanks Rudy :)

Unfortunately, your solution doesn't work on mysql ver. 4.0.

BUT, I actually found the query that works on both ver. 4.0 and 4.1.

It's simply the first one posted on this thread but with "t7." removed from the ORDER BY clause. Actually, the error messsage said it all ;)

I don't know if it will work on ver. 5.0 though.

Lastly, I want to thank you warmly for always being here when your help is greatly needed.

I would never be where I am without you.

Anyway, here is, for example, the same query, the difference being that this one starts from a sublevel :

Code: 
SELECT t7. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID, '.', t6.cat_ID, '.', t7.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID
INNER JOIN mod_ecom_prod_cats AS t7 ON t7.cat_parent_ID = t6.cat_ID
AND (
t1.cat_ID = "2"
OR t1.cat_ID = "13"
OR t1.cat_ID = "17"
OR t1.cat_ID = "27"
)
WHERE t1.cat_parent_ID = "2"
UNION ALL SELECT t6. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID, '.', t6.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
INNER JOIN mod_ecom_prod_cats AS t6 ON t6.cat_parent_ID = t5.cat_ID
AND (
t1.cat_ID = "2"
OR t1.cat_ID = "13"
OR t1.cat_ID = "17"
OR t1.cat_ID = "27"
)
WHERE t1.cat_parent_ID = "2"
UNION ALL SELECT t5. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID, '.', t5.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
INNER JOIN mod_ecom_prod_cats AS t5 ON t5.cat_parent_ID = t4.cat_ID
AND (
t1.cat_ID = "2"
OR t1.cat_ID = "13"
OR t1.cat_ID = "17"
OR t1.cat_ID = "27"
)
WHERE t1.cat_parent_ID = "2"
UNION ALL SELECT t4. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN mod_ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
AND (
t1.cat_ID = "2"
OR t1.cat_ID = "13"
OR t1.cat_ID = "17"
OR t1.cat_ID = "27"
)
WHERE t1.cat_parent_ID = "2"
UNION ALL SELECT t3. * , CONCAT( t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN mod_ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
AND (
t1.cat_ID = "2"
OR t1.cat_ID = "13"
OR t1.cat_ID = "17"
OR t1.cat_ID = "27"
)
WHERE t1.cat_parent_ID = "2"
UNION ALL SELECT t2. * , CONCAT( t1.cat_ID, '.', t2.cat_ID ) AS cat_parent_IDs
FROM mod_ecom_prod_cats AS t1
INNER JOIN mod_ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
AND (
t1.cat_ID = "2"
OR t1.cat_ID = "13"
OR t1.cat_ID = "17"
OR t1.cat_ID = "27"
)
WHERE t1.cat_parent_ID = "2"
UNION ALL SELECT t1. * , t1.cat_ID AS test
FROM mod_ecom_prod_cats AS t1
WHERE t1.cat_parent_ID = "2"
ORDER BY cat_parent_ID, cat_ID
LIMIT 0 , 100




 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

E
  • Locked
  • Question
MySQL calculator tools
Replies
0
Views
225
EdZzy
E
lupidol
  • Locked
  • Question
Cannot create new table in mySQL 3
Replies
6
Views
442
lupidol
lupidol
EdLentz
  • Locked
  • Question
How do I use a keyword in a query??
Replies
6
Views
510
EdLentz
EdLentz
dylim
  • Locked
  • Question
SQL_CALC_FOUND_ROWS still runs in MySQL 8.0.29
Replies
2
Views
476
dylim
dylim
lupidol
  • Locked
  • Question
mysql cannot add rows with "UNION" 1
Replies
5
Views
400
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top