Avatar of davideo7
davideo7Flag for United States of America asked on

MySQL: How can I return true if a field matches an entry in another table?

This is going to be hard to explain but I have a while loops which loops through results.  The results are of threads that I have on my forum as seen here:
http://www.vizzed.com/vizzedboard/forum.php?id=5

What I want to do is while retrieving the threads from the DB, I want the query to also compare each thread with another table called 'auto_mark_forums_read' which basically checks to see if a user has read that thread and if they haven't, display a New icon.

Below in my code you'll see the query I have which retrieves all of the threads from the DB.
SELECT t.*,u1.name AS name1,u1.sex AS sex1,u1.powerlevel AS power1,u2.name AS name2,u2.sex AS sex2,u2.powerlevel AS power2 FROM threads t,users u1,users u2 WHERE forum=$id AND u1.id=t.user AND u2.id=t.lastposter $subforumtext ORDER BY sticky DESC,lastpostdate DESC

Open in new window

PHPMySQL ServerSQL

Avatar of undefined
Last Comment
davideo7

8/22/2022 - Mon
SOLUTION
AngryBinary

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
davideo7

Not sure if that made much sense.  Here are the 2 fields from the auto_mark_threads table
forumID       user

ASKER CERTIFIED SOLUTION
AngryBinary

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
davideo7

Ok I know we're getting close.  I'm sorry for the info I didn't give you, I assumed I'd be able to figure out most of it on my own but I was wrong.

Anyways, here's the query, I've made modifications to it.  I also want to point out that they thread would only be marked as read if the lastpostdate of the thread is mor erecent than the date of the auto_mark_threads table, which gets updated each time a user goes to a thread.  Code still doesn't work but it's probably close, it's currently retrieving all the threads but it's showing them all as marked unread.
SELECT t.*,u1.name AS name1,u1.sex AS sex1,u1.powerlevel AS power1,u2.name AS name2,u2.sex AS sex2,u2.powerlevel AS power2, (a.date > t.lastpostdate) AS isRead

FROM threads t
inner join users u1 on u1.id = t.user
inner join users u2 on u2.id = t.lastposter
left join auto_mark_threads a on (a.forumID = t.id and a.user = 1)

WHERE forum=5

ORDER BY sticky DESC,lastpostdate DESC

LIMIT 0,50

Open in new window

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
davideo7

Good catch, it's suppose to be a.threadID

Well that fixed the problem.  Thanks for all your help, it would've taken me many hours to figure this out on my own, you saved me probably a full day worth of work :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
davideo7

Thanks so much