Results 1 to 2 of 2

Thread: Subquery?

  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unhappy Unanswered: Subquery?

    I'm new to SQL, and I'm trying to figure out how to construct a query where it will select values that contain a string "X" and then look for values that contain a string "Y" if X does not exist.

    Here is my sample code:

    SELECT RB_MLS_1.CATEGORYID,
    RB_MLS_1.CATEGORYNAME, RB_MLS_1.COST,
    RB_MLS_1.COSTTYPEID,
    RB_MLS_1.COSTTYPENAME,
    RB_MLS_1.DESCRIPTION, RB_MLS_1.DOWNLOADID,
    RB_MLS_1.ID, RB_MLS_1.MEDIADESCRIPTION,
    RB_MLS_1.MEDIAID, RB_MLS_1.NAME,
    RB_MLS_1.QUANTITY,
    RB_MEDIA_1.DESCRIPTION DESCRIPTION_2,
    RB_MEDIA_1.ID ID_2
    FROM RB_MLS RB_MLS_1
    LEFT OUTER JOIN RB_MEDIA RB_MEDIA_1 ON
    (RB_MEDIA_1.ID = RB_MLS_1.MEDIAID)
    WHERE ( RB_MLS_1.CATEGORYNAME IN ('B&W Display') )
    AND ( RB_MEDIA_1.CATEGORYNAME IN ('Daily','Weekly') )
    AND (( RB_MLS_1.NAME LIKE '2 col x 7%')
    OR (SELECT RB_MLS_1.NAME Like '1/4%'))

    The result of my query is that I am getting all RB_MLS_1.NAME records that contain '2 col x 7' and '1/4'. Does anyone know how I can select LIKE '2 col x 7%' first and then select LIKE '1/4%' if '2 col x 7%' does not exist? Thanks for your help in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Subquery?

    Originally posted by cjl7157
    I'm new to SQL, and I'm trying to figure out how to construct a query where it will select values that contain a string "X" and then look for values that contain a string "Y" if X does not exist.

    Here is my sample code:

    SELECT RB_MLS_1.CATEGORYID,
    RB_MLS_1.CATEGORYNAME, RB_MLS_1.COST,
    RB_MLS_1.COSTTYPEID,
    RB_MLS_1.COSTTYPENAME,
    RB_MLS_1.DESCRIPTION, RB_MLS_1.DOWNLOADID,
    RB_MLS_1.ID, RB_MLS_1.MEDIADESCRIPTION,
    RB_MLS_1.MEDIAID, RB_MLS_1.NAME,
    RB_MLS_1.QUANTITY,
    RB_MEDIA_1.DESCRIPTION DESCRIPTION_2,
    RB_MEDIA_1.ID ID_2
    FROM RB_MLS RB_MLS_1
    LEFT OUTER JOIN RB_MEDIA RB_MEDIA_1 ON
    (RB_MEDIA_1.ID = RB_MLS_1.MEDIAID)
    WHERE ( RB_MLS_1.CATEGORYNAME IN ('B&W Display') )
    AND ( RB_MEDIA_1.CATEGORYNAME IN ('Daily','Weekly') )
    AND (( RB_MLS_1.NAME LIKE '2 col x 7%')
    OR (SELECT RB_MLS_1.NAME Like '1/4%'))

    The result of my query is that I am getting all RB_MLS_1.NAME records that contain '2 col x 7' and '1/4'. Does anyone know how I can select LIKE '2 col x 7%' first and then select LIKE '1/4%' if '2 col x 7%' does not exist? Thanks for your help in advance.
    I don't follow your logic. You are looking for strings that START WITH '2 col x 7' or START WITH '1/4'. Is that what you want? If so, your query is correct (as far as that is concerned). A string can't start with both at once, so the bit about "Y if X does not exist" is implicit.

    If you really meant contained ANYWHERE in the string then:

    AND (( RB_MLS_1.NAME LIKE '%2 col x 7%')
    OR (RB_MLS_1.NAME NOT LIKE '%2 col x 7%' AND RB_MLS_1.NAME Like '%1/4%'))

Posting Permissions

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