1

I have been stuck at this problem for almost a week and may get the pink slip if I can't solve it soon. As such, I would appreciate any assistance.

The 'column2 = table2.column2' seems to be the culprit.

  SELECT (SELECT column2
            FROM (SELECT column3,
                         column2
                    FROM table2 AS table2Alias
                   WHERE column2 = table2.column2
                GROUP BY column2 COLLATE utf8_bin, column3
                ORDER BY COUNT(*),
                         column2 COLLATE utf8_bin) AS t
         GROUP BY column3
         ORDER BY COUNT(*),
                  column2 COLLATE utf8_bin
            LIMIT 1) AS t2
    FROM table2
   WHERE column1 IS NULL
GROUP BY column2

EDIT: How can I move the nested nested subquery one or two levels outside so that it can access a column of the outermost table?

EDIT 2: The names of the columns have been changed for obvious reasons. I don't actually use 'file' or 'directory' in the actual code. The code has nothing to do with files or directories.

EDIT 3: I've changed the names to prevent confusion.

Yaskov
  • 73
  • 1
  • 1
  • 8
  • 1
    Possible duplicate of [Unknown Column In Where Clause](https://stackoverflow.com/questions/153598/unknown-column-in-where-clause) – Obsidian Age Oct 25 '18 at 02:23
  • 1
    It is not a duplicate of that question. The "Unknown Column In Where Clause" question refers to using an alias introduced in the [same] output clause. – user2864740 Oct 25 '18 at 02:30
  • 1
    What happens if you just run the subquery? Can you reduce the problem to a [mcve]? – Robert Columbia Oct 25 '18 at 02:31
  • what about explaining table(s) structure and use case what You want from that table(s). – num8er Oct 25 '18 at 02:33
  • I cannot understand Your query at all. So I guess You need file listing grouped by `directory`, `name`. If Yes so try this: `SELECT GROUP_CONCAT(id) AS ids, name, directory FROM file GROUP BY directory, name ORDER BY directory;` – num8er Oct 25 '18 at 02:43
  • Since you aliased `file AS file2`, try changing `file.name` to `file2.name`. Also: [`FILE` is a reserved keyword in MySQL](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-in-current-series). You will need to escape it with backticks, like `\`file\``. – dossy Oct 25 '18 at 02:52
  • @dossy I want to access name of table file at the outer table. That's why I use file.name. – Yaskov Oct 25 '18 at 02:55
  • @RobertColumbia It is not possible to run the subquery because the subquery needs a value from the outermost table. I can run the query provided I hardcode a dummy value for "file.name". – Yaskov Oct 25 '18 at 02:58
  • @num8er The names must be compared case-sensitively and non case-sensitively at different parts. – Yaskov Oct 25 '18 at 02:59
  • So, first you need to fix the query and place `\`` around the word `file` because `file` is a reserved keyword, and to use identifiers that are reserved keywords, you have to wrap them in `\``. Second, if you want to refer to the outer table, then alias the outer table to something unique and use that alias in your inner query. – dossy Oct 25 '18 at 03:05
  • @dossy I used 'file' as an example. I didn't realise it was a resered word. I've changed it to something else. As for the alias, I've done that but it doesn't work. – Yaskov Oct 25 '18 at 03:08
  • The query you are using in your question does NOT have the outer table aliased. You need to do that, because otherwise it's ambiguous - in the inner query, just because you aliased `table2` to `table2Alias` it doesn't remove the inner `table2` - that's still accessible, which means if you want to refer to the outer query's `table2`, you need to alias the outer query's `table2` and refer to the alias in your inner query. – dossy Oct 25 '18 at 03:29
  • @dossy I know what you mean and I've tried it. No luck. – Yaskov Oct 25 '18 at 03:39
  • Oh, because your inner query isn't a subquery, it's a column value in the outer query's `SELECT` clause - totally separate scope, basically. – dossy Oct 25 '18 at 03:48
  • @dossy Yup. Am I doomed? – Yaskov Oct 25 '18 at 03:52
  • Yeah, I don't think you can access the outer table from the inner subqueries. You'll need to return a column in your subquery's result set that you can join on in the outer query. – dossy Oct 25 '18 at 03:53
  • If you can better describe what it is you're actually trying to accomplish, I could probably help you write the correct query - as it is, I'm not even sure what `ORDER BY COUNT(*)` will even do and why you have a `GROUP BY` clause when the `SELECT` clause doesn't even have an aggregate function in it... – dossy Oct 25 '18 at 03:54
  • Okay, at this point you need to create a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve), including an actual schema and sample data set, so I can help you write an actual SQL query to do that. – dossy Oct 25 '18 at 04:09
  • Posting a broken query without telling what you are trying to do will not help us help you. First tell what you are trying to do. Second provide necessary information (table structure, relationships, columns). Third, post your broken query. Read this. https://stackoverflow.com/help/how-to-ask – Eric Oct 26 '18 at 19:09

2 Answers2

0

I would guess that WHERE column2 = table2.column2 is the problem there: table2.column is not defined at that stage.
You need to change the WHERE so that it will filter outside of the innermost SELECT

Fibo
  • 31
  • 7
0
I would suggest that you rebuikd your query. <br />

Currently the outermost select has no field, so presumably changing select (select to select * from( select might give some result...
but as far as I understand you built this query over time and most of its logic is now deeply hidden. For instance the clause "where column1 is null" could probably go into the innermost select...

Fibo
  • 31
  • 7