Posted by dhek on 06/08/05 18:22

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> skrev i en
meddelelse news:1118166259.908268.240690@g47g2000cwa.googlegroups.com...
> Without DDL and sample data to go on I can only guess at your
> requirements:
>
> SELECT x.col1, x.col2,
> COALESCE(y.col3, z.col3), COALESCE(y.col4, z.col4)
> FROM x
> LEFT JOIN y
> ON x.??? = y.??? /* unspecified */
> LEFT JOIN z
> ON x.??? = z.??? /* unspecified */
>
> SELECT @@ROWCOUNT

First of all thanx for your reply. I'll now try to be more specific.

3 Tables: X, Y, Z

I want to be able to view/get for instance 10 specific fields from table X.
Also I want to get fields from either table Y or Z depending on the value of
one of the 10 fields in table X (i.e. X.depend) - if it contains data, then
I want to display i.e. 5 fields from table Y, else I want to display i.e. 5
fields from table Z. So in total I want to always display 15 fields for a
given record. The 5 fields in Y and Z have identical names and contain
address information (one is private/home the other is work).

Table X, the main table, contains a unique number (X.uN) that both Y and Z
also contains a copy of, so thats how they can be bound together.

It is only records that contains a value (i.e. X.constrain) that should be
shown/pop up.

I want the results to be ordered by either the field Y.orderBy og Z.orderBy
depending on which one i displayed according to above logic/requirement.

I would like to display the total number of records/hits.

[Back to original message]