SQL Query Help

mAdMaLuDaWg

Platinum Member
Feb 15, 2003
2,437
1
0
I have two tables with data sorta like this
Table1
MO-XV
MO-VX
MO-SVTR
MO-UVUAQ

Table 2
MO-XV1
MO-VXajs
MO-SVTRUAQUZ
MO-UVAQ341

So basically what I need to do is see if the data in table 1 is in table 2. However, Table 1 has truncated data so actually I need to do something like this:
Select * from Table2 where Table2.Data like '(Select Table1.Data from Table1)%'

So in the above instance it should return all the data in Table 2.

Any ideas?
 

Thyme

Platinum Member
Nov 30, 2000
2,330
0
0
Haven't used MS-SQL, but in PL/SQL, you can

select * from table1 t1, table2 t2 where
trunc(t1.a,5) = t2.b and
trunc(t1.c,5) = t2.d and ...

See if you have a truncate function, or else just use a substring function as long as they are actually perfectly truncated and not just some things are missing from the middle.
 

mAdMaLuDaWg

Platinum Member
Feb 15, 2003
2,437
1
0
Originally posted by: HeroOfPellinor
Man I'd like to help, but I have no idea what results you actually want.

Basically I need to know how to do this
Select * from Table2 where Table2.Data like 'VAR%'

(VAR='(Select Table1.Data from Table1)')

 

mAdMaLuDaWg

Platinum Member
Feb 15, 2003
2,437
1
0
Originally posted by: Thyme
Haven't used MS-SQL, but in PL/SQL, you can

select * from table1 t1, table2 t2 where
trunc(t1.a,5) = t2.b and
trunc(t1.c,5) = t2.d and ...

See if you have a truncate function, or else just use a substring function as long as they are actually perfectly truncated and not just some things are missing from the middle.

The lengths could be variable so I can't use truncate, but I'll see if I can work it out with the CHARINDEX function.
Thanks.
 
Dec 27, 2001
11,272
1
0
Originally posted by: mAdMaLuDaWg
Originally posted by: HeroOfPellinor
Man I'd like to help, but I have no idea what results you actually want.

Basically I need to know how to do this
Select * from Table2 where Table2.Data like 'VAR%'

(VAR='(Select Table1.Data from Table1)')

but you said you also wanted to see every record from table2, correct?
 

Thyme

Platinum Member
Nov 30, 2000
2,330
0
0
You can trunc or substring both if that won't lead to false positives. The important thing is you don't need to use subqueries as you should be able to select from multiple tables.

If you just want the output to be table 2, change the * to t2.a, t2.b...