I am trying to do what seems as if it should be simple. At root, it's a
simple left outer join. All records from table 1, with data from matching
records in table 2. But I want to limit table 2. Here's the example:
DATABASES
DB_ID, DB_NAME, DB_TYPE
1,MyDatabase,1
2,YourDatabase,2
3,TheirDatabase,1
PASSWORDS
PWD_ID, PWD_DBID, PWD_USERID, PWD_TEXT
1,1,20,'jonah'
2,1,21,'apples'
3,1,35,'harrington'
4,2,20,'jesus'
4,3,31,'oranges'
In this setup, databases are listed in the DATABASES table, and user
passwords are stored in the passwords table. Records in the passwords table
are linked to the USERS table via the foreign key PWD_USERID and to the
DATABASES table via the foreign key PWD_DBID. So, a given database may have
0, 1, or more corresponding entries in PASSWORDS, and a given user may have
0, 1, or more corresponding entries in PASSWORDS.
I want a list showing DB_NAME and PWD_TEXT from databases (a) of DB_TYPE=1,
(b) all corresponding rows from the PASSWORDS table (if available), and (c)
rows from the PASSWORDS table limited by the current user's USERID, which I
know.
Parts a and b are easy:
SELECT DB_NAME,PWD_TEXT
FROM DATABASES, PASSWORDS
WHERE DB_TYPE = 1 AND
DB_ID = PWD_DBID (+)
This returns:
MyDatabase,'jonah'
MyDatabase,'apples'
MyDatabase,'harrington'
But I want to limit output to the password for the current user, whose ID is
21. If I add PWD_USERID = 21 to the WHERE clause, I get no records.
Is there an easy way to do this? I can achieve it through a clunky way, but
it seems that it should be easy. I want only to limit the group of records
against which I am performing the left outer join.
Thanks,
Matthieu
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
