Join

[The Little Guy]

I can not get this to work... please help.

 

SELECT *, users.id as linkto, friends_images.`default` as `default`,
friends_comments.id as `cid`
FROM friends_comments,users,friends_images WHERE
friends_comments.posterID = users.id
AND
friends_comments.posterID = friends_images.ownerID
AND
friends_comments.ownerID = '$id'
ORDER BY friends_comments.id DESC

 

This seems to hard for me to explain, but...

 

I need to display the users name/displayname, the picture, and the comment.

posterID must match users.id

posterID must match friends_images.ownerID

$id = a number of the current viewing profile.

 

What is happening, when I test it, it displays the comment 2 times, since when it does its SQL, it finds that my id has 2 images saved in the database. So.. it will display the comment 2 times.

 

I have a column called default, this is either 1 or 0

1 = default image

0 = non default image.

 

The SQL is getting both default and non-default images.

it should return 1 or more comments, with one image. if there is no image, then it should return NULL or something like so.

[The Little Guy]

If in the WHERE caluse, I add

 

IFNULL(friends_images.default = '1', friends_images.default = '0')

 

OR

 

(friends_images.default = '1' OR friends_images.default = '0')

 

OR

 

friends_images.default = '1'

 

None of them work.

 

the 3rd one will only show the comment if the user has a set default image, and not everyone has a set default image.

 

I don't know what the first two are doing though, they just don't display anything.

[The Little Guy]

This is almost the same thing, it is used for image comments, to comment on one image, it work 100%

 

it post 1 post know matter how many images you have, or if you have a default image or not.

 

SELECT *, users.id as linkto, friends_images.`default` as `default`,
friends_image_comments.id as `cid`
FROM friends_image_comments,users,friends_images WHERE 
friends_image_comments.posterID = users.id
AND 
friends_image_comments.imageID = friends_images.id
AND 
friends_image_comments.imageID = '$id'
ORDER BY friends_image_comments.id DESC

[The Little Guy]

Anyone?????

[fenway]

First, that would be easier to read if it was a join... second, I'm not sure what you actually want it to do.

[teng84]

First, that would be easier to read if it was a join... second, I'm not sure what you actually want it to do.

 

yes i guess it would be better if you show the sample output you want

[clearstatcache]

ryt....i would prefer  join...