Im making a Join
Select Table1
From Tables
Where Table1=Table2
Table1 Table2
09876 Q9876
09871 Q9871
02345 Q2345
09867 Q9867
I need to join these two tables, and pull Table1, however I still need for that letter Q to be equal to '0' and I need to keep the zero in front of the number..
How would i do that?
Thank you
What DB are you using?
I cant tell if you are trying to join 2 separate tables, or attempting to filter a single table based on values in 2 columns, so here is the solution for both:
Single Table Filter
Assuming:
* you are using MSSQL Server
* both your fields are text
* You are trying to 'match' the 2 columns in a single table to produce a filtered query (catering for the difference between Q & 0)
* All your values start with Q, and there is only ever a single Q in the column
SELECT Table1
FROM Tables
WHERE Table1 = replace(Table2,'Q','0')
Joining 2 tables
Assuming:
* you are using MSSQL Server
* both your fields are text
* You are trying to join 2 separate tables on the columns that have 0 or Q
* All your values start with Q, and there is only ever a single Q in the column
SELECT t1.ColumnName
FROM Table1 t1, Table2 t2
WHERE t1.ColumnName = replace(t2.ColumnName,'Q','0')
Of course you need to update ColumnName to be whatever the columns are called in the DB.
Hope this helps.
J
Im using Teradata
Thank you Im gonna try this right now
Im trying to use REPLACE
SELECT Table1,
Table2,
FROM ACCT_HIST left join
ACCT_LIST on (Table1 = replace table2 ,'Q','0')left join
I keep getting this error expected something between = and REPLACE.
What am I doing wrong?
Thank you
Maybe you need another type of syntax? :-\
Hi,
May be you need to an one more set of braces between = and replace.
Regards,
~Harish