Select coalesce(t1.NUMPAT, t2.NUMPAT) as NumPat, t1.TRACT as Tract1, t2.TRACT as Tract2, t1.ItemA, t2.ItemB
FROM Table1 t1
FULL JOIN Table2 t2 on t1.NUMPAT = t2.NUMPAT and t1.TRACT = t2.TRACT

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Alejandro Garcia
Sent: 6. september 2007 15:01
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Full join

Yes, it's better showing an example, I'm joining on NUMPAT and TRACT, and I need as a resultset a NUMPAT column showing all NUMPATs and the other columns showing null for those that do not join to anyone:


Table1
NUMPAT TRACT ITEMa
1 100 50
2 134 60
5 200 30

Table2
NUMPAT TRACT ITEMb
1 100 55
3 126 40
4 140 70
5 100 20


Result set
NUMPAT TRACT1 TRACT2 ITEMa ITEMb
1 100 100 50 55
2 134 null 60 null
3 null 126 null 40
4 null 140 null 70
5 200 null 30 null
5 null 100 null 20








Svein Erling Tysvær <svein.erling.tysvaer@...> escribió:
Huh? I didn't quite understand your question. If you wonder whether it is possible to have a column that return one of several values, then you could do something like COALESCE(t1.NUMPAT, t2.NUMPAT) and it would return NUMPAT of t1 if any, and if this was NULL it would return t2.NUMPAT.

If this doesn't help, think through your question again and ask in a way that makes it easier to understand (e.g. showing some SQL).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Alejandro Garcia
Sent: 6. september 2007 10:24
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Full join

Hi, I'm trying to full join to tables with this structure:
NUMPAT CTRACT DATA ITEM1, ITEM2...
and I'm joining them on NUMPAT and CTRACT but I need all the NUMPAT to appear at the rigth of the result, now when I full join them the first table NUMPATs appear at the right and the rest appear with null value because they do not join to any of the second table, but I need them all to appear at the rigth of the result..could this be done? thanks in advance