Page 2 of 3
Posted: Wed Aug 15, 2007 4:56 pm
by superdezign
VladSun wrote:there would be multiple rows and I'd wanted just one - in this case GROUP BY gives only one result, but using it in *this* query is worng according to SQL standrards, isn't it?
Well, actually reading the query this time

, I would think using GROUP BY in that manner would return only one result per estates.id from the JOINed estate_pictures table, but it would always choose the one with the lowest / earliest primary key. Using GROUP BY would basically give you what DISTINCT could give you with one table. I'm not too sure about the way DISTINCT works with multiple tables though, but in theory, GROUP BY should do exactly that.
Posted: Wed Aug 15, 2007 5:56 pm
by VladSun
1. As far as I know GROUP BY is to be used *only* with aggregate functions ...
2. From
http://dev.mysql.com/doc/refman/5.0/en/ ... rrors.html
Incorrect number of columns from subquery:
ERROR 1241 (ER_OPERAND_COL)
SQLSTATE = 21000
Message = "Operand should contain 1 column(s)"
This error occurs in cases like this:
SELECT (SELECT column1, column2 FROM t2) FROM t1;
You may use a subquery that returns multiple columns, if the purpose is comparison. In other contexts, the subquery must be a scalar operand. See Section 12.2.8.5, “Row Subqueries”.
So ... subselect in return/select area is permitted in case it returns a scalar value!
PS: Another one from MySQL official site:
#
Incorrectly used table in subquery:
Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"
This error occurs in cases such as the following:
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target.
Posted: Wed Aug 15, 2007 6:15 pm
by superdezign
VladSun wrote:1. As far as I know GROUP BY is to be used *only* with aggregate functions ...
Except in MySQL?
VladSun wrote:2. From
http://dev.mysql.com/doc/refman/5.0/en/ ... rrors.html
Incorrect number of columns from subquery:
ERROR 1241 (ER_OPERAND_COL)
SQLSTATE = 21000
Message = "Operand should contain 1 column(s)"
This error occurs in cases like this:
SELECT (SELECT column1, column2 FROM t2) FROM t1;
You may use a subquery that returns multiple columns, if the purpose is comparison. In other contexts, the subquery must be a scalar operand. See Section 12.2.8.5, “Row Subqueries”.
So ... subselect in return/select area is permitted in case it returns a scalar value!
PS: Another one from MySQL official site:
#
Incorrectly used table in subquery:
Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"
This error occurs in cases such as the following:
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target.
What was the error from the second query you posted? Was it from using the data from the outside of the subquery inside of it, possibly? As far as I know, subqueries are evaluated first.
Posted: Wed Aug 15, 2007 6:19 pm
by VladSun
I have no errors

The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions:
... You may use a subquery that returns multiple columns, if the purpose is comparison. In other contexts, the subquery must be a scalar operand. See Section 12.2.8.5, “Row Subqueries”...
... You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target. ...
Actually, the second query in my first post is working exactly as one could expect. No errors, no multiple rows, etc.
Posted: Wed Aug 15, 2007 6:26 pm
by VladSun
superdezign wrote:VladSun wrote:1. As far as I know GROUP BY is to be used *only* with aggregate functions ...
Except in MySQL?
Yeah ... this is a kind of "this works for me" ... You could never know whether it will work with other (higher) versions of MySQL ...
Posted: Wed Aug 15, 2007 6:37 pm
by superdezign
VladSun wrote:I have no errors

The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions:
... You may use a subquery that returns multiple columns, if the purpose is comparison. In other contexts, the subquery must be a scalar operand. See Section 12.2.8.5, “Row Subqueries”...
... You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target. ...
Yeah, when I said it I was assuming that CoderGoblin was correct. I actually use subqueries in SELECT statements in my own code. I've done a lot with subqueries just to see what they are capable of and they can do most anything, but a lot of the more 'advanced' things are hack-ish. So, I naturally assumed that this might have been one of those times. Hehe.
Posted: Wed Aug 15, 2007 7:42 pm
by VladSun
Thanks for participating in this argue - another important manual has been read

Posted: Wed Aug 15, 2007 10:09 pm
by VladSun
superdezign wrote:Using GROUP BY would basically give you what DISTINCT could give you with one table. I'm not too sure about the way DISTINCT works with multiple tables though, but in theory, GROUP BY should do exactly that.
I missed that one
Using DISTINCT is pointless in this query - all of my estate_pictures records have distinct name field. So every row in this query will be unique. As a result I'll have multiple rows for each estates.id due to LEFT JOINing tables. I.e. with or without DISTINCT the result remains still the same.
PS: I wonder whether there is a way (a MySQL keyword/modifier) to have DISTINCT which is not per row but per coloumn. So I could be able to build such query:
Code: Select all
select distinct coloumn1, coloumn2, distinct coloumn3 from table
and the result should be: all records with unique
coloumn1 AND unique
coloumn3 without any care of the values of
coloumn2...
Posted: Wed Aug 15, 2007 10:28 pm
by AKA Panama Jack
You might want to read up on how SQL standard JOINs work.
http://www.w3schools.com/sql/sql_join.asp
and group by
http://www.w3schools.com/sql/sql_groupby.asp
Your first example is a valid query and will usually return a value even if that value is 0 matching record sets.
Posted: Wed Aug 15, 2007 10:31 pm
by VladSun
What should I look for there?
PS:
GROUP BY... was added to SQL because aggregate functions
I have no aggregate functions in my first query .... that is the point ...
Posted: Wed Aug 15, 2007 10:37 pm
by AKA Panama Jack
Exactly what a LEFT JOIN will return when used with two tables. And since you are grouping by estate.id you are probably not going to get what you think you should be getting.
Posted: Wed Aug 15, 2007 10:47 pm
by VladSun
AKA Panama Jack wrote:Exactly what a LEFT JOIN will return when used with two tables.
Can't understand it - why LEFT JOIN should behave like an aggregate function - I've searched really, really hard and couldn't find an example where there are only LEFT JOIN and GROUP BY without any aggregate function - no luck!
AKA Panama Jack wrote:And since you are grouping by estate.id you are probably not going to get what you think you should be getting.
Well, surprisingly to me, it does do what I want it to do.
PS: And for N-th time - PostgreSQL would never accept this query - try it, see it and try to explain it.
Posted: Thu Aug 16, 2007 12:50 am
by AKA Panama Jack
Because Postgres doesn't accept it doesn't mean it isn't a valid SQL query.

Even Postgres isn't 100% SQL compatible.
Posted: Thu Aug 16, 2007 3:29 am
by CoderGoblin
superdezign wrote:I'm not sure how Postgre handles all of the things that MySQL does, but I've grown used to the way MySQL works.
Postgres (up until the last major version of Mysql) had/has more inbuilt functionality than MySQL. So it should be phrased the other way around. Unfortunately my knowledge is with Postgres rather than MySQL so I know how that works.

Posted: Thu Aug 16, 2007 3:32 am
by CoderGoblin
VladSun wrote:
The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions:
Well if that is the case I apologise.

I answer from my experience and normally have no problems getting things working. Is there any advantage to putting a select in the Return area ? If not, for readability, I would maintain the SELECT structure initially talked about.