Обсуждение: count() and multiple tables
I want to select all the entries from d that have at least one corresponding entry in u that meets my conditions. The problem is that count(*) is returning the number of corresponding entries in u, and I want only the number of entries in d. How do I do this? create table d( id int primary key, status int default 1 ); create table a( key int primary key, status int default 1 ); create table u( dkey int not null,akey int not null,b bool DEFAULT false, status int default 1,primary key (dkey,akey) ); insert into d values (1, 2); insert into a values (1, 3); insert into a values (2, 3); insert into a values (3, 3); insert into u values(1,1,false,2); insert into u values(1,2,false,1); insert into u values(1,3,false,2); select count(*) from d where status = 2 and d.id = u.dkey and u.status = 2 and not u.b and u.akey = a.key and a.status = 3; /* that returns 2 when I want it to return 1 */ drop table d; drop table a; drop table u; -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Joseph, SImple as pie (e.g., easy on your 100th one): > select count(*) from d where status = 2 and d.id = u.dkey and > u.status = > 2 and not u.b and u.akey = a.key and a.status = 3; Count(*) will always count the number of records in the largest table in your join. If you want the number of records in a specific table, then you have to make sure that that table is the only on in your FROM clause, and reference all other tables in WHERE: select count(*) from d where id IN (select u.dkey from u, a where u.akey = a.key AND u.status = 2 and not u.b and a.status = 3); -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Stephan Szabo wrote: > > On Mon, 19 Mar 2001, Joseph Shraibman wrote: > > > I want to select all the entries from d that have at least one > > corresponding entry in u that meets my conditions. The problem is that > > count(*) is returning the number of corresponding entries in u, and I > > want only the number of entries in d. How do I do this? > > > > > > create table d( > > id int primary key, > > status int default 1 > > ); > > > > create table a( > > key int primary key, > > status int default 1 > > ); > > > > create table u( > > dkey int not null, > > akey int not null, > > b bool DEFAULT false, > > status int default 1, > > primary key (dkey, akey) > > ); > > > > insert into d values (1, 2); > > > > insert into a values (1, 3); > > insert into a values (2, 3); > > insert into a values (3, 3); > > > > insert into u values(1,1,false,2); > > insert into u values(1,2,false,1); > > insert into u values(1,3,false,2); > > > > select count(*) from d where status = 2 and d.id = u.dkey and u.status = > > 2 and not u.b and u.akey = a.key and a.status = 3; > > And postgres tries to be helpful again... :( [I *really* dislike this > adding to from list thing] Technically the above should be illegal > because no from list contains u or a. Postgres is adding them to the > from list for you. > I get the same result if I do: select count(d.id) from d where status = 2 and d.id = u.dkey and u.status = 2 and not u.b and u.akey = a.key and a.status = 3; So in standard SQL all the tables you join accross are required to be in the FROM? > I think you want something like (untested): > select count(*) from d where status=2 and > exists ( > select * from u, a where u.dkey=d.id and u.status=2 and > no u.b and u.akey=a.key and a.status=3 > ); That works, but I thought there might be a better way because it looks like that will get all the data out of the table and throw it away right after. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
On Mon, 19 Mar 2001, Joseph Shraibman wrote: > I want to select all the entries from d that have at least one > corresponding entry in u that meets my conditions. The problem is that > count(*) is returning the number of corresponding entries in u, and I > want only the number of entries in d. How do I do this? > > > create table d( > id int primary key, > status int default 1 > ); > > create table a( > key int primary key, > status int default 1 > ); > > create table u( > dkey int not null, > akey int not null, > b bool DEFAULT false, > status int default 1, > primary key (dkey, akey) > ); > > insert into d values (1, 2); > > insert into a values (1, 3); > insert into a values (2, 3); > insert into a values (3, 3); > > insert into u values(1,1,false,2); > insert into u values(1,2,false,1); > insert into u values(1,3,false,2); > > select count(*) from d where status = 2 and d.id = u.dkey and u.status = > 2 and not u.b and u.akey = a.key and a.status = 3; And postgres tries to be helpful again... :( [I *really* dislike this adding to from list thing] Technically the above should be illegal because no from list contains u or a. Postgres is adding them to the from list for you. I think you want something like (untested): select count(*) from d where status=2 and exists (select * from u, a where u.dkey=d.id and u.status=2 and no u.b and u.akey=a.key and a.status=3 );
> > And postgres tries to be helpful again... :( [I *really* dislike this > > adding to from list thing] Technically the above should be illegal > > because no from list contains u or a. Postgres is adding them to the > > from list for you. > > > I get the same result if I do: > select count(d.id) from d where status = 2 and d.id = u.dkey and > u.status = 2 and not u.b and u.akey = a.key and a.status = 3; > > So in standard SQL all the tables you join accross are required to be in > the FROM? Basically, yes. It's more complicated than that probably (what isn't in SQL), but that's the general idea. Postgres assumes your query is select count(*) from d,u,a ... Because d.id was guaranteed to be unique, you might be able to count(distinct d.id) and get the result you want. [I think the subquery is a nicer way of representing it]
Stephan, Joseph, > Because d.id was guaranteed to be unique, you might be able to > count(distinct d.id) and get the result you want. [I think the > subquery is a nicer way of representing it] Plus a several SQL implementations don't implement the DISTINCT until after the COUNT, reuslting in a still-inflated (and sometimes varying!) COUNT. It won't work properly in T-SQL or MS Office SQL, for example. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco