...

View Full Version : Select Distinct not picking unique records



swatisonee
11-26-2006, 04:37 PM
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


$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


$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));

nikkiH
11-26-2006, 05:29 PM
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.

swatisonee
11-27-2006, 01:46 AM
hi,

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




SELECT DISTINCT `AID`, `CID` FROM `Custaddress` GROUP BY `AID`

nikkiH
11-27-2006, 05:24 AM
SELECT DISTINCT `AID`, `CID`, `Street`, `Zip`, `Phone` FROM `Custaddress`


What does that return for you?

swatisonee
11-29-2006, 03:11 PM
Hi Nikkih,

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

S

nikkiH
11-29-2006, 03:14 PM
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.

guelphdad
11-29-2006, 07:36 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum