SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inner Join with sub select query

    Hi Guys,

    I am struggling with this sub select query within a join anyone got any ideas?

    All I want to do is get a count from a joined table

    Code:
    SELECT area FROM areas a
    INNER JOIN (SELECT COUNT(*) AS villa_count FROM villas GROUP BY area)v ON a.id = villas.area
    ORDER BY a.area ASC
    The error mysql keeps giving me is

    Code:
    #1054 - Unknown column 'villas.area' in 'on clause'
    Any help appreciated

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      area,
      COUNT(villas.area) AS `villa_count`
    FROM
      areas
    LEFT OUTER JOIN
      villas
    ON
      villas.area = areas.area
    GROUP BY areas.area
    ORDER BY areas.area

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wicked dude, thanks for your help sorted.

    have a good w/e


Bookmarks

Posting Permissions

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