In my database I have a “Person” table and a “Picture” table. The relationship between the tables is that “a person can have 0-3 pictures”. The Picture table also has a column indicating whether or not the user wants to use this pic in their profile. One of the rules is that the person can only use 1 of their pictures (again the max is 3 uploaded pics) in their profile.

Just so I can reference names of tables and columns in my question, let's say the database looks like this (it is a mySQL database by the way – version 14.7, distrib 4.1.7)...

TABLE: Person
PK: PersonId
PersonName

TABLE: Picture
PK: PictureId
FK: PersonId
PictureURL
PicIsSelectedForProfileYN


That being said, I now have to construct a query in Hibernate that displays all people in the Person table once and ONLY once but I also need data from the Picture table as well for display in the view. Another rule is that, if column PicIsSelectedForProfileYN is set to true then THAT is the Picture I need to return. I believe there are 3 cases I need to account for and I feel a little overwhelmed trying to write a query that will take care of them all. If necessary, I can write several queries but, even that doesn't seem easy. Below are the 3 cases I can think of:

1)Person exists in the Person table but there's no matching PersonId in the Picture table.
2)There's a matching PersonId in the Picture table but column “PicIsSelectedForProfileYN” is set to true
3)There's a matching PersonId in the Picture table but column “PicIsSelectedForProfileYN” is set to false

Since I'm very unsure of the Hibernate syntax I tried to think of how I would solve this using T-SQL. I know I need an outer join to return me everyone from the Person table regardless of whether or not they exist in the Picture table.

select per.PersonName, pic.PictureURL
from person as per
left outer join picture as pic
on per.PersonId = pic.PersonId

That's a start but it returns me duplicate people (0-3 of them depending on how many pictures they have uploaded). It would seem like I could do a union of several different queries or maybe a “unique” on the person. But as I think about how to do these I feel like I just need someone w/ more expertise to attack this problem (from both the “understanding SQL enough to write the correct T-SQL to solve the problem” level *AND* the Hibernate level). Any help would be GREATLY appreciated.

this can be done several ways...I'll try and describe one
assuming that you define a Person class containing a set of Picture objects...

for person with no matching pictures...
just run
select Person as p
then check if p.getPictures is empty set.

to get the person with their profile picture
select Person as p inner join fetch p.pictures as pic where pic.picIsSelectedForProfileYN=:picSelected

Set parameter picSelected to true, before running query.

if don't want the profile pictures...
set parameter picSelected to false before running query.