August 14, 2006 at 2:05 pm
I need anyones help quering some tables. I have a report process that runs and emails users report files.
I need a datatable of the files with the emails that they will be sent to.
There are 3 tables:
1) Files (id, filename)
2) Email_Lookup (id, emailaddress)
3) Mapping (Files.id, and Email_Lookup.id)
Thanks in advance for help
Ryan
August 15, 2006 at 7:17 am
Can you give us more details? I'm not 100% on what you are looking to accomplish.
August 15, 2006 at 7:46 am
Are you looking for something like this?
CREATE TABLE Files (id INT, filename VARCHAR(255))
CREATE TABLE Email_Lookup (id INT, emailaddress VARCHAR(255))
CREATE TABLE Mapping (Files_id INT, Email_Lookup_id INT)
INSERT INTO Files VALUES(1, 'test 1')
INSERT INTO Files VALUES(2, 'test 2')
INSERT INTO Files VALUES(3, 'test 3')
INSERT INTO Files VALUES(4, 'test 4')
INSERT INTO Email_Lookup VALUES(1, 'me@aol.com')
INSERT INTO Email_Lookup VALUES(2, 'you@aol.com')
INSERT INTO Email_Lookup VALUES(3, 'everyone@aol.com')
INSERT INTO Mapping VALUES (1, 1)
INSERT INTO Mapping VALUES (1, 2)
INSERT INTO Mapping VALUES (3, 2)
INSERT INTO Mapping VALUES (2, 3)
SELECT f.filename,
e.emailaddress
FROM Files f,
Email_Lookup e,
Mapping m
WHERE m.Files_id = f.id
AND m.Email_Lookup_id = e.id
ORDER BY emailaddress
/* Results
test 2 everyone@aol.com
test 1 me@aol.com
test 1 you@aol.com
test 3 you@aol.com
*/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply