Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    15

    Unanswered: Subquery with Operators

    I’ve got a subquery that keeps throwing up an error but I can’t think of another way of completing the query.
    Firstly I need to find records where TestQty =3
    (this would find 2 records with TestQty =3, TestNumber, 7171003 and 7088650)
    The TestNumber is not unique so I would like the final set of records to include all the records with TestNumber, 7171003 and 7088650

    TestNumber TestQty
    7088650____________3
    7088650____________1
    7088650____________2
    7088650____________1
    7088650____________2
    7171003____________1
    7171003____________3
    7171003____________2

    Thanks in advance.

    Code below:
    [Code SQL]
    USE TestWarehouse
    IF ('dbo.TestItems') IS NOT NULL
    DROP TABLE [dbo].[TestItems];
    GO

    CREATE TABLE [dbo].[TestItems]
    ( TestItem int not null IDENTITY (1,1)
    ,TestNumber int not null
    ,TestQty tinyint not null
    );

    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7032357 , 1 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7032357, 2 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7032357 , 1 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7034826 , 1 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7034826 , 2 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7088650 , 3 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7088650 , 1 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7088650 , 2 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7088650 , 1 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7088650 , 2 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7171003 , 1 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7171003 , 3 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7171003 , 2 )
    INSERT INTO dbo.TestItems (TestNumber, TestQty)
    VALUES (7175553 , 1 )

    [Code SQL/]
    This is the Sub Query I'm trying to get working:
    [Code SQL]

    SELECT a.TestNumber, a.TestQty,

    (SELECT TestNumber, TestQty FROM [dbo].[TestItems]
    WHERE TestQty IN(3))

    FROM [dbo].[TestItems] a
    LEFT JOIN [dbo].[TestItems] b
    ON a.TestNumber = b.TestNumber

    WHERE a.TestNumber = b.TestNumber

    ORDER BY TestNumber
    [Code SQL/]
    Last edited by andrew a; 06-19-12 at 11:24.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select a.TestNumber, a.TestQty 
    from [dbo].[TestItems] as a
    join [dbo].[TestItems] as b on (b.TestNumber = a.TestNumber) and (b.TestQty = 3)
    Hope this helps.

  3. #3
    Join Date
    Mar 2012
    Posts
    15
    imex that works fine thanks. Unfortunately it doesn’t sort my query as in trying to simplify the question I was posting I lost sight of what I was trying to achieve. D’oh

    This is the select statement I’m actually need to use, you can use the above table

    USE TestWarehouse
    SELECT TestNumber, TestQty
    FROM dbo.TestItems
    WHERE TestQty > 0
    GROUP BY TestNumber, TestQty
    HAVING MIN(TestQty) > 2

    You’ll see if has a “Group By” clause which I can’t get to work in the Left Join stament.

    The crux of the query is to find a set of records, then exapnd that set out to all the records with a matching TestNumber, hence why I was trying to us ea subquery.

    Cheers

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    USE TestWarehouse
    GO
    
    SELECT a.TestNumber, a.TestQty
       FROM dbo.TestItems AS a
       JOIN dbo.TestItems AS b
          ON (b.TestNumber = a.TestNumber)
       WHERE b.TestQty > 2
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Actually, that could get messy pretty fast. A better choice for this problem might be:
    Code:
    SELECT a.TestNumber, a.TestQty
       FROM dbo.TestItems AS a
       WHERE  EXISTS (SELECT *
          FROM dbo.TestItems AS b
          WHERE  b.TestNumber = a.TestNumber
             AND 2 < b.TestQty)
    That will bring back every TestNumber and TestQty for any TestNumber that has one or more TestQty values above two.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Mar 2012
    Posts
    15
    Thanks Pat that works fine (sorry there is a but coming) but the Group By and Min Clause are required.
    In essence I need to
    1) Perform a search to find individual records (this will require using the Group By, Min clause etc see below.)
    2)With the records found in the below search expand the set out to show other records with the same TestNumber .

    <Fit code around below statement that gather up records with the TestNumber

    USE TestWarehouse
    SELECT TestNumber, TestQty
    FROM dbo.TestItems
    WHERE TestQty > 0
    GROUP BY TestNumber, TestQty
    HAVING MIN(TestQty) > 2


    A bit like performing the search, then doing another search with all the TestNumber in a IN(7171003, 7088650) (using the first search as an array to do the second search)

    Cheers

Posting Permissions

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