determin if there is a record or not

I have two tables that i want to retireve data from. The first table contains information like (message, subject, datesent, touserid, sentuserid)

The second table contians the default picture of the person who sent the message.

In some cases the person who sent the message will not have a record in the "defaultpic table"

The SQL goes as follows:

<cfquery name="messages" datasource="#application.datasource#">
SELECT USER_MAIL.MAILID, USER_MAIL.TOUSERID, USER_MAIL.TOGROUPID, USER_MAIL.SENTUSERID, USER_MAIL.DATE_TIME, USER_MAIL.SUBJECT, USER_MAIL.MESSAGES, USER_DEFAULTPIC.PICTUREURL, USER_DEFAULTPIC.GROUPID
FROM USER_MAIL, USER_DEFAULTPIC
WHERE USER_MAIL.TOUSERID = '#client.userid#' AND USER_MAIL.SENTUSERID = USER_DEFAULTPIC.GROUPID 
</cfquery>

Code (markup):

The problem I'm having: if there is no record in the defaultpic table then this SQL statment will not output the message from the message table.

How can I check to see if the user who sent the message has a defaultpic or not. And if they dont then use a <cfif> statement to use a different image

I'm not even sure if this makes sense to me?

Thanks for your help!

 

Greetings,

You would need to do something like this..

<cfset myPic = ''>

<cfif IsDefined("messages.PICTUREURL") AND #messages.PICTUREURL# NEQ "">

<cfset myPic = '#messages.PICTUREURL#'>

<cfelse>

<cfset myPic = 'somePhotoURL.jpg'>

</cfif>

Have fun!

Sincerely,
Travis Walters

 

Although It dosent create any errors, it still dosent work.

What happens is that it will look at the defaultpic table and see if there is a record for the "sentuserid". But for whatever reason it thinks that no one has a record for a defaultpic in the defaultpic table. So it just sets "mypic" = "somePhotoURL.jpg"

But on the other hand it will display all the messages from the message table. With out the defaultpic, and just the "somePhotoURL.jpg" in its place.

If I figure it out I'll let you know.

Thank you for your help.

 

You could use a LEFT JOIN to return all messages, even if there is no matching record in the defaultpic table. Then use COALESCE() to return a different value if the default image is NULL.

SELECT
 
USER_MAIL.MAILID, 
USER_MAIL.TOUSERID, 
USER_MAIL.TOGROUPID, 
USER_MAIL.SENTUSERID, 
USER_MAIL.DATE_TIME, 
USER_MAIL.SUBJECT, 
USER_MAIL.MESSAGES, 
COALESCE(USER_DEFAULTPIC.PICTUREURL, 'someOtherPicture.jpg') AS PictureURL
USER_DEFAULTPIC.GROUPID

FROM 
        USER_MAIL LEFT JOIN USER_DEFAULTPIC
        ON USER_MAIL.SENTUSERID = USER_DEFAULTPIC.GROUPID 

WHERE 
           USER_MAIL.TOUSERID = '#client.userid#' 

Code (markup):

 

This works exactly like it should.

You have put an end to a two week long mission.

Thank you very much for your help, once again you have opened another door for me in my quest to develope United Local Bands.com

 

You're welcome. Its also nice to read about and hear how your site is progressing