result of COUNT as join condition

dennispg · #1 (**permalink**) 03-03-04, 17:48

is it possible to use as a join condition the result of a function like COUNT?

for example:

Code:

select
  a.key
from
  a left join b on b.key = count(a.key)
group by
  a.key;

or

Code:

select
  count(a.key) as C
from
  a left join b on b.key = C
group by
  a.key;

is it even possible to reference a column from the select clause in the join conditional?

example:

Code:

select 5 as Five a left join b on b.key = Five;

what im trying to do: i have a table of rates per person in a room... which rate to use for a person depends on the total number of people in the room which i get by using count. im assuming i could accomplish this with subqueries.. but i dont have the latest version of mysql on my host.

thanks for any help!

r937 · #2 (**permalink**) 03-03-04, 18:01

what you want is most likely possible, except all your a's and b's and c's are too confusing, and your people and rates and rooms are pretty nebulous as far as knowing which columns are in which tables

dennispg · #3 (**permalink**) 03-03-04, 22:43

Code:

Rates
---------------------------
| RCount	| Rate	|
---------------------------
| 1	| 250	|
| 2	| 175	|
| 3	| 150	|
---------------------------

People
----------------------------------------------------
| personID	| FName	| LName	| RoomID	|
----------------------------------------------------
| 1	| Jack	| Brown	| 1	|
| 2	| John	| Doe	| 1	|
| 3	| Jane	| Doe	| 2	|
| 4	| Phil	| Brown	| 1	|
----------------------------------------------------

Rooms
---------------------------
| RoomID	| RName	|
---------------------------
| 1	| Room1	|
| 2	| Room2	|
---------------------------


Resulting Query I want:
------------------------------------------------------------------------------
| personID	| Name		| RName	| RCount	| Rate	|
------------------------------------------------------------------------------
| 1	| Jack Brown	| Room1	| 3	| 150	|
| 2	| John Doe		| Room1	| 3	| 150	|
| 3	| Jane Doe		| Room2	| 1	| 250	|
| 4	| Phil Brown	| Room1	| 3	| 150	|
------------------------------------------------------------------------------


Without the rates, this is the query i have currently:

select
	people.personID,
	people.FName,
	people.LName,
	rooms.RName,
	count(people.personID) as RCount
from
	people
	left join rooms using(roomID)
	left join people as roommates using(roomID)
group by
	people.personID
;

r937 · #4 (**permalink**) 03-04-04, 03:20

unfortunately i can't think of a way to do it without temp tables

to count the number of people in the room, you must GROUP BY the room

PHP Code:


			
create table roomcount

select rooms.roomID

     , count(people.personID) as RCount

  from people

inner

  join rooms 

    on people.roomID = rooms.roomID

group 

    by rooms.roomID

    

select personID

     , Name

     , RName

     , RCount

     , Rate    

  from people

inner

  join roomcount

    on people.roomID = roomcount.roomID

inner

  join Rates

    on roomcount.RCount = Rates.RCount

dennispg · #5 (**permalink**) 03-04-04, 14:44

thanks! i think i will use temp tables after all.

just for the sake of my education/curiousity...

is it possible to do it using subqueries if i did have mysql4? what would it look like?

r937 · #6 (**permalink**) 03-04-04, 19:15

yes, just take the first query and "nest" it inside the second as a derived table

Code:

select personID
     , Name
     , RName
     , RCount
     , Rate    
  from people
inner
  join (
       select rooms.roomID
            , count(people.personID) as RCount
         from people
       inner
         join rooms 
           on people.roomID = rooms.roomID
       group 
           by rooms.roomID 
       ) as roomcount
    on people.roomID = roomcount.roomID
inner
  join Rates
    on roomcount.RCount = Rates.RCount

Internet Services

Web Development

Digital Marketing

Consumer Tech