T-SQL Challenge #1 (re-released) – Pair-wise and ordered assignment of objects from two different lists

This puzzle is part of the TSQL Challenge contest.

Author: Jacob Sebastion

Introduction

This challenge will be interesting for TSQL enthusiasts as well as bird lovers! It involves assigning food items to birds from two different baskets. Here is a ‘relational’ representation of birds, food items and baskets in the form of tables and rows.

Table Birds lists the birds which are the recipients of food items. The baskets containing the food items are the tables Grains and Fruits. Whenever possible, you must assign the food items in pairs and they must be taken from each table in alphabetical order. When one of the tables no longer has a food item for a bird you must output a null in the corresponding column and continue assigning food items from the other table until that one runs out of food baskets as well.

Sample Data

Birds Table

Code Name
---- -------
1    Pigeon
2    Sparrow
3    Parrot

Grains Table

Code Grain
---- ------
1    Wheat
1    Rice
2    Corn
2    Millet

Fruits Table

Code Fruit
---- ------
1    Banana
1    Mango
1    Guava
2    Grapes

Expected Results

Code Bird    Grain  Fruit
---- ------- ------ ------
1    Pigeon  Rice   Banana
1    Pigeon  Wheat  Guava
1    Pigeon  NULL   Mango
2    Sparrow Corn   Grapes
2    Sparrow Millet NULL
3    Parrot  NULL   NULL

Rules

  1. For a bird with no food basket at all, a single line should be output with the Grain and Fruit columns containing null.
  2. The output should be ordered by Code followed by the order in which the Grain/Fruit pairs were extracted from the Grains/Fruits tables.

Sample Script

IF OBJECT_ID('TC1_BIRDS','U') IS NOT NULL BEGIN
    DROP TABLE TC1_BIRDS
END
GO

CREATE TABLE TC1_BIRDS(
    Code INT, 
    Name VARCHAR(10)
)
GO

INSERT INTO TC1_BIRDS(Code,Name)
SELECT 1,'Pigeon'  UNION ALL
SELECT 2,'Sparrow' UNION ALL
SELECT 3,'Parrot'
GO

IF OBJECT_ID('TC1_GRAINS','U') IS NOT NULL BEGIN
    DROP TABLE TC1_GRAINS
END
GO

CREATE TABLE TC1_GRAINS(
    Code INT, 
    Grain VARCHAR(10)
)
GO

INSERT INTO TC1_GRAINS(Code,Grain)
SELECT 1,'Wheat'  UNION ALL
SELECT 1,'Rice'  UNION ALL
SELECT 2,'Corn' UNION ALL
SELECT 2,'Millet'

IF OBJECT_ID('TC1_FRUITS','U') IS NOT NULL BEGIN
    DROP TABLE TC1_FRUITS
END
GO

CREATE TABLE TC1_FRUITS(
    Code INT, 
    Fruit VARCHAR(10)
)
GO

INSERT INTO TC1_FRUITS(Code,Fruit)
SELECT 1,'Banana'  UNION ALL
SELECT 1,'Mango'  UNION ALL
SELECT 1,'Guava' UNION ALL
SELECT 2,'Grapes'

Restrictions

  1. The solution should be a single query that starts with a “SELECT” or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.