Help! Complicated SQL Statement

Help! Complicated SQL Statement

Post by Sloa » Fri, 19 Jan 2001 05:34:14

Hi All!

I've got  a problem matching up lists.  The lists are help in a pair of
tables, Share_Lists and Share_List_Items.  Records in Share_Lists identify
individual lists, and Share_List_Items hold the items in those lists.  The
two lists both contain a common field (date) that I'd like to match up.

I have another table, Fund which has two fields, each containing a key for a
Share (a fourth table)

The lists in Share_Lists are tied to records in Share.  So that a particular
Share record can have multiple Share_Lists (indentified by List_Type)

Now for the problem.  The two lists pointed to indirectly from the Fund
table through the Share and Share_Lists tables have different lengths (i.e.
number of items)  I need to return an equal number of items for each list,
using nulls for missing items (simple inner join, right?)  It works fine
when the first list is the longer one, but doesn't work when the second list
is the longer one (it returns only as many records as is found in the first
(shorter) list)

The select is something like:

SELECT Fund_Key,
    IsNull(yb.ShareListItem_Date1,xb.ShareListItem_Date1) as DateVal1,
    IsNull(yb.ShareListItem_Val1,0) as PointVal1,
    IsNull(xb.ShareListItem_Date1,yb.ShareListItem_Date1) as DateVal2,
    IsNull(xb.ShareListItem_Val1,0) as PointVal2
  FROM Funds z
    LEFT JOIN Share y ON z.Share_Key = z.List1Key
    LEFT JOIN Share_Lists ya ON ya.ShareList_ShareKey = t.Share_Key and
    LEFT JOIN Share_List_Items ON yb.ShareListItem_ListKey =
    LEFT JOIN Share x ON x.Share_Key = z.List2Key
    LEFT JOIN Share_Lists xa ON xa.ShareList_ShareKey = t.Share_Key and
    LEFT JOIN Share_List_Items ON xb.ShareListItem_ListKey =
        AND xb.ShareListItem_Date1 = yb.ShareListItem_Date1
WHERE Fund_Key = 3275

For example, it might return:

3275    01/01/1990    73.323    01/01/1990    0
3275    02/01/2990    74.543    02/01/1990    0
3275    03/01/2990    76.221    03/01/1990    53.45
3275    04/01/2990    79.754    04/01/1990    54.30
3275    05/01/2990    78.413    05/01/1990    55.75

Indicating the the first list had five items and the second had three.  That
part works.

It's the reverse situation that doesn't work, and I won't know which it is
before hand (can't write two and then choose, trust me)

If the first list is shorter, I only get the number of items in the first

Any ideas???

Thanks in advance!



Help! Complicated SQL Statement

Post by Visual Drag » Sun, 21 Jan 2001 14:15:53

Quote:>Any ideas???

>Thanks in advance!


Create another SELECT that has the LEFT JOIN going the other way and then
UNION the two select statements.  This should give you everything
regardless of which list is the longer and UNION will automagically
eliminate the duplicates.

Hope this helps.