showing data not in another table

Box

14y1.7kSQL

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Box14y ago
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 !=)
0
Pradip Pandey14y ago
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.UserName,E.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.
0
Box14y ago
I've attached the tables here. Is this enough information?
0
Pradip Pandey14y ago
Can you provide some data of all the tables?
0
Box14y ago
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.
0
Pradip Pandey14y ago
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.
0