Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: Query

  1. #1
    Join Date
    May 2002
    Posts
    73

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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: Query

    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.

  3. #3
    Join Date
    May 2002
    Posts
    73

    Dinamic SQL

    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,

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    select fecha, hora, tamano
    from messages where
    numero IN (select min_user from voice)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2002
    Posts
    73

    Query

    Hi Brett,

    Thanks for your suggestion.

    I ran it and returns nothing.....

    What it could happen?

    What about stored procedures?

    Regards,

  6. #6
    Join Date
    May 2002
    Posts
    73

    Query

    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,

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    May 2002
    Posts
    73

    Query

    Hello,

    I get the attached sql errors...

    Thanks
    Attached Files Attached Files

  9. #9
    Join Date
    May 2002
    Posts
    73

    Query

    Ups..
    I get the attached sql error
    Thanks
    Attached Files Attached Files

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: Query

    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

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: Query

    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

  12. #12
    Join Date
    May 2002
    Posts
    73

    Query

    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,

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  14. #14
    Join Date
    May 2002
    Posts
    73

    Query

    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,

  15. #15
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: Query

    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.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •