FreeLists / oracle-l / RE: table join

["Roger Xu" <roger_xu@xxxxxxxxxxx>]

thanks for all of you who had answered or are answering my email.

-----Original Message-----
From: Jim Silverman [mailto:jsilverman@xxxxxxxxxxxxx]
Sent: Wednesday, February 01, 2006 3:14 PM
To: Roger Xu; Oracle-L@Freelists. Org (E-mail)
Subject: RE: table join



You concatenate two columns in the WHERE clause the same way you do so in the 
SELECT clause:

        ...

        where A.Lastname || A.Firstname = B.Lastname-Firstname

        ...

I'm not sure if I have the column names exactly correct, but you should get the 
idea.

Note that this expression in the WHERE clause will likely preclude the use of 
indexes on either of the columns in table A, unless you use function-based 
indexes.

=====================================
Jim Silverman
Senior Systems Database Administrator
Solucient, LLC
Telephone:   734-669-7641
FAX:            734-930-7611
E-Mail:         jsilverman@xxxxxxxxxxxxx 

        From: oracle-l-bounce@xxxxxxxxxxxxx [ 
mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Roger Xu
Sent: Wednesday, February 01, 2006 4:07 PM
To: Oracle-L@Freelists. Org (E-mail)
Subject: table join

        Table A:

        Lastname (Mike)

        Firstname (Johnson)

        Phone(323-442-5678)

         

        Table B:

        Lastname-Firstname(MikeJohnson)

        StreetAddress(1242 Main St.)

        Zip(76222)

         

        How do I join these two tables?

         

        select B.Lastname-Firstname,A.Phone,B.StreetAddress,B.Zip

        from A,B

        where B.Lastname-Firstname = A.Lastname + A.Firstname

         

        Basically, how to concatenate two fields in the where clause?

         

        Thanks,

         

        Roger


        This e-mail is intended solely for the person or entity to which it is 
addressed and may contain confidential and/or privileged information. Any 
review, dissemination, copying, printing or other use of this e-mail by persons 
or entities other than the addressee is prohibited. If you have received this 
e-mail in error, please contact the sender immediately and delete the material.
____________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email Security 
System. Any questions please call 972-721-8257 or email your request to 
tech_support@xxxxxxxxxxxx

This message is a private communication. It may contain information that is 
confidential
and legally protected from disclosure. If you are not an intended recipient, 
please do
not read, copy or use this message or any attachments, and do not disclose them 
to others. 
Please notify the sender of the delivery error by replying to this message, and 
then 
delete it and any attachments from your system. 
Thank you,
Solucient LLC
(eXclaimer 4x)

For technical support please email tech_support@xxxxxxxxxxx or you can
call (972)721-8257. 
This email has been scanned for all viruses by the MessageLabs Email Security 
System.



This e-mail is intended solely for the person or entity to which it is 
addressed and may contain confidential and/or privileged information. Any 
review, dissemination, copying, printing or other use of this e-mail by persons 
or entities other than the addressee is prohibited. If you have received this 
e-mail in error, please contact the sender immediately and delete the material. 
____________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email Security 
System. Any questions please call 972-721-8257 or email your request to 
tech_support@xxxxxxxxxxxx