Page 1 of 1

Sub Selects

Posted: Mon Nov 08, 2004 9:30 am
by Etherguy
Boys and Squirrels,

I am having trouble getting the desired results from my sub select query :cry:

I am trying to output the user name and the total counts from each category using the following query

Code: Select all

select distinct a.name,a.count1,b.count2 from gifts, (select count(g.gid) as count1,r.name from gifts g,recipients r where g.uid=1 and g.category=2 and g.rid = r.rid group by r.name)a, (select count(g.gid) as count2,r.name from gifts g,recipients r where g.uid=1 and g.category=1 and g.rid = r.rid group by r.name)b group by a.name
The main problem I am seeing, is that it is not outputing the proper counts for the same id from the two queries.. it kinda of mixes them...

Any suggestions?

Regards,

Posted: Mon Nov 08, 2004 9:37 am
by Weirdan
please post your db schema and expected results.

Posted: Mon Nov 08, 2004 9:52 am
by Etherguy
Sorry, I should know better :oops:

Here is the DB Schema

Code: Select all

TABLE : GIFTS

gid       int(5)    PRI     auto_increment        
uid       int(3)                                                                
gname     varchar(100)                                             
gcost     decimal(4,2)                                                          
pdate     date                                                                  
rid       int(3)                                                                
pos       varchar(100)                                             
category  int(1)

TABLE : RECIPIENTS

rid     int(3)       PRI   auto_increment           
uid     int(3)                
name    varchar(50)  UNIQUE
My expected output is as follows....

Let's say I have two recipients Jack(rid=1) and Jill(rid=2)

Let get a whole load of gifts seperated into two categories (1,2)

What I want to do is outout the totals of each category for each recipients

Alla :

Code: Select all

Name                   Category 1 Total       Category 2 Total

Jack                              5                                 1
Jill                                 2                                 7
You follow?

Posted: Mon Nov 08, 2004 10:11 am
by Weirdan
untested:

Code: Select all

select 
    r.name, 
    sum(g.category=1) as `Category 1 Total`,  
    sum(g.category=2) as `Category 2 Total`
from
    recipients r
left join  /* change to inner join if you don't want to diplay recipients without gifts */
    gifts g
using(rid) /* I suppose tables are linked by rid column */
where
    r.rid in(1,2) /* Jack and Jill, you can use names as well */
group by r.name /* It's safe to group by name as it have UNIQUE key over itself */

Posted: Mon Nov 08, 2004 10:21 am
by timvw
in the http://adodb.sf.net package there is a file pivottable.inc file which generates the queries you need :)

Posted: Mon Nov 08, 2004 10:28 am
by Weirdan
timvw wrote:in the http://adodb.sf.net package there is a file pivottable.inc file which generates the queries you need :)
That's great, but is a total overkill. In my opinion, of course. :?

Posted: Mon Nov 08, 2004 10:33 am
by Etherguy
Thanks Weirdan ...

A little tinkering and some php variables and it did just what I was looking for!