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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
