SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inner join - distinct rows

    Hi everyone,

    please have a look at the following query:

    Code:
    $q = "SELECT c.city_name, f.fruit_name, t.tree_name, fl.flower_name
    FROM users AS u INNER JOIN
    city AS c ON c.city_id = u.city_id INNER JOIN
    fruits AS f ON f.fruit_id = u.fruit_id INNER JOIN
    trees AS t ON t.tree_id = u.tree_id INNER JOIN
    flowers AS fl ON fl.flower_id = u.flower_id 
    WHERE c.city_name = 'LA' ";
    This query would return something like:

    apples
    apples
    apples
    kiwis
    kiwis
    roses
    roses
    oak
    oak
    oak

    What I want is only one instance of every fruit, flower and tree:

    apples
    kiwis
    roses
    oak

    I probably can loop all fruits, flowers and trees into separate arrays and then chuck out duplicate rows but can I do this with SQL only? While I figure this out perhaps some kind soul can advise me on this.

    Thank you!

  2. #2
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    317
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Use the DISTINCT keyword in front of the column that you want to retrieve.

    Code:
    $q = "SELECT DISTINCT c.city_name, DISTINCT f.fruit_name, DISTINCT t.tree_name, DISTINCT fl.flower_name
    FROM users AS u INNER JOIN
    city AS c ON c.city_id = u.city_id INNER JOIN
    fruits AS f ON f.fruit_id = u.fruit_id INNER JOIN
    trees AS t ON t.tree_id = u.tree_id INNER JOIN
    flowers AS fl ON fl.flower_id = u.flower_id 
    WHERE c.city_name = 'LA' ";

  3. #3
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Patche,

    but that didn't work. I get an error:

    mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

  4. #4
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    317
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Can you show the rest of your code leading up the mysql_num_rows() call ?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    I get an error:
    your query failed

    always test queries outside of php first, that way you'll get the actual mysql error message

    in your case it's because you cannot repeat the DISTINCT keyword -- it is allowed only once, and it applies to all columns in the SELECT clause

    so DISTINCT isn't even the solution you are looking for

    you need to examine the relationships between users and cities and fruits and trees and flowers

    unless each user can have at most only one city, only one fruit, only one flower, and only one tree, you are going to get these "duplicates" all over the place
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Patche, so it seems DISTINCT can only be used once, worth a try though.


    Thank Rudy

    unless each user can have at most only one city, only one fruit, only one flower, and only one tree, you are going to get these "duplicates" all over the place
    In my case every user can have only one city, fruit, flower and tree. What the query is returning isn't really "duplicates", I just need to find a way to get only one of each returned fruit, flower and tree. Perhaps a PHP array function will do the trick?
    Last edited by RedBishop; Mar 27, 2014 at 06:23. Reason: spelling mistake

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    here you go --
    Code:
    SELECT 'fruit:' || f.fruit_name AS name
      FROM city AS c 
    INNER
      JOIN users AS u 
        ON u.city_id = c.city_id 
    INNER 
      JOIN fruits AS f 
        ON f.fruit_id = u.fruit_id 
     WHERE c.city_name = 'LA'    
    UNION -- to remove dupes    
    SELECT 'tree:' || t.tree_name
      FROM city AS c 
    INNER
      JOIN users AS u 
        ON u.city_id = c.city_id 
    INNER 
      JOIN trees AS t 
        ON t.tree_id = u.tree_id 
     WHERE c.city_name = 'LA'        
    UNION -- to remove dupes    
    SELECT 'flower:' || fl.flower_name
      FROM city AS c 
    INNER
      JOIN users AS u 
        ON u.city_id = c.city_id 
    INNER 
      JOIN flowers AS fl 
        ON fl.flower_id = u.flower_id 
     WHERE c.city_name = 'LA'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much Rudy, I got the query working. I appreciate your help.

    May I ask what purpose the colon serves in the query? Also, does the first SELECT mean "select fruit OR f.fruit_name"?



    Thank you!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    May I ask what purpose the colon serves in the query?
    it separates the word which denotes the type of name from the name itself

    all three types have this format, so that you can separate the types


    Quote Originally Posted by RedBishop View Post
    Also, does the first SELECT mean "select fruit OR f.fruit_name"?
    no, it selects only the fruit name, appending the fruit type in front of it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All right, thank you for clarifying that.

    Cheers.


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
  •