General discussion


SQL Join

By graemeduncan ·

I have 2 tables that I want to join, the two fields I wish to join on are both the same data type "VARCHAR2(20)" however field one contains data in the format "99999999" while field two contains data in the format "99999999/9". There is a unique match on characters 1-8 on both fields in the tables.

I did think of adding on the "/9" to field one but was unsure how to go about this. Any help would be most appreciated.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Jaqui In reply to SQL Join

what database server application?
most will support outer join, or inner join, but not both.
some will support both.

Collapse -

by Jaqui In reply to

Inner and Outer Joins
A join between two tables does not include any rows from either table that have no matching rows in the other. This is called an inner join and frequently causes confusion since fewer rows are returned than the user expects. For example, tables english and spanish look like this:
* select * from english; * select * from spanish; ----------------------------- ----------------------------- |tag |name | |tag |name | ----------------------------- ----------------------------- |1 |one | |2 |dos | |2 |two | |3 |tres | |3 |three | |4 |cuatro | ----------------------------- ----------------------------- 3 rows selected 3 rows selected
When you join these two tables, you get only the two rows that have the same tag:
* select, e.tag, from english e, spanish s where e.tag = s.tag; ------------------------------------------- |name |tag |name | ------------------------------------------- |two |2 |dos | |three |3 |tres | ------------------------------------------- 2 rows selected
Row one in table english and row cuatro in table spanish fall into the outer joins:
Joins +--------------+ left outer ---> | one 1 | | +--------------+ +--> | two | 2 : dos | inner join | | | : | +--> | three | 3 : tres | +--------|- - -+ | | 4 cuatro| <--- right outer +--------------+
You can select outer join rows by using not exists. This query fetches the row in english that is not in spanish (the left outer join):
* select as English, e.tag, '--no row --' as Spanish from english e where not exists (select * from spanish s where e.tag=s.tag); ------------------------------------------- |English |tag |Spanish | ------------------------------------------- |one |1 |--no row -- | ------------------------------------------- one row selected
This query fetches the row in spanish that is not in english (the right outer join):
* select '--no entry--' as English, s.tag, as Spanish from spanish s where not exists (select * from english e where e.tag=

Collapse -

by graemeduncan In reply to SQL Join

I am running SQL*PLUS 9.0.1

Collapse -

by Tony Hopkinson In reply to SQL Join

select * from table1,table2 where substr(table1.joinfield,1, = substr(table2.joinfield,1,
Don't know the software you're using but there should be a function like substr
could be substring or even Mid$ if it's vb based.

Related Discussions

Related Forums