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

    Unanswered: Multiple joins to a single table

    The system I'm working with (Talis library management system) has a single table called TYPE_STATUS that contains the descriptive names of attributes that are stored as id codes in other tables.

    I want to create a query that will output all borrower type / item type / loan type combinations in the LOAN_RULE table, along with a count of how many times each combination appears. This is easy enough to do as a list of codes, but more difficult if I want to make it human-readable.

    What I've tried doing is including the TYPE_STATUS table three times, using different aliases, in the hope that the system will then treat the aliases as different tables:

    select BTYPE.NAME as BORROWER, ITYPE.NAME as ITEM, LTYPE.NAME as LOAN, Count(*)
    from TYPE_STATUS BTYPE, TYPE_STATUS ITYPE, TYPE_STATUS LTYPE, LOAN_RULE
    where BTYPE.TYPE_STATUS = LOAN_RULE.BORROWER_TYPE
    and BTYPE.SUB_TYPE = 2
    and ITYPE.TYPE_STATUS = LOAN_RULE.ITEM_TYPE
    and ITYPE.SUB_TYPE = 1
    and LTYPE.TYPE_STATUS = LOAN_RULE.LOAN_TYPE
    and LTYPE.SUB_TYPE = 24
    group by BORROWER, ITEM, LOAN

    However, the system just produces nothing (not even an error message). Can anyone point out where my logic is going screwy, please.

    Thanks,
    Rob

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Hmm. You might want to try one join at a time and then keep adding and see why you are not getting the data.

  3. #3
    Join Date
    Dec 2007
    Posts
    4
    Thanks for the reply.

    Actually, I found that my mistake was quite simple - Sybase doesn't allow you to group by a column alias. I had to change the final line to:

    group by BTYPE.NAME, ITYPE.NAME, LTYPE.NAME

    ...and it worked!

Posting Permissions

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