Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Unanswered: SELECT B WHERE no A, Else Select A

    Having a tough time trying to work out a SELECT statement...

    I have a table like this:

    ID Name Type
    =============
    100, Babbit, A
    101, Jones, A
    101, Jones, B
    102, Smith, B


    The result set should be:

    ID Name Type
    =============
    100, Babbit, A
    101, Jones, A
    102, Smith, B

    Criteria: I need all the Type A records but only Type B records if there is no Type A record. This must be simple but I'm just not seeing it.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the data in the column Type really is as simple as in the sample you provided, here is a solution:
    Table1:
    Code:
    ID	Name	Type
    ---------------------
    100	Babbit	A
    101	Jones	A
    101	Jones	B
    102	Smith	B
    Query:
    Code:
    SELECT Table1.ID, Table1.Name, Min(Table1.Type) AS Type
    FROM Table1
    GROUP BY Table1.ID, Table1.Name
    ORDER BY Table1.ID
    Results:
    Code:
    ID	Name	Type
    ---------------------
    100	Babbit	A
    101	Jones	A
    102	Smith	B
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    2
    Thought I could get by with over-simplifying the case. Nope! There are 5 additional columns that may have one or more unique values for records with the same ID number. Your code works fine for the simple table I gave but now I see there are complications with the additional columns in the picture. I can't just select the other columns but not group on them, unfortunately.

    Here's my code. The first SELECT calculates the current month's accrued interest using the prior month's accrued interest in the calculation if a record exists for the prior month AND it has a value in the AccruedInt field (could be NULL.

    The second (UNION'd) SELECT attempts to handle the records that the first select can't and apply a default calculation. But I can't seem prevent some of the records being handled by both SELECTS rather than either one or the other. Sorry this isn't as simple as I tried to present it. I haven't been involved with this stuff for about ten years and am finding I have forgotten much more than I'd like to admit.



    Select z.BankID, z.LastName, z.MonthNo, z.YearNo, z.MortgageInt, z.EarnedInt, z.AccruedInt, Min(z.Type) As Type
    From
    [SELECT q.BankID, q.LastName, q.MonthNo, q.YearNo, q.MortgageInt, q.EarnedInt, (q.MortgageInt - NZ(t.AccruedInt) + q.EarnedInt) AS AccruedInt, "A" AS Type
    FROM qryAccrued AS q LEFT JOIN tblBankInterest AS t ON q.BankID = t.BankID
    WHERE t.BankID Is Null
    OR (t.MonthAbbr & t.YearNo = (MonthName(IIF(q.MonthNo -1 = 0,12,q.MonthNo -1), -1) & IIF(IIF (q.MonthNo -1 = 0,12,q.MonthNo -1) =12,q.YearNo -1,q.YearNo)))

    UNION ALL
    SELECT q.BankID, q.LastName, q.MonthNo, q.YearNo, q.MortgageInt, q.EarnedInt, (q.MortgageInt + q.EarnedInt) AS AccruedInt, "B" AS Type
    FROM qryAccrued AS q INNER JOIN tblBankInterest AS t ON q.BankID = t.BankID
    WHERE (t.MonthAbbr & t.YearNo <> (MonthName(IIF(q.MonthNo -1 = 0,12,q.MonthNo -1), -1) & IIF(IIF (q.MonthNo -1 = 0,12,q.MonthNo -1) =12,q.YearNo -1,q.YearNo)))
    GROUP BY q.BankID, q.LastName, q.MonthNo, q.YearNo, q.MortgageInt, q.EarnedInt, (q.MortgageInt + q.EarnedInt)]. AS z

    GROUP BY z.BankID, z.LastName, z.MonthNo, z.YearNo, z.MortgageInt, z.EarnedInt, z.AccruedInt

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you send a sample of the actual table (with the sensible values obfuscated, of course), just like you did in your first post?

    I find it rather difficult to work on such problem with just the abstraction of a query (which does not work as expected, moreover).
    Have a nice day!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try:
    Code:
    SELECT *
       FROM YourTableNameHere AS a
       WHERE  'A' = a.Type
          OR NOT EXISTS (SELECT 1
             FROM YourTableNameHere AS b
             WHERE  b.ID = a.ID
                AND 'A' = b.Type)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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