Derived Table doesn't exist - mySQL Error 1146 (42S02)

I'm trying to build a cohort analysis in mySQL and have run into the below issue. I'm running the code in an online compiler (https://paiza.io).

So far I have created sups and ords table and the below start of query:

select coh_nos.coh_week, coh_nos.id as coh_items
from
(select 
 WEEK(sup_timestamp) AS coh_week,
 user_id AS id
 from sups as S 
order by coh_week, id
) as coh_nos

join 

(select ordmade.id, ordmade.weeks_diff as ordmade_items
from
 (select 
 user_id as id, 
 FLOOR(DATEDIFF(ord_timestamp, 
 coh_nos.coh_week)/7 ) as weeks_diff
 from ords as O 
 ) as in_ordmade
group by id, weeks_diff
) as ordmade 
on coh_nos.id = ordmade.id;

getting this error:

ERROR 1146 (42S02) at line 29: Table 'test.coh_nos' doesn't exist

First clause select query runs NP but adding 2nd throws the error.

 select * from coh_nos;

also throwing same does not exist error.

Would some kind soul take pity and tell me why please?

Thanks

edited Nov 11 '18 at 2:26

asked Nov 10 '18 at 18:38

Morb

Dont use column number based ORDER BY and GROUP BY. Always prefer to use explicit column/alias names

– Madhur Bhaiya
Nov 10 '18 at 18:41

Ok - would you mind suggesting how it should be written with that in mind?

– Morb
Nov 10 '18 at 21:20

eg: for the first select it should be: order by coh_week, id

– Madhur Bhaiya
Nov 10 '18 at 21:21

Got it! Thanks, appreciate the best practise style advice but still throws an error with changes made.

– Morb
Nov 10 '18 at 23:04

add a comment |

I'm trying to build a cohort analysis in mySQL and have run into the below issue. I'm running the code in an online compiler (https://paiza.io).

So far I have created sups and ords table and the below start of query:

select coh_nos.coh_week, coh_nos.id as coh_items
from
(select 
 WEEK(sup_timestamp) AS coh_week,
 user_id AS id
 from sups as S 
order by coh_week, id
) as coh_nos

join 

(select ordmade.id, ordmade.weeks_diff as ordmade_items
from
 (select 
 user_id as id, 
 FLOOR(DATEDIFF(ord_timestamp, 
 coh_nos.coh_week)/7 ) as weeks_diff
 from ords as O 
 ) as in_ordmade
group by id, weeks_diff
) as ordmade 
on coh_nos.id = ordmade.id;

getting this error:

ERROR 1146 (42S02) at line 29: Table 'test.coh_nos' doesn't exist

First clause select query runs NP but adding 2nd throws the error.

 select * from coh_nos;

also throwing same does not exist error.

Would some kind soul take pity and tell me why please?

Thanks

edited Nov 11 '18 at 2:26

asked Nov 10 '18 at 18:38

Morb

Dont use column number based ORDER BY and GROUP BY. Always prefer to use explicit column/alias names

– Madhur Bhaiya
Nov 10 '18 at 18:41

Ok - would you mind suggesting how it should be written with that in mind?

– Morb
Nov 10 '18 at 21:20

eg: for the first select it should be: order by coh_week, id

– Madhur Bhaiya
Nov 10 '18 at 21:21

Got it! Thanks, appreciate the best practise style advice but still throws an error with changes made.

– Morb
Nov 10 '18 at 23:04

add a comment |

I'm trying to build a cohort analysis in mySQL and have run into the below issue. I'm running the code in an online compiler (https://paiza.io).

So far I have created sups and ords table and the below start of query:

select coh_nos.coh_week, coh_nos.id as coh_items
from
(select 
 WEEK(sup_timestamp) AS coh_week,
 user_id AS id
 from sups as S 
order by coh_week, id
) as coh_nos

join 

(select ordmade.id, ordmade.weeks_diff as ordmade_items
from
 (select 
 user_id as id, 
 FLOOR(DATEDIFF(ord_timestamp, 
 coh_nos.coh_week)/7 ) as weeks_diff
 from ords as O 
 ) as in_ordmade
group by id, weeks_diff
) as ordmade 
on coh_nos.id = ordmade.id;

getting this error:

ERROR 1146 (42S02) at line 29: Table 'test.coh_nos' doesn't exist

First clause select query runs NP but adding 2nd throws the error.

 select * from coh_nos;

also throwing same does not exist error.

Would some kind soul take pity and tell me why please?

Thanks

edited Nov 11 '18 at 2:26

asked Nov 10 '18 at 18:38

Morb

I'm trying to build a cohort analysis in mySQL and have run into the below issue. I'm running the code in an online compiler (https://paiza.io).

So far I have created sups and ords table and the below start of query:

select coh_nos.coh_week, coh_nos.id as coh_items
from
(select 
 WEEK(sup_timestamp) AS coh_week,
 user_id AS id
 from sups as S 
order by coh_week, id
) as coh_nos

join 

(select ordmade.id, ordmade.weeks_diff as ordmade_items
from
 (select 
 user_id as id, 
 FLOOR(DATEDIFF(ord_timestamp, 
 coh_nos.coh_week)/7 ) as weeks_diff
 from ords as O 
 ) as in_ordmade
group by id, weeks_diff
) as ordmade 
on coh_nos.id = ordmade.id;

getting this error:

ERROR 1146 (42S02) at line 29: Table 'test.coh_nos' doesn't exist

First clause select query runs NP but adding 2nd throws the error.

 select * from coh_nos;

also throwing same does not exist error.

Would some kind soul take pity and tell me why please?

Thanks

mysql aliasing

edited Nov 11 '18 at 2:26

asked Nov 10 '18 at 18:38

Morb

edited Nov 11 '18 at 2:26

asked Nov 10 '18 at 18:38

Morb

edited Nov 11 '18 at 2:26

asked Nov 10 '18 at 18:38

Morb

asked Nov 10 '18 at 18:38

Morb

asked Nov 10 '18 at 18:38

Morb

Dont use column number based ORDER BY and GROUP BY. Always prefer to use explicit column/alias names

– Madhur Bhaiya
Nov 10 '18 at 18:41

Ok - would you mind suggesting how it should be written with that in mind?

– Morb
Nov 10 '18 at 21:20

eg: for the first select it should be: order by coh_week, id

– Madhur Bhaiya
Nov 10 '18 at 21:21

Got it! Thanks, appreciate the best practise style advice but still throws an error with changes made.

– Morb
Nov 10 '18 at 23:04

add a comment |

Dont use column number based ORDER BY and GROUP BY. Always prefer to use explicit column/alias names

– Madhur Bhaiya
Nov 10 '18 at 18:41

Ok - would you mind suggesting how it should be written with that in mind?

– Morb
Nov 10 '18 at 21:20

eg: for the first select it should be: order by coh_week, id

– Madhur Bhaiya
Nov 10 '18 at 21:21

Got it! Thanks, appreciate the best practise style advice but still throws an error with changes made.

– Morb
Nov 10 '18 at 23:04

Dont use column number based ORDER BY and GROUP BY. Always prefer to use explicit column/alias names

– Madhur Bhaiya
Nov 10 '18 at 18:41

Ok - would you mind suggesting how it should be written with that in mind?

– Morb
Nov 10 '18 at 21:20

eg: for the first select it should be: order by coh_week, id

– Madhur Bhaiya
Nov 10 '18 at 21:21

Got it! Thanks, appreciate the best practise style advice but still throws an error with changes made.

– Morb
Nov 10 '18 at 23:04

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53242208%2fderived-table-doesnt-exist-mysql-error-1146-42s02%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Dfyjkt