Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Posts
    6

    Question Unanswered: result of COUNT as join condition

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Posts
    6
    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
    ;
    Last edited by dennispg; 03-03-04 at 23:50.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Posts
    6
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •