SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Postcode search sql problem

    Hi Guys!

    I've developed a postcode search that is used to find candidates in our database. Now, a user can have multiple postcodes associated with them (stored in the users_locations) table. My problem is that when we search for a postcode sometimes the user doesn't come up even though they exist with that postcode associated with them.

    For example, a user may have the following 3 postcodes associated with them:

    CM5
    CM6
    CM14

    When I search for CM5 and CM6 the user is found. However if I search for CM14 it doesn't get found. Does anyone have an idea why? Is it a GROUP BY problem maybe?

    Here is the SQL

    Code:
    SELECT users . * , l.display_name, l.postcode, l.display_county
    FROM users
    INNER JOIN (
    
    SELECT user_id, if( count( user_id ) >3, 'Locations throughout the UK', group_concat( display_name
    SEPARATOR ' ' ) ) AS display_name, postcode, display_county, location_id
    FROM users_locations
    GROUP BY user_id
    ) AS l ON l.user_id = users.id
    WHERE users.status = '1'
    AND users.cv_hide = '0'
    ORDER BY cv_date DESC
    LIMIT 0 , 30

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    looks like a user can have multiple rows in the users_locations table, but you are collapsing them all down to one aggregate row per user_id

    if you do a GROUP_CONCAT on the display_name, then you should also do that on the other columns in the SELECT clause

    i'll bet you find CM14 appearing, as if by magic...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    looks like a user can have multiple rows in the users_locations table, but you are collapsing them all down to one aggregate row per user_id

    if you do a GROUP_CONCAT on the display_name, then you should also do that on the other columns in the SELECT clause

    i'll bet you find CM14 appearing, as if by magic...
    Hi,

    So you mean like this?

    Code:
    SELECT users. * , l.display_name, l.postcode, l.display_county
    FROM users
    INNER JOIN (
    SELECT user_id, if( count( user_id ) >3, 'Locations throughout the UK', group_concat( display_name
    SEPARATOR ' ' ) ) AS display_name, group_concat( postcode
    SEPARATOR ' ' ) AS postcode, display_county, location_id
    FROM users_locations
    GROUP BY user_id
    LIMIT 0 , 30
    I just ran the above code but it gave a syntax error.

  4. #4
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I managed to figure it out. Here's the final SQL:

    Code:
    SELECT users . * , l.display_name, l.postcode, l.display_county
    FROM users
    INNER JOIN (
    
    SELECT user_id, if( count( user_id ) >3, 'Locations throughout the UK', group_concat( display_name
    SEPARATOR ' ' ) ) AS display_name, group_concat( postcode
    SEPARATOR ' ' ) AS postcode, display_county, location_id
    FROM users_locations
    GROUP BY user_id
    ) AS l ON l.user_id = users.id
    WHERE users.status = '1'
    AND users.cv_hide = '0'
    AND (
    l.postcode LIKE '%CM14%'
    )
    ORDER BY cv_date DESC
    LIMIT 0 , 30

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs View Post
    I just ran the above code but it gave a syntax error.
    oh

    Quote Originally Posted by Zaggs View Post
    I think I managed to figure it out. Here's the final SQL:
    and you're happy with it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •