CODEWITHSUNDEEP
mysqlselectuniqueunique-key
David Poulsen
David Poulsen

Reputation: 51

MYSQL, Get only first rows with unique id from colum with multiple same value rows?

I have two tables user_photos and user_annons, I fetch from both using join.

In the user_annons each row is always with a unique id, the Id column is auto increment, and the randomkey colum is also unique.

But in the user_photos table there can be multiple rows with the same randomkey value, and the randomkey value is the same as in user_annons. user_photos table also have an id column that is auto increment.

My code looks like this:

$result = mysql_query("SELECT iname, title, pris 
                        FROM user_annons 
                        JOIN user_photos 
                        ON user_photos.randomkey = user_annons.randomkey 
                        AND user_annons.profile_id='".$profile_id."' 
                        AND user_photos.profile_id='".$profile_id."'");

while ($row2 = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "<a href=\"".$profile_id."/images/".$row2['iname']."\">
    <img src=\"".$profile_id."/images/".$row2['iname']."\"></a>";
    echo $row2['title'];
    echo $row2['pris'];
}

What I want is to fetch the only row with the unique randomkey value in the user_annons table, together with the first unique randomkey row in user_photos table.

Now the code displays a picture first and then the title and pris from user_annons, and then a different picture but the same title and pris from user_annons, because there is one row in user_annons but two rows in user_photos with the same randomkey value.

So is there anyone that can help me?

Upvotes: 0

Views: 1602

Answers (2)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT 
    ua.iname, 
    ua.title, 
    ua.pris,
    up.profile_id
FROM user_annons as ua
JOIN (select min(id) as up_id , randomkey , profile_id from  user_photos) as up ON up.randomkey = ua.randomkey 
AND ua.profile_id='".$profile_id."' 
AND up.profile_id='".$profile_id."'

Using MIN() will fetch the first matching row and if you use MAX it will fetch the last one

Upvotes: 0

sel
sel

Reputation: 4957

Use LIMIT 1

$result = mysql_query("SELECT iname, title, pris 
                        FROM user_annons 
                        JOIN user_photos 
                        ON user_photos.randomkey = user_annons.randomkey 
                        AND user_annons.profile_id='".$profile_id."' 
                        AND user_photos.profile_id='".$profile_id."' LIMIT 1");

Upvotes: 1

Related Questions