SQL "IN" with field value (AKA: HELP RUDY!)

[csteinhilber]

I have a query in which I'm trying to join some tables base on whether a given field in table A contains the ID of a record in table B... something like:

<CFQUERY ...>
    SELECT *
    FROM tableA A,tableB B
    WHERE B.id IN ( A.ids )
</CFQUERY>

but, of course, Oracle complains because A.ids is a string and B.id is numeric (at least... that's what I think is going on).

How can I get this done? I'm sure it's something simple, but my brain is mush.

TIA!
-Carl

 

[bnymk]

Hey Carl,

Can't you use inner join in your query?????

 

[csteinhilber]

Iiiiiii... don't think so.

I'd still have to compare a varchar against an int.... wouldn't I?


-Carl

 

[r937]

carl, thanks for thinking of me in the thread title

it worked, too -- as soon as i got home and launched tek-tips, i immediately saw it

sadly, the news i have for you is not good

you cannot do what you want :-(


query the A table, get the ids, stuff them into an sql query string, then use that to query the B table

conceptually neat and simple, clean code, and the only thing is, you have two calls to the database in a row, instead of one, so the performance is a teensy-weensy bit slower

doing it in one query is impossible

well, not impossible, but you wouldn't want to try it, it will resemble the piece of code by Joe Celko at the bottom of this article here --

How to do looping in SQL

http://searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid544808_tax285649,00.html

a comma-delimited list of keys in a column violates first normal form

no soup for you -- one year!!


rudy

 

[csteinhilber]

Yikes... yeah, two database calls would be a lot easier than Joe's code.

Oh well... I guess it wasn't just my brain.

And, no, I didn't design the database.



But everyone in front of me got free bread!!!

Thanks, Rudy!


-Carl

 

[jeepxo]

I was thinking about this on the way home from work tonight. (traffic was bad and I had nothing else to think about)

Now I haven't tested it but why couldn't you do this?

Select * from TableA A, TableB B where Trim(B.id + " &quot in (Select * from TableA innerjoin TableB on TableA.id = Trim (TableB.id + " &quot)

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[r937]

jeepxo, i love a guy for trying

problem is, in your subquery, you're trying to join A.id to B.id, and the thing is (if i understood Carl correctly), A.id contains a comma-separated list of ids


rudy

 

[csteinhilber]

Yep... you understood correctly :-(


-Carl

 

[jeepxo]

From what I read above A.ID is string variable of some kind. B.ID is an int.
By concatenating B.id with a space then removing the space, the value of B.ID becomes a string.

That's how I read it.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001