Professional Web Applications Themes

Select distinct - MySQL

Hi Folks Here is my SQL code: SELECT * FROM Company INNER JOIN `Properties` ON `Company`.`Company_ID`=`Properties`.`Company_ID` WHERE `Properties`.`Property` LIKE '%$clean_search%' ORDER BY Country ASC What I want this to return is a list of all fields: Company & Country from the table Company. However its returning the same company multiple times when there are more than 1 Properties.Property LIKE $clean_search. What I want but I'm not actually sure if it can be done is a statement like: SELECT DISTINCT Company, * Can this be done? Thanks A...

  1. #1

    Default Select distinct

    Hi Folks

    Here is my SQL code:

    SELECT *
    FROM Company
    INNER JOIN `Properties`
    ON `Company`.`Company_ID`=`Properties`.`Company_ID`
    WHERE `Properties`.`Property` LIKE '%$clean_search%'
    ORDER BY Country
    ASC

    What I want this to return is a list of all fields: Company & Country
    from the table Company. However its returning the same company multiple
    times when there are more than 1 Properties.Property LIKE
    $clean_search.

    What I want but I'm not actually sure if it can be done is a statement
    like:

    SELECT DISTINCT Company, *

    Can this be done?

    Thanks

    A

    UKuser Guest

  2. #2

    Default Re: Select distinct


    UKuser wrote:
     

    Try:

    SELECT DISTINCT `Company`.*
    FROM `Company`
    INNER JOIN `Properties` USING(`Company_ID`)
    WHERE `Properties`.`Property` LIKE '%$clean_search%'
    ORDER BY Country ASC

    Captain Guest

  3. #3

    Default Select distinct

    Hi Folks,

    Is there a way to select one field distinctly but still return all the
    others?

    i.e.

    Table
    Name Address Tel
    Test 123 Str 23456
    Test 56 Str 21233
    Hello 67 Town 232323

    SELECT DISTINCT Name
    But return in the query Address & Telephone as well

    Not quite sure how this can be done.

    Thanks

    A

    UKuser Guest

  4. #4

    Default Re: Select distinct

    Hi again,

    I have found the solution & hope you find this helpful

    GROUP BY Name HAVING COUNT(DISTINCT Name)=1

    Will fix the example problem.

    A

    UKuser wrote: 

    UKuser Guest

  5. #5

    Default select distinct

    Hi,

    I'm trying to select from users table the idusers and concatonated
    fname with lname as name, but I want the name to be unique (distinct).
    The idusers are unique but the combination of fname and lname are not.
    I don't want the duplicates to be displayed.

    My query:

    $query="select u.idusers, concat(fname,\" \",lname) as name
    from users u
    left join projects p
    on u.idorganisations=p.idorganisations
    where u.idusers!=197 and u.lname!=''
    and ( p.idprojects=".$idProjects." or u.idorganisations=0 )
    order by lname";

    At this moment I'm getting back duplicate name records.
    Can anyone help?

    Cheers,
    Natalia

    natalalaa@googlemail.com Guest

  6. #6

    Default Re: select distinct

    On 7 Jun, 12:35, com wrote: 

    If you don't want all the values of idusers, why are you bothering to
    select it?

    Captain Guest

  7. #7

    Default Re: select distinct

    On Jun 7, 1:45 pm, Captain Paralytic <com> wrote: 





    >
    > If you don't want all the values of idusers, why are you bothering to
    > select it?[/ref]

    I need them for the drop down box that this select is gathering
    information for.

    natalalaa@googlemail.com Guest

  8. #8

    Default Re: select distinct

    On 7 Jun, 13:50, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > I need them for the drop down box that this select is gathering
    > information for.- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    But if you don't have all the names, you won't have all the idusers
    for the drop down box???

    Anyway, you can use GROUP BY `name` to remove the duplicates.

    Captain Guest

  9. #9

    Default Re: select distinct

    On Jun 7, 1:56 pm, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > But if you don't have all the names, you won't have all the idusers
    > for the drop down box???
    >
    > Anyway, you can use GROUP BY `name` to remove the duplicates.[/ref]

    No, I don't need all idusers. I need anly one iduser for a name
    (doesn't matter which iduser for a particular name it will be).

    I tried to put group by with distinct in the end of the query but I'm
    getting an sql syntax error.
    Could you write me how exactly it should look like?

    natalalaa@googlemail.com Guest

  10. #10

    Default Re: select distinct

    On 7 Jun, 14:05, com wrote:

    | Could you write me how exactly it should look like

    I did, I wrote:

    GROUP BY `name`

    Captain Guest

  11. #11

    Default Re: select distinct

    On Jun 7, 2:59 pm, Captain Paralytic <com> wrote: 

    Sweet! :)

    I was trying to put both order by and group by together in one
    statement and it was complaining, but just group by solved the
    problem.
    Thanks a lot!

    natalalaa@googlemail.com Guest

  12. #12

    Default Re: select distinct

    On 7 Jun, 15:12, com wrote: 



    >
    > Sweet! :)
    >
    > I was trying to put both order by and group by together in one
    > statement and it was complaining, but just group by solved the
    > problem.
    > Thanks a lot![/ref]

    Glad to be of service.

    Captain Guest

Similar Threads

  1. Using SELECT DISTINCT
    By The Ox in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 16th, 10:18 PM
  2. Select Distinct Statement Help
    By Ralph Freshour in forum PHP Development
    Replies: 5
    Last Post: August 20th, 07:27 AM
  3. Select Distinct
    By Scott in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 3rd, 05:22 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139