Relationship Between Tables

ldtiw

I have two tables.

One with profile information and the other with images.
Both have a username field to put the image with a profile and visversa.

I can do a repeate region and print out all the images but i cant figure out how to display selected info from the profile table

Does anyone have any ideas?

Thanks,
Danny

ldtiw

Ok Heres what i came across in a book:

SELECT user_profile.username, images.user, images.id, images.FILE, user_profile.Name, user_profile.Sex, user_profile.Age, user_profile.Country, user_profile.whythink, images.showimage, user_profile.active
FROM user_profile, images
WHERE user_profile.username = images.user

This is very messy i know but its nearly doing what i need.

For testing i have two profiles in the table, and 4 images. 3 are with one, and one another.

With this statement im getting the information pulled i need, but with one small problem. Its displaying all images and users.

So im getting three images displayed for the one user when i need it to be only one!

i just need one image from each user but only if showimage is set to Y and the active for the profile is set to Y.

Bit messy at describing that one, but im still learning!!

Thanks,
Danny

Ps Using Dreamweaver for this!

Pig

First a note about your schema. It is correct to have a seperate table for your images, but you should have the table keys be (ie: the table id) be the joining relationship, not the user name. Even if the user name is unique, it could possibly change, and you would have to update 2 tables to make the change effective (which is obviously not proper design). Use the autoincrement integer for the table, not the user name.

Anyway, to answer your question... If I understand correctly, you have an on/off field in the images table called showimage. If so, try simply adding a filter to your WHERE clause.

WHERE user_profile.username = images.user AND images.showimage = 'Y'

Roger_Ramjet

Try this:

[SELECT user_profile.userid, user_profile.username, user_profile.Name, user_profile.Sex, user_profile.Age, user_profile.Country, user_profile.whythink, user_profile.active, images.FILE, images.showimage,
FROM user_profile INNER JOIN images ON user_profile.userid = images.userid
WHERE user_profile.active = TRUE AND images.showimage = TRUE

You will have to add the userid field to your images table as the foreign key to user_profile, as Pig has said. Be aware, the INNER JOIN will limit your results to those users that have an image. If you want all users, even those that don't yet have an image then use a LEFT JOIN.