|
|
Posted by Erland Sommarskog on 06/09/05 00:55
dhek (dhek@REMOVEtiscali.dk) writes:
> 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.
Something like:
SELECT x.col1, x.col2, ...
yzcol1 = CASE x.depend WHEN 'Y' THEN y.col1 ELSE z.col1 END,
yzcol2 = CASE x.depend WHEN 'Y' THEN y.col2 ELSE z.col2 END,
...
yzorderby = CASE x.depend WHEN 'Y' THEN y.orderby
ELSE x.orderby END,
FROM x
LEFT JOIN y ON x.uN = y.uN
LEFT JOIN z ON x.uN = z.uN
WHERE x.constraint = <value>
ORDER BY CASE yzorderby
If this does not answer your problem, I recommend that you post:
o CREATE TABLE statements for your tables, possibly simplified.
o INSERT statements with sample data.
o The desired result given the sample.
This sort of information helps to clarify what you are asking for, and
makes it simple to develop and test a complete solution.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|