Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Aug 2005
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Distinct not picking unique records

    Hi,

    I have a table with the foll. structure :
    CAID(int, primary,autoincrement)
    AID (int)
    CID(int)
    Street, Zip, Phone (all varchar)

    Now, each CID (company name) can have multiple AID( caddress location). And each AID can be linked to multiple CIDs.

    So Company A can have an office in Location A and Location B.
    Company B which is a subsidiary of Company A can also have offices in Location A and Location B ie they can share the same premises.

    Now the table unfortunately has duplicate records so there will be 2-3 entries of Company A with Location A as an address location etc.

    I tried to list only unique records such as:

    Company A Location A Street Zip Phone
    Company A Location B " " "
    Company B Location A " " "
    Company B Location B " " "

    But the code below gives me CID and AID but not the Street, Zip and Phone data - those columns remain blank.
    If i use

    PHP Code:
    $sqla "SELECT DISTINCT * FROM `Custaddress`  "
    only, it lists all the data including the duplicates. I also tried using GROUP BY `AID` but its didnt work. Why is this please ? Any suggestions on how i could change it ?

    Thanks.
    S

    PHP Code:
    $sqla "SELECT DISTINCT `AID`, `CID`  FROM `Custaddress`  ";

    $resulta mysql_query($sqla) or die (mysql_error ());

    do {

        
    printf("<tr>
    <td height=30 width=269  bordercolor=#99CCFF><font size=2 face=Tahoma color=#E9CF61><b>%s</td>
    AND SO ON....
         </tr>"
    ,
     
    $myrowa["CAID"],
     
    $myrowa["AID"],
     
    $myrowa["CID"],
     
    $myrowa["Street"],
     
    $myrowa["Zip"],
     
    $myrowa["Phone);

      } while ($myrowa = mysql_fetch_array($resulta)); 

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Because With select *, you are getting the identity field CAID. Which is always unique.

    You have to select each field yourself.
    i.e.
    select distinct AID, CID, Street, City, zip from `Custaddress`

    Hope this helps.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Regular Coder
    Join Date
    Aug 2005
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi,

    i did try the foll. but that didnt work either . Any suggestions?

    PHP Code:

    SELECT DISTINCT 
    `AID`, `CIDFROM `CustaddressGROUP BY `AID

  • #4
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Code:
    SELECT DISTINCT `AID`, `CID`, `Street`, `Zip`, `Phone` FROM `Custaddress`
    What does that return for you?

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #5
    Regular Coder
    Join Date
    Aug 2005
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Nikkih,

    That worked ! Thank you !! But why did i have to choose all the fields ??

    S

  • #6
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Because with select *, you are getting the identity field CAID. Which is always unique. Even if you are not using it in the PHP, the database selected it if you said "select *". So, you WERE getting distinct records, just not the ones you meant to ask for.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    don't use distinct and group by together, they are atonomous.

    distinct works across every column in your select so if two of the three values are different but the third is the same both rows would be returned.

    in the case of duplicates of information in a column how do you decide which other columns to return?

    for instance
    uid firstname lastname age
    5 James Brown 50
    6 James Brown 45

    leaving out the uid column, which age would you return for James Brown? you will have to decide similar for your data.

    also note you should be asking database type questions in the mysql or general database threads as appropriate.


  •  

    Posting Permissions

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