MySQL Join Query Help

Sayian

I am trying to Construct a Query to get the Photo Filename from a Table that store's My User's photo filenames.

So far, my query Looks like this.

$sql = mysql_query("SELECT shocc_users.city,shocc_account_photos.photo_filename
FROM shocc_users
LEFT JOIN shocc_account_photos 
ON shocc_users.username = shocc_account_photos.username
WHERE
main = 1
");

Now this Would return all the user's city's from my user table, and the filename of their main picture from shocc_account_photo's For each user.

Because i have constructed a Left Table join, if no account photofilename was found then the city is still returned.

Not So.

In my Shocc_account_photo's table, Users can upload upto 13 pics ... So 1 user could have 13 entry's in that table for photo filenames. Only 1 can be the Main photo tho, and this is marked by main = 1
Main being a Colum in shocc_account_photos

Now In our Query above .. We said WHERE main=1 so any user's who dont have a entry in shocc_account_photos for a main pic .. Will be discarded.

Any user who does not have a Main Picture, it wont even return the city back, it Doesnt find a main=1 as needed in the WHERE clause, so it skips over the row.

I dont want that .. If a user doesnt have a picture, that's fine, i still need the information returned for their city .. And as it is right now, any user's that dont have a main picture , might as well not even be users.

How can i fix this to return the photo_filename WHERE main = 1 , but if it cant find a entry for a user where main = 1 it will still return information about the city?

coreyp_1

DISCLAIMER:

I was not able to test this, so it may not even work.

SELECT shocc_users.city, IFNULL(mainfile, otherfile) AS photo_filename
FROM shocc_users
LEFT JOIN (SELECT username, photo_filename AS mainfile
  FROM shocc_account_photos
  WHERE main=1)
  USING username
LEFT JOIN (SELECT username, MIN(photo_filename) AS otherfile
  FROM shocc_account_photos
  GROUP BY username)
  USING username

I'm trying this with subqueries (you do have mysql 4.1+, right?)

Lars_Berg

$sql = mysql_query("SELECT shocc_users.city,shocc_account_photos.photo_filename
FROM shocc_users
LEFT JOIN shocc_account_photos
ON shocc_users.username = shocc_account_photos.username
and main = 1
");

Sayian

Im sorry, but neither one of those code example work for what im trying to do.

Yes i do have mysql 4.1 +

Ive tried everthing i know and im stuck.

Again, what im trying to do is return the user's main photo if they have one WHERE main=1, but also return the rest of the results for that user, even if they dont have a main photo

Any other advice?

The current code i have now is

 $sql = "SELECT shocc_users.city, shocc_users.state, shocc_users.status, shocc_users.username,
shocc_users.rating,shocc_users.age,shocc_users.signup_time,shocc_users.ethnicity,shocc_users.gender,shocc_users.starsign,
shocc_account_photos.photo_filename
FROM shocc_users
LEFT JOIN shocc_account_photos
ON shocc_users.username = shocc_account_photos.username
WHERE main = 1
ORDER BY shocc_users.signup_time DESC
LIMIT 0,25
";

But .. WHERE main = 1 will only return user's with pictures.

I need to return user's even if they DONT have a WHERE main = 1

Lars_Berg

There is no where clause in the query I posted.

Sayian

And the query you posted doesnt work.

Lars_Berg

Could you give some sample data and the expected result and describe how my query does not work in relation to that.

bt23

When you join tables there is no such thing as "main" anymore, you need to referance it as <table>.main or alias the table name and alias.main

This problem is quiet common (the "null field" one, not what I listed above, that is just something easily noticable also) because people don't really understand how the parser handles joins; however, what you want to do IS achievable, and it's not terribly hard to do but many just don't get how to structure the query to get it to go that way because by default that's not the nature of how the join is supposed to act.

As Lars said, providing the table structure and a few rows of sample entries (ones with main photos, ones without) can get you the query a lot faster than letting people "guess" random queries. I'll write you a working query when I have the structures+data. I'm too lazy to do it manually and run the queries locally =]

Sayian

Thanks for your reply's

Let me provide some table data.

In my user's table the structure is setup like

shocc_users

username
city
state
zipcode
age
email

shocc_account_photos

photo_id
username
upload_time
main
photo_filename

shocc_active

active_id
username
active_time

So let's say id like to perform a query that get's the user's city, state, and zipcode along with the main photo of user's who are Online at the time. ( the main photo in the user's table is identified with a 1 in the main column .. if it's a sub photo( not a main photo) then main will equal 0

SELECT shocc_users.username,shocc_users.city,shocc_users.state,
shocc_account_photos.photo_filename,shocc_active.active_id
FROM shocc_users
LEFT JOIN shocc_account_photos ON shocc_users.username = shocc_account_photos.username
LEFT JOIN shocc_active ON shocc_users.username = shocc_active.username
WHERE main = 1

And that's what's getting me in trouble ... Im trying to select the users main photo by getting the photo_filename from shocc_account_photos, but if it doesnt find a main photo (where main = 1) .. For instance, let's say they didnt have a main photo, and only sub photos or better yet, no photos, then it wouldnt select the user .. b/c it doesnt find a match WHERE main = 1

I understand what your telling me about main is not good to use in this case, but im new to table join's and am still trying to work all this out.

I hope that help's you, to help me.

Ive been working on this for over a week .. Thanks for your help.