Help with SQL

[masha2008]

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

[JGirl]

What DB are you using?

[JGirl]

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

Code Select Expand


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

Code Select Expand


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

[masha2008]

 Im using Teradata

Thank you Im gonna try this right now

[masha2008]

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

[adm_agec]

Maybe you need another type of syntax? :-\

[harish.malik]

Hi,

May be you need to an one more set of braces between  = and replace.

Regards,
~Harish