showing data not in another table

Question

Hi

I've done similar queries to this before but am struggling with this one now.

I'm writing this in c sharp but dont imagine that should change anything too much really

I have 4 tables: (with fields in brackets)

tblMsg: (MID, UserID, GID, MDate, MContent)
tblGRead: (MID, UserID)
tblUsers: (UserID, UserName)
tblGroupUsers: (GID, UserID)

tblMsg has the common field and other tables are linked to it.

a row of data is put into tblMsg

when this row is read the persons user id (UserID) and the message id (MID) is added to
the the table tblGRead

this part all works fine.

I just need a way of displaying the details from tblMsg if a persons UserID and MessageId combination are not found in the table tblGRead

I've done more basic version of this type of thing by using left joins and looking for a null value but can't figure out how to do it this way. or atleast not nicely!

any ideas?

Thanks in advance

Box · Answer

Cheers for helping I really appreciate this but its still not working for me :| I get the error: Missing operator in query expression a.mfrom b.guser left outer join tblusers As C ON A.MFrom = C.UserI (I replaced the != with as access sql uses for does not equal rather than !=)

Pradip Pandey · Answer

run this query to get all users except that whose has read the messages select distinct A.MFrom  C.UserName from tblmsg as A inner join tblGRead AS B on a.MFROM != b.GUser left outer join tblusers As C ON A.MFrom = C.UserID Result will look like this m172734 Mister_Hickey m194012 Scott Chegg m189697 James and this query will return that users name who belongs to the group select distinct A.MFrom  C.UserNameE.GName E.GOwner from tblmsg as A inner join tblGRead AS B on a.MFROM != b.GUser left outer join tblusers As C ON A.MFrom = C.UserID inner join tblGroupUsers As D on D.UserID = A.MFrom inner join tblGroups AS E on E.GID = D.GID Hope this will help you.

Box · Answer

I've attached the tables here. Is this enough information?

Pradip Pandey · Answer

Can you provide some data of all the tables?

Box · Answer

Hi Thanks for the reply but this doesnt work in many ways :( It shows multiple duplicated results. I threw in a distinct to get rid of these but the results themselves are still wrong. Its showing messages sent to other users in there. Also 2 tables have been omitted which I need data from. Its my fault for not providing more details in my first post. tblMsg contains all messaging data. an individual user id starts with an M. A group of people start with a number. ie user number 1 = M001 group number 1 = 1 The groupUsers table contains a list of users group id's and which user id's belong to them ie: GID UserID 1 M001 1 M002 2 M001 2 M004 The individual messages work fine (theres a read flag in the messages table i use) Its just this group one im having issues with.

Pradip Pandey · Answer

Hi Box select A.mid A.userid from tblmsg as A inner join tblgread AS B on a.mid != b.mid and a.userid != b.userid Hope it will help.