Your absolutely right, my question was not clearly
specified. So here's a concrete problem:
Let's say table A has 2 fields: PRODUCT_ID (unique) and
PRODUCT_NAME,
table B has 3 fields: PRODUCT_ID,COMPANY_ID and
LAST_PRODUCTION_DATE,
and finally table C : COMPANY_ID and CONTACT_INFO
Table A is the main table of the query. It contains product
names with unique product ids. Table B has at most 1 entry
for each product id.
(There may be products which have no entries in table B).
Table C contains the company's contact info if available(!).
I want to show the following informations in each row:
product name, last production date (if available) and
producer's contact info (if available).
So, as I see the query should look like this:
SELECT
a.PRODUCT_NAME,b.LAST_PRODUCTION_DATE,c.CONTACT_INFO
FROM A AS a
LEFT JOIN B AS b ON a.PRODUCT_ID=b.PRODUCT_ID
LEFT JOIN C as c ON b.COMPANY_ID=c.COMPANY_ID
..but this is wrong.
Thanks for any suggestions..
Christian Fischer
> > I need some suggestions about a special query. The
situation is:
> >There are (for simplicity) 4 tables: A,B,C and D which
must be joined
> >like this:
> > Between A and B must be an outer-join association
and C and D must
> >be connected to B also with outer-join.
> >But this causes a 'Cross dependency found in OUTER JOIN'
error. I could
> >connect A and B with INNER JOIN but it's useless from my
point of view.
> >I think the problem could be solved with redesigning the
database
> >structure, but it's currently not possible.
>
> You need to specify the outer join. A LEFT OUTER JOIN B is
not the
> same as A RIGHT OUTER JOIN B or B LEFT OUTER JOIN A. When
you have
> nested outer joins, only one of the tables can be
preserved. You need
> to specify which table is being preserved (all rows are
returned).
>
> I recommend posting the table definitions and stating
which columns
> are used in the joins.
>
> > Does somebody know a good (general) solution for
this? Temporary
> >tables could help, but i deal with lot's of data and I'm
afraid it would
> >be very slow.
> Outer joins + lot's of data = slow queries, even without
temp tables.
> Bob Hall
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
