Getting only top row from left table

Getting all records from right table, but only first from the left one?

[sgietz]

Hello,

This is somewhat perplexing to me, but I'm not that smart, so this is not uncommon :)

For the sake of simplicity I'm breaking this down to the meat of it. I basically have two tables. One holds articles with various other information, such as the headline, category, date etc. The second table holds image names that are associated with the articles in the first table. They are linked via a unique article id.

Some articles may have one picture, several pictures, or no pictures at all. I want to get all articles with or without images, thus I'm using a LEFT JOIN. Here's my problem. I would like to get all articles, and IF there are any images, I want ONLY the first one, and that is where I'm stuck. Currently I get all articles, but if there is more than one image, the article gets duplicated with the second image. Using DISTINCT doesn't work either. Below is the stripped down statement:

SELECT myid, myheadline, mydate, myimage
FROM mytable
LEFT JOIN myothertable
ON mytable.myid = myothertable.myid

This works great if only picture is in the second table. How can I make this work?

Thanks ;)

[syber]

try this:

SELECT myid, myheadline, mydate, myimage  
FROM mytable  
 LEFT JOIN myothertable  
 ON mytable.myid = myothertable.myid 
WHERE myothertable.myid IS NULL  
 OR myothertable.myid = (SELECT MIN(myothertable.myid)  
             FROM myothertable 
             WHERE myothertable.myid = mytable.myid) 

[sgietz]

Thanks, but that soesn't work, bcause the "myid" appears as many times in the second table as there are images. Just a quick example:

Table 1

myid ¦ headline ¦ date
----------------------------
1 ¦ Some Text ¦ some date
2 ¦ Some Text ¦ some date
3 ¦ Some Text ¦ some date

Table 2

myid ¦ image name
-------------------
1 ¦ foo.jpg
1 ¦ bar.jpg
3 ¦ twizzler.gif

The result set looks like this:

myid ¦ headline ¦ date ¦ image name
-----------------------------------
1 ¦ Some Text ¦ some date ¦ foo.jpg 
1 ¦ Some Text ¦ some date ¦ bar.jpg 
3 ¦ Some Text ¦ some date ¦ twizzler.jpg

:\

[syber]

ok, use myimage instead:

(SELECT MIN(myimage)
FROM myothertable
WHERE myothertable.myid = mytable.myid)

[sgietz]

Yeah, I finally figured it out.

Thanks ;)

[sgietz]

Yikes ... spoke too soon. I no longer get duplicates, but now I only get articles for which there are images in the second table.

To use my example table above, the second row won't show up.

*scratches head*

[syber]

the line below is supposed to take care of that:

WHERE myothertable.myid IS NULL

[sgietz]

Works now. Something in the WHERE clause messed it up.

Thanks for the help! I can now knock this on out before the day is over.