May 7, 2009 at 9:05 am
I am trying to select the email of everyone in Member who has an unread message in the table Messages
i've tried this:
SELECT Email FROM Member, Messages WHERE IsRead = 0
but it gives me a bigger list than it actually is. Could anyone give me a query that could work for this?
May 7, 2009 at 9:15 am
select email from Member usr
inner join Messages msg
on usr.ID = msg.UserID
where msg.IsRead = 0
Ok, so I'm presuming that there is a UserID column in the member table and that it has a corresponding foreign key in the Messages table.
You're basically doing a cross join where you are getting ALL the records from Member and ALL of the records from Messages instead of joining on the specific columns where you can limit your records.
May 7, 2009 at 9:18 am
The two tables dont actually have any corresponding columns which is why its making this so hard.
atleast as far as i can tell there arent any.
Nevermind, I got it working. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply