SQL Query Help

[mAdMaLuDaWg]

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]

Which DB?

 

[mAdMaLuDaWg]

MS SQL

 

[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.

 

[HeroOfPellinor]

Man I'd like to help, but I have no idea what results you actually want.

 

[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)')

 

[mAdMaLuDaWg]

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.

 

[HeroOfPellinor]

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]

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...