-
DaPi's 1¢ prize
It's getting quiet around here.
OK, for 1¢ and vast honour, explain this (true story):
I wanted to select the rows in table A containing an id existing in a code table B. I coded:
Code:
Select * From A
Where myId = (Select myId From B Where CodeVal = 'Something')
There were in fact no rows in A containing the id I was looking for, however the query returned all the rows of table A.
Why? What mistake had I made?
Added later:
If you want to work on this problem for fun, DON'T SCROLL DOWN The prizewinning answer is two posts further on.
Last edited by DaPi; 04-29-2005 at 04:13 AM.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
PHP Code:
SQL> desc A
Name Null? Type
----------------------------- -------- ----------------------------
MYID CHAR(1)
NAME VARCHAR2(20)
SQL> select * from a;
M NAME
- --------------------
TAMIL
DAPI
SQL> desc b
Name Null? Type
---------------------------- -------- ----------------------------
MYID CHAR(1)
CODEVAL VARCHAR2(20)
SQL> select * from b ;
M CODEVAL
- --------------------
abc
SQL> select * from a
where myid = (select myid from b where codeval = 'abc') ;
M NAME
- --------------------
TAMIL
DAPI
I enter ' ' value for MYID in both tables.
Did I tell the correct answer?
Tamil
-
Table B doesn't have a column called myID ...
Code:
SQL> create table A (myId number);
Table created.
SQL> insert into A values (1);
1 row created.
SQL> insert into A values (2);
1 row created.
SQL> create table B (codeval varchar2(30));
Table created.
SQL> insert into B values ('Something');
1 row created.
SQL> Select * From A
2 Where myId = (Select myId From B Where CodeVal = 'Something')
3 /
MYID
----------
1
2
Your name is DaPi and I claim my five pounds. Or 1 cent.
-
Originally posted by tamilselvan
I enter ' ' value for MYID in both tables.
Sorry Tamil, I said "There were in fact no rows in A containing the id I was looking for". I'd never use ' ' as an id, but nothing is stopping me.
slimdave wins the prize - I think it's now two beers and 1¢ I owe you. I'm being generous because the example you give has no id column in table B.
The real-life problem was that the two corresponding id's had different names:
rlRequiredDocumentId and rlRequiredDocId
and I typed the same name twice.
Two or three lessons to learn
- better discipline in design & specification
- using aliases can help a lot:
Select * From A Where A.myId = (Select B.myId From B Where B.CodeVal = 'Something')
would have failed.
- sub-queries inherit variables
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Thanks Slimdave & Dapi.
Slim deserves Johnnie Walker Black label Scotch.
Learned new stuff.
Tamil
-
Originally posted by DaPi
[B]I think it's now two beers and 1¢ I owe you. I'm being generous because the example you give has no id column in table B.
Penalised for presenting the simplest possible example? Tut.
I'l reserve judegment on the generosity issue until I get those beers and that cash. Maybe I'll start a DaPi Generosity timer on my blog. "Days With No Beer: n".
-
Originally posted by slimdave
Penalised for presenting the simplest possible example? Tut.
I'l reserve judegment on the generosity issue until I get those beers and that cash. Maybe I'll start a DaPi Generosity timer on my blog. "Days With No Beer: n".
You can always drink Coors Lite !!!
Don't they make that out your way???
-
Originally posted by gandolf989
You can always drink Coors Lite !!!
Don't they make that out your way???
I said "Beer", you scoundrel.
B-E-E-R
-
Originally posted by slimdave
Penalised for presenting the simplest possible example? Tut.
You've just lost your 1¢, fails to meet spec:
. . . containing an id existing in a code table B
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Originally posted by DaPi
You've just lost your 1¢, fails to meet spec:
I wanted to select the rows in table A containing an id existing in a code table B.
Hey, you said that that's what you wanted to do -- you didn't say that the code actually was in table B. From the code your intentions were never in doubt ... just your competence, my little furry friend.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|