So this is what finally worked for you then?
SELECT d.db_name, p.pwd_text
FROM Databases d,
(Select pwd_dbid, pwd_text from passwords where pwd_userid = 21) p
WHERE d.db_id = p.pwd_dbid(+)
AND d.db_type=1;
Given the fact that I completely overlooked your requirement to return *all* records
from the Databases table, I'm pleased that you were able to come up with the solution!
:)
~Dina
----- Original Message -----
From: Cornillon, Matthieu
To: SQL
Sent: Friday, November 22, 2002 12:26 PM
Subject: RE: left outer join with a limit on the right?
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
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
