Query

Hello folks

I have next tables:

t1: messages with fields fecha, hora, tamano, numero

t2: voice with fields min_id, min_user, min_ciudad

field t1.numero contains field t2.min_user. e.g numero = 0I1234585852555850234555255585023455 12.WAV and min_user = 5255585023455

So I want to build a query which can return me fecha,hora, tamano, numero for each min_user.

I was thinking on something like:

select fecha, hora, tamano from messages where numero like '%(select min_user from voice)'

Thanks in advance for your help.I know you could do this using Dynamic SQL. Personally, I would never prefer using this. But if there is no other option left for you try it.

Originally posted by jeremas
Hello folks

I have next tables:

t1: messages with fields fecha, hora, tamano, numero

t2: voice with fields min_id, min_user, min_ciudad

field t1.numero contains field t2.min_user. e.g numero = 0I1234585852555850234555255585023455 12.WAV and min_user = 5255585023455

So I want to build a query which can return me fecha,hora, tamano, numero for each min_user.

I was thinking on something like:

select fecha, hora, tamano from messages where numero like '%(select min_user from voice)'

Thanks in advance for your help.|||You know, Im not an SQL expert, I just started working with this without any serious SQL training.
So I will appreciate if you could help me with code, how it could look.
Regards,|||select fecha, hora, tamano
from messages where
numero IN (select min_user from voice)|||Hi Brett,

Thanks for your suggestion.

I ran it and returns nothing....

What it could happen?

What about stored procedures?

Regards,|||If I run just

select count(*) from messages where numero like '%5585090333%'

or select * from messages where numero like '%5585090333%'

where 5585090333 is one value of field min_user of table voice it returns all values for such condition. But when I run the same query using where numero like '%(select min_user from voice)%' it gives nothing, the same if I use IN instead LIKE
why?

Regards,|||SELECT fecha, hora, tamano
from messages a
where EXISTS
(select * from voice b WHERE '%'+a.numero+'%' LIKE '%'+b.min_user+'%')

Or

SELECT fecha, hora, tamano
from messages a
where '%'+a.numero+'%'
IN (select '%'+b.min_user+'%' from voice b)

Either way..not pretty|||Hello,

I get the attached sql errors...

Thanks|||Ups..
I get the attached sql error
Thanks|||Try:
declare @.str varchar(100)
select @.str = 'select fecha, hora, tamano from messages where numero like ''%' + min_user from voice + '%'''
print @.str

Originally posted by jeremas
Ups..
I get the attached sql error
Thanks|||Ooops ... Instead of print (@.str) it should be exec (@.str)
Originally posted by sbaru
Try:
declare @.str varchar(100)
select @.str = 'select fecha, hora, tamano from messages where numero like ''%' + min_user from voice + '%'''
print @.str|||Sorry for asking, are you with this declaring a new function or procedure?

I hope you dont mind but I'm new on SQL

Regards,|||jeramas,

surely the concatenated values in t1.numero are delimited in some logical way, such as fixed width? If not, you might as well throw up your hands in defeat right now, 'cause your problems with this are going to last the life of the application.

If they are concatenated with some sort of consistent logic, then you should develop an algorythym for parsing out the elements. Put it in a function if you can.

blindman|||The thing is that for a single record it work quite well
But using something like where numero like '%(select number_min from voice)%' returns empty row.

Regards,|||Originally posted by jeremas
where numero like '%(select number_min from voice)%'
Regards,

Man .. when you say '%(select number_min from voice)%' SQL server is not going to run the select query coz it assumes it is a string you are searching for which has '(select number_min from voice)' string in between. Hence your 0 rows.|||So can u correct the code...|||Originally posted by jeremas
So can u correct the code...

What do you think the guys have been doing prior to my post ?

Oh .. and BTW .. the error messages you posted make me think you are executing their code in EM ... use Query analyzer ...|||Thanks folks,

Now Im doing:

select r.fecha, r.hora, r.tamano, r.numero, c.number_min from messages r full join numeros c on r.numero containing '%c.number_min%' order by c.number_min;

Looks like it is worknig. Now I dont know how to send the results to a table.

Regards,|||select r.fecha, r.hora, r.tamano, r.numero, c.number_min
INTO #temp
from messages r
full join numeros c
on r.numero containing '%c.number_min%' order by c.number_min;|||While running the query the performance is very poor.

Is ther any way to speed up the process?

Regards,|||Placing a wildcard in front of a string will always cause a table scan

How many rows do you have in both tables?|||More than 500,000 in each table...

But I, not using the wildcar option...

Regards,|||What do you think the % sign is?

What's "Containing"?

Is this SQL Server?

Theres a CONTAINS, but no containing...|||Instead of %c.number% or whatever I jus use contains c.number

Regards,|||It is Interbase.