[thelist] SQL View: Join? SubQuery?

Joshua Olson joshua at waetech.com
Wed Aug 21 09:38:01 CDT 2002


----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Tuesday, August 20, 2002 1:42 PM


> it requires an unconventional join
>
>   select ProgramID, Venue, VDate, Conductor
>        , CompositionTitle, ComposerName
>      from Programs
>   inner
>      join Compositions
>        on CompID in ( PerformedPieces )

Rudy,

I hate to contradict you (since you are the man and all) but I couldn't get
the query to work.  :(

Since PerformedPieces is a string (varchar, char, etc), and CompID is a
number (numeric, int, etc), the SQL Parser throws a nasty "Cannot convert
from varchar to numeric" error.

Can you please explain to me what I'm doing wrong?

I set up a very simply table structure:

Table1

id     name (varchar)
~~~ ~~~~~~~~~~
1     apple
2     bobcat
3     cardinal
4     dogma
5     egg

Table2

id    names (varchar)
~~~ ~~~~~~~~
1      1,2,3,4

and then I ran the query:

SELECT *
FROM Table2
INNER JOIN
Table1
ON Table1.id IN (Table2.names)

Thanks,

-joshua




More information about the thelist mailing list