 | |
#1
 
| |||
| |||
|
Re: SQL query -
08-02-2003
, 04:03 PM
|
robertzeller21 (AT) aol (DOT) com (Robert Zeller 21) wrote in message news:<20030802091838.13267.00000805 (AT) mb-m10 (DOT) aol.com>... How on earth is it possible to execute a query that joins 4 tables and uses 'GROUP BY' with aggregate functions? How the hell do you do this if the SELECT statement cannot specify items that are not in the GROUP BY statement??!!! TIA peeps What do you mean? Could you provide the ddl for the 4 tables you mention, and what you would like your query to return. Meanwhile, the following query joins four tables and uses an aggregate function: select a.m, b.n, count(1) from a, b, c, d where a.x = b.x and b.y = c.y and c.z = d.z group by a.m, b.n |
specified in the from clause? Nothing special?
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#2
| |||
| |||
|
|
genew (AT) mail (DOT) ocis.net (Gene Wirchenko) wrote in message news:<3f2c1682.19339969 (AT) news (DOT) ocis.net>... [...] So what does it mean when no fields are selected from a table specified in the from clause? Nothing special? Not that I can think off. Anything special that you had in mind? |
referred to somewhere in the statement, but I had a nagging doubt that
maybe, the results are undefined. (I am not an SQL expert.)
Sincerelky,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#3
| |||
| |||
|
|
lennart (AT) kommunicera (DOT) umea.se (Lennart Jonsson) wrote: genew (AT) mail (DOT) ocis.net (Gene Wirchenko) wrote in message news:<3f2c1682.19339969 (AT) news (DOT) ocis.net>... |
Quote:
|
So what does it mean when no fields are selected from a table specified in the from clause? Nothing special? Not that I can think off. Anything special that you had in mind? Well, I suppose it would mean that such tables have columns being referred to somewhere in the statement, but I had a nagging doubt that maybe, the results are undefined. (I am not an SQL expert.) |
following rather silly example:
create table countries (
countrycode char(2) not null primary key,
countryname varchar(50) not null
);
insert into countries values ('se', 'Sweden'), ('no', 'Norway');
create table persons (
name char(10) not null primary key,
country char(2) not null,
foreign key (country) references countries
);
insert into persons values ('Lennart', 'se'), ('Hagbard', 'no');
create table items (
owner char(10) not null,
name char(10) not null,
primary key (owner, name),
foreign key (owner) references persons
);
insert into items values ('Lennart', 'Car'), ('Lennart', 'Boat')
-- then list persons from Sweden, and the number of items they own,
-- ignoring people without items
select
p.name, count(1)
from
countries c, persons p, items i
where
i.owner = p.name and
p.country = c.countrycode and
c.countryname = 'Sweden'
group by
p.name
I have a feeling that the original poster is wondering about something
else ,but without more details it is hard to tell
Regards
/Lennart
[...]
#4
| |||
| |||
|
|
genew (AT) mail (DOT) ocis.net (Gene Wirchenko) wrote in message news:<3f2de010.14943956 (AT) news (DOT) ocis.net>... lennart (AT) kommunicera (DOT) umea.se (Lennart Jonsson) wrote: genew (AT) mail (DOT) ocis.net (Gene Wirchenko) wrote in message news:<3f2c1682.19339969 (AT) news (DOT) ocis.net>... [...] So what does it mean when no fields are selected from a table specified in the from clause? Nothing special? Not that I can think off. Anything special that you had in mind? Well, I suppose it would mean that such tables have columns being referred to somewhere in the statement, but I had a nagging doubt that maybe, the results are undefined. (I am not an SQL expert.) There is nothing strange (normally) with such a query. Concider the following rather silly example: |
I am glad to know that it does make sense. Thanks.
[snip]
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.