Possible JOIN question 1

[blekfis]

I've read tutorials and forums, but I just dont get it or it's not possible to do what I want...

I've got two tables (a bit cut here):
albums (aid, visibility)
pictures (pid, aid, name, title) // pid increases by 1 for each new row

I'd like to select the latest addition from pictures, but if aid for that picture in table albums has a visibility set to 1 next row should be checked.

Unclear enough...?


SAMPLE:
albums
1, 0
2, 1

pictures
11, 2, pic1.gif, Pic1
10, 1, pic2.gif, Pic2

In this little sample it would jump Pic1 with highest pid (11) because albums.aid visibility is 1. Instead it would chose Pic2

Maybe a bit clearer

So is it possible to do in one go, or would I need to make two querys?

 

[r937]

what you said: if visibility=1 then skip that pic

if it were me, i would use visibility=0 to mean "album is not visible" and visibility=1 to mean "album is visible"

but that's just me ;-)

select p.pid
     , p.aid
     , p.name
     , p.title
  from pictures as p
inner
  join albums as a
    on a.aid = p.aid
   and a.visibility <> 1
order
    by p.pid desc limit 1

r937.com | rudy.ca

 

[blekfis]

I'll try this later and also try to understand how it works now that I have a piece that I can relate to.

Visibility = 0 for users, =1 for admins only

 

[blekfis]

Worked perfect! Thanx!!