Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    33

    Unanswered: This should be easy.... subquery with like

    Hi,
    I have a very simple question:
    I have a subquery and I would like to use the LIKE statement ether in the join or where part.
    The problem is that if I just use the column name (from the top query) it looks for the exact match (no place for the wild card %).
    If I place a + '%' at the end, the query runs but does not return the matching rows that are not exact.
    Here is my statement:
    SELECT A,

    (SELECT B
    FROM T2
    WHERE A LIKE [B] + '%') AS newc

    FROM T1

    What am I doing wrong?

    Thanks
    Motty

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    silly question but should it be

    SELECT T1.A, (SELECT T2.B FROM T2 WHERE T2.B LIKE T1.A + '%') AS ewc
    FROM T1
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Nov 2002
    Posts
    33
    Yes...
    Here is my actual code:
    ----------------------------------
    SELECT mos.job, (SELECT max(mop.oh_number) FROM mop WHERE mop.oh_number LIKE mos.job + '%') AS ewc
    FROM mos

    am I missing something?


    Motty

  4. #4
    Join Date
    Nov 2002
    Posts
    33
    OK,
    That was easy.... I added the rtrim() to both sides of the WHERE statment...



    Thansk
    Motty

    Originally posted by mseal1
    Yes...
    Here is my actual code:
    ----------------------------------
    SELECT mos.job, (SELECT max(mop.oh_number) FROM mop WHERE mop.oh_number LIKE mos.job + '%') AS ewc
    FROM mos

    am I missing something?


    Motty

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    were both attributes char datatype? Also this solution will force a table scan, is that okay?
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Nov 2002
    Posts
    33
    Both are char type.
    Is there a better way to do this?

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Better, hum??

    IMNSHO, I try to avoid using char datatypes and opt for varchar instead. With a char datatype you always have to deal with trailing space.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Nov 2002
    Posts
    33
    I take it back.
    The type is nvarchar
    does that help?

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I'd say you have the best answer with your current code. The "N" on nvarchar just means unicode.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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