Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


table join problem

table join problem

table join problem

I need to join two tables, but the issue is there is no similar columns to join. what i mean is;

table 1
name                          type    notes
----                          ----    ------------
12345678-22556                3601    sample notes
123123123-22555               3601    sample notes
12345678123456-22556          3602    sample notes

table 2

siteid                        location
-------                       --------  
12345678                      Toronto
123123123                     Toronto

i need to join table 1 with table 2 on name and siteid.
In table 1 the characters upto "-" is the siteid.

Can someone help me with this.

Thanks in advance.

RE: table join problem

Suggest table1 be properly defined. . .

With the current definition, you could also do this inside a program (read table1, build the siteid into a variable, read table2).


RE: table join problem

Use combo of substr and posstr to purge the siteid from the name in table 1. In order to compare you will need to make sure that you have same datatype between join columns, so use the INT function to make an integer of the partial string from name from table 1.

Ties Blom

RE: table join problem

Hi blom0344,

I'm new to this, so can you please give an example how to use the substr and posstr in SQL.

Thanks a lot.  

RE: table join problem

The POSSTR function returns the position of a character (or string) in another string. For instance:
would return the value 4.

The SUBSTR function returns a portion of a string dependant on the parameters given in the SUBSTR function. For instance:
would return the value 'BC' (position 2 for 2 characters)

What Ties is alluding to is a WHERE clause that uses POSSTR on table 1, combined with SUBSTR in order to give you the start and end of the key, dropping off everything after the '-', and then linking that with table 2.

If you need further help, let us know.

Ps - I haven't given you the complete answer as you said that you were new to this, and I believe that you will find a good deal of satisfaction when you work this slightly unusual problem out yourself.   

RE: table join problem

Thanks Marc,

that helped me a lot.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close