You should really be qualyfing your tables and columns. What DB are you using?
----- Original Message ----- From: "Cornillon, Matthieu" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, November 21, 2002 8:47 PM Subject: left outer join with a limit on the right? > 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
