Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: SQL Help - Retrieving rows not in a joining table

    I wonder if you can help...

    I have a simple setup: 2 tables and a joining table, and want to retrieve a data set showing every possible combination of table A and table B together with whether that combination actually exists in the joining table or not.

    My tables:

    channels
    ======
    channel_id
    channel_name

    items
    ====
    item_id
    item_name

    channels_items (joining table)
    ===========
    channel_id
    item_id
    created

    An example of the dataset I want (assuming 2 items and 2 channels, with itemA not being in channelB):

    Code:
    item_id item_name channel_id channel_name exists
    ======= ========= ========== ============ ======
    1       ItemA     1          ChannelA     True
    2       ItemB     1          ChannelA     True
    1       ItemA     2          ChannelB     False
    2       ItemB     2          ChannelB     True
    I'm completely stuck on how to achieve this. Any guidance would be very much appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Psuedo code might be something like:
    A derived table creating a cartesian product (i.e. using a cross join) of items and channels. Left outer join this derived table to channels_items and then just create a CASE statement to complete your exists column, testing whether or not there is a corresponding row in channels_items.

    That enough to get you started?

  3. #3
    Join Date
    Dec 2007
    Posts
    2
    Thanks pootle flump! That's exactly what I needed.

Posting Permissions

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