sql access query join multiple table

I only have a basic understanding of all of those things and I am not sure what kind of join I need, however I do know the results I want to have.

It is a coldfusion app using the sql to query an Access database.

What I want to do is when a user logs into the system, based on his user name, show him a list of all CM#'s that is specifically for him (because his email is on the notification list).

I have 3 tables [Notify,Main,Login]
They say a picture is worth a thousand words, so:

Click to see table pic here

**Notification List in Main table is the same as Email in Notify table.


Here is what I have so far..

?--and then somehow results is unique Main.CmNumber--?

OR maybe it should be something like


And something else is missing so that Main.CmNumber is returned at the end.


Let me know if anyone needs clarification

Thanks

This really has nothing to do with ColdFusion. You should post on one of the SQL forums if you need help with this stuff.
You need to join your tables on a common field (has to be same data type). You may want to read into relationships:

SELECT l.FullName, n.FullName, m.CMNumber, m.NotificationList
FROM Login l INNER JOIN Main m ON m.MainToLogin=l.MainToLogin INNER JOIN Notify n ON l.FullName=n.FullName
WHERE n.Email = m.NotificationList

The little letters are aliases for tables. The first criteria was unneccessary because of the second inner join.

Solved!!

But now the question is how to specify one more condition on the sql query.

Where is this SQL forum that you mentioned so I can post the question there?
I'm not using mysql and I don't think it is MS sql either (is it?).

This has to do with the SQL inside coldfusion pages that will access an MS access database.

Then it's really an Access question. CF does nothing but send the SQL code you write to the database...it's the database that does all the SQL processing. You could try one of the database forums, or possibly search around for one specifically for Access.

There isn't an Access forum so I'd put it in the MS SQL forum, that's the closest match and they're more likely to be sympathetic (being MS users and all...)