Sybase Database - 'Cross-product' and join question

Author

'Cross-product' and join question

wgblackmon@yahoo.com

2006-03-27, 1:23 pm

Hi,
I'm designing a query with DBArtisan against a Sybase database. I'm not
a SQL guru but I can usually get the results I need given a good
understanding of the database and the relationships between tables.
However, this problem has me stumped. When I run the following query:

SELECT DISTINCT dbo.T_MULTILIST_GRADE.grade,
dbo.T_MULTILIST.code,
dbo.T_MULTILIST.description,
dbo.T_RECEIVING_DETAIL.amount,
dbo.T_ORDER. requisition_time_sta
mp,
dbo.T_REQUISITION.id,
dbo.T_DEPOSITORY.depository_type,
dbo. T_REQUISITION_DETAIL
.quantity,
dbo.T_RECEIVING_DETAIL.invoice_number,
dbo.T_RECEIVING.status
FROM dbo.T_MULTILIST, dbo.T_MULTILIST_GRADE, dbo.T_REQUISITION,
dbo. T_REQUISITION_DETAIL
, dbo.T_ORDER, dbo.T_DEPOSITORY,
dbo.T_RECEIVING_DETAIL, dbo.T_RECEIVING
WHERE (dbo.T_RECEIVING_DETAIL.invoice_number =
dbo.T_RECEIVING.invoice_number AND
dbo.T_RECEIVING_DETAIL.order_id = dbo.T_ORDER.id AND
dbo.T_ORDER.depository_id = dbo.T_DEPOSITORY.id AND
dbo.T_REQUISITION.id = dbo.T_ORDER.requisition_id AND
dbo. T_REQUISITION_DETAIL
.requisition_id = dbo.T_REQUISITION.id AND
dbo. T_REQUISITION_DETAIL
.multilist_code =
dbo.T_MULTILIST_GRADE.multilist_code AND
dbo.T_MULTILIST_GRADE.multilist_code = dbo.T_MULTILIST.code)

I receive the following 'Warning' from DBArtisan:
'Warning: Table T_RECEIVING_DETAIL is not involved in the join and will
result in the generation of a cross product'

I also get duplicate records, the result of the cross-product. I don't
understand why T_RECEIVING_DETAIL is 'not involved in the join',
however. The data model in this database leaves a lot to be desired.
Any advice would be greatly appreciated.

Thanks,
Bill

Mark A. Parsons

2006-03-27, 8:23 pm

Try running your query via 'isql'. 'isql' won't generate any messages
about cartesian products but you will be able to compare the results.

My guess is that the 'isql' session will give you what you want.

-----------------------------

If you have MDA tables installed in your dataserver you should try to pull
the SQL text of the actual SQL command submitted by DBArtisan (see
master..monSysSQLText).

It would be interesting to see what DBArtisan is passing to the dataserver;
at a minimum the 'SELECT DISTINCT' should be getting rid of duplicates, ie,
the fact that you're getting dup's sounds like DBArtisan is submitting
something (to the dataserver) other than what you've typed.

wgblackmon@yahoo.com wrote:
> Hi,
> I'm designing a query with DBArtisan against a Sybase database. I'm not
> a SQL guru but I can usually get the results I need given a good
> understanding of the database and the relationships between tables.
> However, this problem has me stumped. When I run the following query:
>
> SELECT DISTINCT dbo.T_MULTILIST_GRADE.grade,
> dbo.T_MULTILIST.code,
> dbo.T_MULTILIST.description,
> dbo.T_RECEIVING_DETAIL.amount,
> dbo.T_ORDER. requisition_time_sta
mp,
> dbo.T_REQUISITION.id,
> dbo.T_DEPOSITORY.depository_type,
> dbo. T_REQUISITION_DETAIL
.quantity,
> dbo.T_RECEIVING_DETAIL.invoice_number,
> dbo.T_RECEIVING.status
> FROM dbo.T_MULTILIST, dbo.T_MULTILIST_GRADE, dbo.T_REQUISITION,
> dbo. T_REQUISITION_DETAIL
, dbo.T_ORDER, dbo.T_DEPOSITORY,
> dbo.T_RECEIVING_DETAIL, dbo.T_RECEIVING
> WHERE (dbo.T_RECEIVING_DETAIL.invoice_number =
> dbo.T_RECEIVING.invoice_number AND
> dbo.T_RECEIVING_DETAIL.order_id = dbo.T_ORDER.id AND
> dbo.T_ORDER.depository_id = dbo.T_DEPOSITORY.id AND
> dbo.T_REQUISITION.id = dbo.T_ORDER.requisition_id AND
> dbo. T_REQUISITION_DETAIL
.requisition_id = dbo.T_REQUISITION.id AND
> dbo. T_REQUISITION_DETAIL
.multilist_code =
> dbo.T_MULTILIST_GRADE.multilist_code AND
> dbo.T_MULTILIST_GRADE.multilist_code = dbo.T_MULTILIST.code)
>
> I receive the following 'Warning' from DBArtisan:
> 'Warning: Table T_RECEIVING_DETAIL is not involved in the join and will
> result in the generation of a cross product'
>
> I also get duplicate records, the result of the cross-product. I don't
> understand why T_RECEIVING_DETAIL is 'not involved in the join',
> however. The data model in this database leaves a lot to be desired.
> Any advice would be greatly appreciated.
>
> Thanks,
> Bill
>

--CELKO--

2006-03-28, 11:23 am

First clean up allof those vague, magical column names that are a total
violation of basic data modeling. What kind of code, amount,
description, etc. ? The worst thing looks like the magical uiniversal
"id" in all tables. This is unreadable and you really need to start
over with the DDL.

..

wgblackmon@yahoo.com

2006-03-29, 11:23 am

--CELKO-- wrote:
> First clean up allof those vague, magical column names that are a total
> violation of basic data modeling. What kind of code, amount,
> description, etc. ? The worst thing looks like the magical uiniversal
> "id" in all tables. This is unreadable and you really need to start
> over with the DDL.
>
>
> .
I'm not allowed to change the existing data model or the field names or
anything else (I work for a state agency), so I'm stuck with what I
have. Thanks for the advice!

Sponsored Links