Dina: I tweaked this for a while, but still couldn't get it to work. But you still saved the day! You see, as a SQL newbie, I was familiar only with the use of subqueries within the WHERE clause (e.g., WHERE col_name IN (SELECT...)). That you can put them in the FROM clause opens up whole new worlds for me. I ended up first limiting the PASSWORDS table through a subquery, and then doing a left outer join of the original DATABASES table against the resulting subquery table.
And Stephen: thanks for your advice on qualifying column names! It became clear to me that, despite the fact that my column naming convention ensures no ambiguous names, the use of subqueries and self joins introduces the possibility of ambiguity. Also, it makes sense that performance would be enhanced by qualification. You two have taught me valuable SQL lessons. Thanks a bunch! Matthieu -----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 6:52 PM To: SQL Subject: Re: left outer join with a limit on the right? I haven't worked with PL/SQL in a while so you'll have to tweak the syntax, but something like this should work: SELECT Databases.db_name, Passwords.pwd_text FROM (Select db_id, db_name from databases where db_type=1) as Databases INNER JOIN Passwords on databases.db_id = passwords.pwd_dbid Where passwords.pwd_userid = 21 And you'll notice the inner join works just fine. ~Dina ----- Original Message ----- From: Cornillon, Matthieu To: SQL Sent: Thursday, November 21, 2002 3:09 PM Subject: RE: left outer join with a limit on the right? Oracle 8. I have a question about qualifying. Why is it necessary? Does it speed things up? I have a naming convention that ensures no ambiguity (the below example is just made up and does not conform to this convention), so if it's just that, I'm not so worried. But if I'm being silly, it's something I'd like to go back and remedy. Thanks, Matthieu -----Original Message----- From: Neil Robertson-Ravo [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 4:00 PM To: SQL Subject: Re: left outer join with a limit on the right? 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 Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
