Catch-22: Any way out?

meehand

Here is my dilemna:

I would like to perform a query that pulls up distinct rows. For example, I would like to find only distinct rows where a users first name is "Michael". However, along with this information, I would like to pull up the user_id, which happens to be the primary key of the table. The problem is that once I include user_id in the query, the rows all become unique and I lose the ability to find distinct rows with the first name "Michael".

What I would like to do is find distinct rows with the first name Michael, and then find the user_id for only those rows. Any ideas on how to do this? Do I use a join?

Thanks for any help.

--Doug

adywarna

If the user_id and first_name is in the same table, you don't have to do any join.

your sql should just look like this, assuming first_name is a field in your table:

select user_id from tableName where first_name='Michael'

That should do it. If you have different tables, then a join might be needed.

meehand

Thanks for the response, but I think I didn't explained myself clearly enough.

Let me try another example:

Say I have a table that hold three columns: user_id, email, and zip_code. The column user_id is the primary key and is thus auto incremented.

Also assume that I have several rows in the table where email and zip code are identical.

Now, say I want to run a query where email and zip code are distinct. This could be done easily enough with SELECT DISTINCT email, zip code FROM tablename

But say I also want to find the user_id of those distinct rows. If I say SELECT DISTINCT * from tablename, it will return several rows that have the same email and zip code because including user_id ensures that each row will always be distinct.

So what I want to do is SELECT DISTINCT email, zip code FROM tablename, and then somehow find the user_id for only those returned rows.

I hope that makes sense.

Thanks.

adywarna

Hi,

It's clearer to me now. Sorry.

What I would do in that case is do 2 queries:

$sql = "select distinct zipcode from tablename";
$result = mysql_query($sql);
$rows = mysql_num_rows($result);
for($x=0;$x<$rows;$x++){ // outer loop
  $data = mysql_fetch_object($result);
  $sql2 = "select user_id from tablename where zipcode=$data->zipcode";
  $result2 = mysql_query($sql2);
  $rows2 = mysql_num_rows($result2);
  for($y=0;$y<$rows2;$y++){ // inner loop
    $data2 = mysql_fetch_object($result2);
    //now you have your user_id in $data2->user_id and do your processing here.
  }
}

Well, just a suggestion... I'm also learning. Maybe someone can give a better suggestion.

Lars_Berg

The query does not make any sense

Assuming your table has the following data

id | Name
1 | Michael
2 | Michael

Which id value do you want and why?

chriskl

This is how you do it:

SELECT DISTINCT ON (email, zip) * FROM tablename;

Chris