Query Construction

  • mail 41752

    Grasshopper

    Points: 24

    I have a need to develop a query that returns multiple rows from a table, based upon a single search term, with this search term to be applied to two different fields, with one filed to be applied to multiple records based upon an associated field in the initial record(s) retrieved.

    Sorry for the messy description. An example is better suited for this description.

    This is the example of what I need to accomplish. The table is set up with two fields, as follows:

    Field A Field B

    123 123

    123 124

    123 125

    123 126

    230 231

    230 232

    230 234

    340 340

    340 341

    340 342

    451 455

    Field B is the Primary Key for the table. Field A in nonunique. The combination of Field A and Field B is unique.

    If I attempt to do a WHERE BY search term of '124', I need the SELECT statement to retrieve the one record which matches the search terms to Field B [A=123 and B=124], but I also need it retrieve any and all records that also have the same '123' in Field A of this one record that was retrieved, based upon its Field A contents. In other words, a single search term of '124' (or '123' or '125' or '126') would retrieve the following record set:

    Field A Field B

    123 123

    123 124

    123 125

    123 126

    Likewise, a single search term of '340', '341' or '342' would retrieve the following record set:

    Field A Field B

    340 340

    341 341

    342 342

    Another wrench in the works is that a Field A value does not necessary have to be in Field B. For example, in the above table data, '230' is in Field A, but it is not contained in Field B. If the single search value is not in Field B, Field A still needs to be searched. For example, a search on the value '230' needs to return the following record set:

    Field A Field B

    230 231

    230 232

    230 234

    Likewise, a search with a single search term of either '231', '232' or '234' would retrieve the same record shown immediately above.

    What is the best approach to accomplishing this search? Can this be accomplished with a single SELECT statement using subqueries?

    Many thanks, in advance, for your help and assistance!

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    Try this:

    DECLARE @FieldB int

    SET @FieldB = 124

    SELECT * From Table1

    WHERE FieldA = (SELECT FieldA from Table1 WHERE FieldB = @FieldB)

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • ChrisM@home

    SSC-Insane

    Points: 24260

    mail 41752 (1/23/2010)


    Can this be accomplished with a single SELECT statement...?

    Yes.

    DROP TABLE #Table1

    CREATE TABLE #Table1 (FieldA INT, FieldB INT)

    INSERT INTO #Table1 (FieldA, FieldB)

    SELECT 123, 123 UNION ALL

    SELECT 123, 124 UNION ALL

    SELECT 123, 125 UNION ALL

    SELECT 123, 126 UNION ALL

    SELECT 230, 231 UNION ALL

    SELECT 230, 232 UNION ALL

    SELECT 230, 234 UNION ALL

    SELECT 340, 340 UNION ALL

    SELECT 340, 341 UNION ALL

    SELECT 340, 342 UNION ALL

    SELECT 451, 455

    DECLARE @Needle INT

    SET @Needle = 123

    SELECT DISTINCT a.FieldA, a.FieldB

    FROM #Table1 a

    INNER JOIN #Table1 b ON b.FieldA = a.FieldA

    WHERE b.FieldB = @Needle

    OR a.FieldA = @Needle

    ORDER BY a.FieldA, a.FieldB

    Cheers

    ChrisM@home


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

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