Posted by Plamen Ratchev on 01/24/08 14:57

Perhaps you mean something like this (SQL Server 2005 required):

CREATE TABLE Foo (x CHAR(1));
CREATE TABLE Bar (x CHAR(1), y CHAR(1));

INSERT INTO Foo VALUES ('a');
INSERT INTO Foo VALUES ('b');

INSERT INTO Bar VALUES ('a', NULL);
INSERT INTO Bar VALUES ('b', '1');

SELECT x, y
FROM Foo AS F
CROSS APPLY (SELECT y FROM Bar AS B1 WHERE B1.x = F.x
UNION
SELECT y FROM Bar AS B2 WHERE B2.x = F.x) AS B
WHERE y IS NOT NULL;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]