[Robhogg]

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

[trvishi]

Hmm. You might want to try one join at a time and then keep adding and see why you are not getting the data.

[Robhogg]

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!