1

This isn't as simple as the title. (What is an appropriate title?) I think the easiest way to describe my issue is with an example.

My goal is a list of what color balloon’s each child is missing.

Let’s assume table 1 contains the following data in 2 columns:

Child BalloonColor

  • Sally Yellow
  • Sally White
  • Sally Blue
  • Bob Red
  • Bob Green
  • Bob White

This is table 2, also 2 columns.

ColorCode Color

  • Y Yellow
  • W White
  • R Red
  • B Blue
  • G Green
  • P Pink

I need a write a result set that states what color balloon each child needs to have all colors.

  • Sally, R, Red
  • Sally, G, Green
  • Sally, P, Pink
  • Bob, Y, Yellow
  • Bob, B, Blue
  • Bob, P, Pink

My example is small, but assume I have a 1000 children in my table 1 and 75 colors in table 2. How can I check each child, one at a time, is my ultimate question? A Not in query will only yield "P, Pink", but you can see I need it at the child level, not table level.

I'm not a developer, but can write good SQL statements.

MS SQL Server 2008 R2.

Thanks in advance, Mike.

Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
Mike Buesing
  • 11
  • 1
  • 1
    You just need a basic join for this. The Color table should be basis of your query. http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – Sean Lange Feb 02 '16 at 21:05
  • Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) – Tab Alleman Feb 02 '16 at 21:20
  • You have a flaw with this table structure. If a child has no balloons at all yet and hence has no row in table1 they should require all balloon colours but because there is no row in table 1 we don't know about them. I think you need a table with just the children's names in it – Alan Macdonald Feb 02 '16 at 21:29
  • Great catch, but every child is given at least 1 balloon. Thus this isn't an issue. – Mike Buesing Feb 02 '16 at 22:08

1 Answers1

1
SELECT
    SQ.child_name,
    BC.balloon_color
FROM
    (
    SELECT DISTINCT
        child_name
    FROM
        Child_Balloons
    ) SQ
CROSS JOIN Balloon_Colors BC
WHERE
    NOT EXISTS (
        SELECT *
        FROM Child_Balloons CB
        WHERE
            CB.child_name = SQ.child_name AND
            CB.balloon_color = BC.balloon_color
    )
Tom H
  • 46,766
  • 14
  • 87
  • 128
  • Cross Joins are new to me. But I think this might be my answer! I'll test it and let ya'll know. Thanks Tom H, for the sql query. – Mike Buesing Feb 02 '16 at 21:29
  • A `CROSS JOIN` basically says, "Give me every possible combination of rows between the two tables." They aren't used a lot, because you usually don't want to explode data out like that. This sort of problem is one of the exceptions because you want to start with every combination of children/balloon colors to see which are then missing. – Tom H Feb 02 '16 at 21:34
  • Hackerman, you're missing Bob, from you results. This is my issue. Tom H, thanks. Yes, it's a lot of data and in my results, I need to pair it down some, but this is huge step in the right direction. – Mike Buesing Feb 02 '16 at 21:58
  • 1
    @hackerman and Tom H...THANK YOU!!! With both your explainations and answers, I was able to get my desired result set! – Mike Buesing Feb 03 '16 at 00:34