Sub Selects

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Sub Selects

Post 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,
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

please post your db schema and expected results.
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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 */
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

in the http://adodb.sf.net package there is a file pivottable.inc file which generates the queries you need :)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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. :?
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Post by Etherguy »

Thanks Weirdan ...

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