Post reply

Using the results of one query as the criteria in another query

  • September 7, 2010 at 1:27 pm

    #223641

    I have 2 tables parts and documents.

    The following query which give me the partNumbers from the parts table

    1. SELECT DISTINCT [part numbers]

    FROM parts.

    The following query gives me the id of specific documents

    2. SELECT ID

    FROM documents

    WHERE Text LIKE '%Microwave Instrumentation%'

    I'm trying to achieve the following.

    1. Use the the result of query 1 i.e.part number, to search the text column in the documents table.

    The part number will be part of a string i.e. '% part number%'

  • September 7, 2010 at 1:49 pm

    #1217579

    there's an article on something similar labeled "exotic joins" i think; if you search and read that, it can help you understand some of the less logical reasons to join tables together.

    this is something like what you are after:

    SELECT documents.ID,MySubQuery.[part numbers]

    FROM documents

    LEFT OUTER JOIN (SELECT DISTINCT

    [part numbers]

    FROM parts) MySubQuery

    ON Text LIKE '%' + MySubQuery.[part numbers] + '%'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 8, 2010 at 12:41 am

    #1217740

    Make a CTE :cool::cool::cool:

    Raunak J

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply