473,372 Members | 947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

SQL Full Outer Join

I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =
dbo.tSymGrp.sgexID FULL OUTER JOIN
dbo.tSymMain ON dbo.tSymGrp.sgID =
dbo.tSymMain.smsgID

Which produces:
exCode sgCode smCode
CBOT CBOTTRS CB2N
CBOT CBOTTRS CB30B
CBOT CBOTTRS CB10N
CBOT CBOTTRS CB5N
CBOT CBOTEQ
CME

I want it to produce:
exCode sgCode smCode
CBOT CBOTTRS CB2N
CBOT CBOTTRS CB30B
CBOT CBOTTRS CB10N
CBOT CBOTTRS CB5N
CBOT CBOTTRS
CBOT CBOTEQ
CBOT
CME
Jul 20 '05 #1
7 6204
alexcn (al****@writeme.com) writes:
I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =
dbo.tSymGrp.sgexID FULL OUTER JOIN
dbo.tSymMain ON dbo.tSymGrp.sgID =
dbo.tSymMain.smsgID


I assume that you want to do a FULL OUTER JOIN all three tables. The
above does not cut it, becasuse when you come to the third table,
SymGrp.sgID may NULL for some rows and you would like to SymExch.exID
instead. You could use a coalesce, but I'm uncertain what the performance
may be.

Here is an outline:

SELECT ...
FROM (SELECT pkcol = coalesce(a.pkcol, b.pkcol)
FROM a
FULL JOIN b ON a.pkcol, b.pkcol) AS ab
FULL JOIN c ON ab.pkcol = c.pkcol

Other columns can be added to the derived table, and then used in
the outer SELECT.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Hi there,

While I appreciate your kind offer, I havent a clue what you mean by
using a coalesce and cant even begin to workout how to translate into
a possible solution for my problem.

Thanks anyway Erland!

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>...
alexcn (al****@writeme.com) writes:
I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =
dbo.tSymGrp.sgexID FULL OUTER JOIN
dbo.tSymMain ON dbo.tSymGrp.sgID =
dbo.tSymMain.smsgID


I assume that you want to do a FULL OUTER JOIN all three tables. The
above does not cut it, becasuse when you come to the third table,
SymGrp.sgID may NULL for some rows and you would like to SymExch.exID
instead. You could use a coalesce, but I'm uncertain what the performance
may be.

Here is an outline:

SELECT ...
FROM (SELECT pkcol = coalesce(a.pkcol, b.pkcol)
FROM a
FULL JOIN b ON a.pkcol, b.pkcol) AS ab
FULL JOIN c ON ab.pkcol = c.pkcol

Other columns can be added to the derived table, and then used in
the outer SELECT.

Jul 20 '05 #3
However I thought this DDL might be useful as someone else asked for
more info. Essentially I am trying to create a tree of the three
tables in a combo box, so by selecting a level i.e. SymExch, it would
infer every SymGrp in under that level, and every SymMain within each
of those SymGrp's and so on.

CREATE TABLE [dbo].[tSymExch] (
[exID] [int] IDENTITY (1, 1) NOT NULL ,
[exCode] [nvarchar] (50) NULL ,
[exDesc] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tSymGrp] (
[sgID] [int] IDENTITY (1, 1) NOT NULL ,
[sgexID] [int] NULL ,
[sgCode] [nvarchar] (10) NULL ,
[sgName] [nvarchar] (50) NULL ,
[sgAddUser] [varchar] (50) NULL ,
[sgAddTime] [datetime] NULL ,
[sgModUser] [varchar] (50) NULL ,
[sgModTime] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tSymMain] (
[smID] [int] IDENTITY (1, 1) NOT NULL ,
[smsgID] [int] NULL ,
[smsmaID] [int] NULL ,
[smCode] [nvarchar] (10) NULL ,
[smDesc] [nvarchar] (100) NULL ,
[smLookup] [nvarchar] (10) NULL ,
[smCSize] [float] NULL ,
[smPSize] [float] NULL ,
[smAddUser] [nvarchar] (10) NULL ,
[smAddTime] [datetime] NULL ,
[smModUser] [nvarchar] (10) NULL ,
[smModTime] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(1,1,1,'EUBUND','EUREX
- Bund','FGBL',1.000000000000000e+001,1.000000000000 000e+001,NULL,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(2,1,2,'EUBOBL','EUREX
- Bobl','FGBM',1.000000000000000e+001,1.000000000000 000e+001,NULL,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(3,1,4,'EUSHTZ','EUREX
- Schatz','FGBS',1.000000000000000e+001,1.0000000000 00000e+001,NULL,NULL,NULL,NU
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(4,2,5,'EUESTX','EUREX
- EuroStoxx','FESX',1.000000000000000e+001,1.0000000 00000000e+001,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(5,2,NULL,'EUDSTX','EUREX
- DJStoxx','FSTX',1.000000000000000e+001,1.000000000 000000e+001,NULL,NULL,NUL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(6,4,NULL,'LFEBOR','LIFFE
- Euribor','I',1.000000000000000e+001,1.000000000000 000e+001,NULL,NULL,NULL,N
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(7,3,NULL,'LFGILT','LIFFE
- Long Gilt','GILT',1.000000000000000e+001,1.000000000000 000e+001,NULL,NULL,N
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(8,4,NULL,'LFSTER','LIFFE
- Short Sterling','L',2.500000000000000e+001,1.00000000000 0000e+001,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(9,7,NULL,'LFFTSE','LIFFE
- FTSE','Z',1.000000000000000e+001,1.000000000000000 e+001,NULL,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(10,1,3,'ERFDAX','EUREX
- DAX','FDAX',2.500000000000000e+001,1.0000000000000 00e+001,NULL,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(11,8,NULL,'LFSWAP','LIFFE
- SWAP Note','P',1.000000000000000e+001,1.000000000000000 e+001,NULL,NULL,NUL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(12,5,NULL,'CB30B','CBOT
- 30-year Bond','ZB',3.125000000000000e+001,1.00000000000000 0e+001,NULL,NULL,N
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(13,5,NULL,'CB10N','CBOT
- 10-year Note','ZN',1.562500000000000e+001,1.00000000000000 0e+001,NULL,NULL,N
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(14,5,NULL,'CB5N','CBOT
- 5-year Note','ZF',1.562500000000000e+001,1.00000000000000 0e+001,NULL,NULL,NUL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(15,5,NULL,'CB2N','CBOT
- 2-year Note','ZT',1.562500000000000e+001,1.00000000000000 0e+001,NULL,NULL,NUL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(16,2,NULL,'EUSMI','EUREX
- SMI','FSMI',1.000000000000000e+001,1.0000000000000 00e+001,NULL,NULL,NULL,NU
INSERT INTO [tSymExch]
([exID],[exCode],[exDesc])VALUES(1,'EURDE','EUREX Frankfurt Products')
INSERT INTO [tSymExch]
([exID],[exCode],[exDesc])VALUES(2,'ENXLO','Euronext-LIFFE Products')
INSERT INTO [tSymExch] ([exID],[exCode],[exDesc])VALUES(3,'CBOT','CBOT
Products')
INSERT INTO [tSymExch] ([exID],[exCode],[exDesc])VALUES(4,'CME','CME
Products')
INSERT INTO [tSymExch]
([exID],[exCode],[exDesc])VALUES(5,'ENXPA','Euronext-Paris Products')
INSERT INTO [tSymExch]
([exID],[exCode],[exDesc])VALUES(6,'EURUS','EUREX US Products')
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(1,1,'EURDEBOND','EUREX
DE - Bonds',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(2,1,'EURDEEQIX','EUREX
DE - Equity Indices',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(3,2,'ENXLOBOND','Euronext
London - Bonds',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(4,2,'ENXLOSTIR','Euronext
London - STIRs',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(5,3,'CBOTTRS','CBOT
- Treasury',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(6,3,'CBOTEQ','CBOT
- Equity',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(7,2,'ENXLOEQIX','Euronext
London - Equity Indices',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(8,2,'ENXLOSWAP','Euronext
London - Swaps',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(9,2,'ENXLOEQTY','Euronext
London - Equity Shares',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(10,1,'EURDEEQTY','EUREX
DE - Equity Shares',NULL,NULL,NULL,NULL)

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>...
alexcn (al****@writeme.com) writes:
I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =
dbo.tSymGrp.sgexID FULL OUTER JOIN
dbo.tSymMain ON dbo.tSymGrp.sgID =
dbo.tSymMain.smsgID


I assume that you want to do a FULL OUTER JOIN all three tables. The
above does not cut it, becasuse when you come to the third table,
SymGrp.sgID may NULL for some rows and you would like to SymExch.exID
instead. You could use a coalesce, but I'm uncertain what the performance
may be.

Here is an outline:

SELECT ...
FROM (SELECT pkcol = coalesce(a.pkcol, b.pkcol)
FROM a
FULL JOIN b ON a.pkcol, b.pkcol) AS ab
FULL JOIN c ON ab.pkcol = c.pkcol

Other columns can be added to the derived table, and then used in
the outer SELECT.

Jul 20 '05 #4
However I thought this DDL might be useful as someone else asked for
more info. Essentially I am trying to create a tree of the three
tables in a combo box, so by selecting a level i.e. SymExch, it would
infer every SymGrp in under that level, and every SymMain within each
of those SymGrp's and so on.

CREATE TABLE [dbo].[tSymExch] (
[exID] [int] IDENTITY (1, 1) NOT NULL ,
[exCode] [nvarchar] (50) NULL ,
[exDesc] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tSymGrp] (
[sgID] [int] IDENTITY (1, 1) NOT NULL ,
[sgexID] [int] NULL ,
[sgCode] [nvarchar] (10) NULL ,
[sgName] [nvarchar] (50) NULL ,
[sgAddUser] [varchar] (50) NULL ,
[sgAddTime] [datetime] NULL ,
[sgModUser] [varchar] (50) NULL ,
[sgModTime] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tSymMain] (
[smID] [int] IDENTITY (1, 1) NOT NULL ,
[smsgID] [int] NULL ,
[smsmaID] [int] NULL ,
[smCode] [nvarchar] (10) NULL ,
[smDesc] [nvarchar] (100) NULL ,
[smLookup] [nvarchar] (10) NULL ,
[smCSize] [float] NULL ,
[smPSize] [float] NULL ,
[smAddUser] [nvarchar] (10) NULL ,
[smAddTime] [datetime] NULL ,
[smModUser] [nvarchar] (10) NULL ,
[smModTime] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(1,1,1,'EUBUND','EUREX
- Bund','FGBL',1.000000000000000e+001,1.000000000000 000e+001,NULL,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(2,1,2,'EUBOBL','EUREX
- Bobl','FGBM',1.000000000000000e+001,1.000000000000 000e+001,NULL,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(3,1,4,'EUSHTZ','EUREX
- Schatz','FGBS',1.000000000000000e+001,1.0000000000 00000e+001,NULL,NULL,NULL,NU
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(4,2,5,'EUESTX','EUREX
- EuroStoxx','FESX',1.000000000000000e+001,1.0000000 00000000e+001,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(5,2,NULL,'EUDSTX','EUREX
- DJStoxx','FSTX',1.000000000000000e+001,1.000000000 000000e+001,NULL,NULL,NUL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(6,4,NULL,'LFEBOR','LIFFE
- Euribor','I',1.000000000000000e+001,1.000000000000 000e+001,NULL,NULL,NULL,N
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(7,3,NULL,'LFGILT','LIFFE
- Long Gilt','GILT',1.000000000000000e+001,1.000000000000 000e+001,NULL,NULL,N
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(8,4,NULL,'LFSTER','LIFFE
- Short Sterling','L',2.500000000000000e+001,1.00000000000 0000e+001,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(9,7,NULL,'LFFTSE','LIFFE
- FTSE','Z',1.000000000000000e+001,1.000000000000000 e+001,NULL,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(10,1,3,'ERFDAX','EUREX
- DAX','FDAX',2.500000000000000e+001,1.0000000000000 00e+001,NULL,NULL,NULL,NULL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(11,8,NULL,'LFSWAP','LIFFE
- SWAP Note','P',1.000000000000000e+001,1.000000000000000 e+001,NULL,NULL,NUL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(12,5,NULL,'CB30B','CBOT
- 30-year Bond','ZB',3.125000000000000e+001,1.00000000000000 0e+001,NULL,NULL,N
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(13,5,NULL,'CB10N','CBOT
- 10-year Note','ZN',1.562500000000000e+001,1.00000000000000 0e+001,NULL,NULL,N
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(14,5,NULL,'CB5N','CBOT
- 5-year Note','ZF',1.562500000000000e+001,1.00000000000000 0e+001,NULL,NULL,NUL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(15,5,NULL,'CB2N','CBOT
- 2-year Note','ZT',1.562500000000000e+001,1.00000000000000 0e+001,NULL,NULL,NUL
INSERT INTO [tSymMain]
([smID],[smsgID],[smsmaID],[smCode],[smDesc],[smLookup],[smCSize],[smPSize],[smAddUser],[smAddTime],[smModUser],[smModTime])VALUES(16,2,NULL,'EUSMI','EUREX
- SMI','FSMI',1.000000000000000e+001,1.0000000000000 00e+001,NULL,NULL,NULL,NU
INSERT INTO [tSymExch]
([exID],[exCode],[exDesc])VALUES(1,'EURDE','EUREX Frankfurt Products')
INSERT INTO [tSymExch]
([exID],[exCode],[exDesc])VALUES(2,'ENXLO','Euronext-LIFFE Products')
INSERT INTO [tSymExch] ([exID],[exCode],[exDesc])VALUES(3,'CBOT','CBOT
Products')
INSERT INTO [tSymExch] ([exID],[exCode],[exDesc])VALUES(4,'CME','CME
Products')
INSERT INTO [tSymExch]
([exID],[exCode],[exDesc])VALUES(5,'ENXPA','Euronext-Paris Products')
INSERT INTO [tSymExch]
([exID],[exCode],[exDesc])VALUES(6,'EURUS','EUREX US Products')
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(1,1,'EURDEBOND','EUREX
DE - Bonds',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(2,1,'EURDEEQIX','EUREX
DE - Equity Indices',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(3,2,'ENXLOBOND','Euronext
London - Bonds',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(4,2,'ENXLOSTIR','Euronext
London - STIRs',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(5,3,'CBOTTRS','CBOT
- Treasury',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(6,3,'CBOTEQ','CBOT
- Equity',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(7,2,'ENXLOEQIX','Euronext
London - Equity Indices',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(8,2,'ENXLOSWAP','Euronext
London - Swaps',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(9,2,'ENXLOEQTY','Euronext
London - Equity Shares',NULL,NULL,NULL,NULL)
INSERT INTO [tSymGrp]
([sgID],[sgexID],[sgCode],[sgName],[sgAddUser],[sgAddTime],[sgModUser],[sgModTime])VALUES(10,1,'EURDEEQTY','EUREX
DE - Equity Shares',NULL,NULL,NULL,NULL)

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>...
alexcn (al****@writeme.com) writes:
I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =
dbo.tSymGrp.sgexID FULL OUTER JOIN
dbo.tSymMain ON dbo.tSymGrp.sgID =
dbo.tSymMain.smsgID


I assume that you want to do a FULL OUTER JOIN all three tables. The
above does not cut it, becasuse when you come to the third table,
SymGrp.sgID may NULL for some rows and you would like to SymExch.exID
instead. You could use a coalesce, but I'm uncertain what the performance
may be.

Here is an outline:

SELECT ...
FROM (SELECT pkcol = coalesce(a.pkcol, b.pkcol)
FROM a
FULL JOIN b ON a.pkcol, b.pkcol) AS ab
FULL JOIN c ON ab.pkcol = c.pkcol

Other columns can be added to the derived table, and then used in
the outer SELECT.

Jul 20 '05 #5
Okay it looks like you want your query to do two things:
- generate a row (exCode, sgCode, smCode) for every
tSymExch/tSymGrp/tSymMain joined by exId and sgId
- independently, generate a row (exCode, sgCode, NULL) for every
tSymExch/tSymGrp joined by exId
- independently, generate a row (exCode, NULL) for every tSymExch.
That would be the kind of thing someone would do in a
traditional report with break totals or headings. Did I get this right?

In that case, what you want is:

SELECT tSymExch.exCode, tSymGrp.sgCode, tSymMain.smCode
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
JOIN dbo.t.SymMain ON tSymGrp.sgID = tSymMain.smsgID
UNION ALL
SELECT tSymExch.exCode, tSymGrp.sgCode, NULL
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
UNION ALL
SELECT tSymExch.exCode, NULL, NULL
FROM dbo.tSymExch

If you want to get selective about WHICH tSymExch rows
(and children) are returned, I suggest you wrap that all in a nested query,
something like:

SELECT *
FROM (
SELECT tSymExch.exCode, tSymGrp.sgCode, tSymMain.smCode
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
JOIN dbo.t.SymMain ON tSymGrp.sgID = tSymMain.smsgID
UNION ALL
SELECT tSymExch.exCode, tSymGrp.sgCode, NULL
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
UNION ALL
SELECT tSymExch.exCode, NULL, NULL
FROM dbo.tSymExch
) AS ExGrMn
WHERE ExGrMn.exCode LIKE 'CB%'

"alexcn" <al****@writeme.com> wrote in message
news:e1**************************@posting.google.c om...
I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =
dbo.tSymGrp.sgexID FULL OUTER JOIN
dbo.tSymMain ON dbo.tSymGrp.sgID =
dbo.tSymMain.smsgID

Which produces:
exCode sgCode smCode
CBOT CBOTTRS CB2N
CBOT CBOTTRS CB30B
CBOT CBOTTRS CB10N
CBOT CBOTTRS CB5N
CBOT CBOTEQ
CME

I want it to produce:
exCode sgCode smCode
CBOT CBOTTRS CB2N
CBOT CBOTTRS CB30B
CBOT CBOTTRS CB10N
CBOT CBOTTRS CB5N
CBOT CBOTTRS
CBOT CBOTEQ
CBOT
CME

Jul 20 '05 #6
Hi Mischa,

Thank you for your support, the union solution you suggested was my
failsafe solution but I seem to remember someone telling me that UNION
queries like that are frowned upon. However, it does give me exactly
what I need, perhaps this is the only way to achieve the desired
results but I was wondering if there was a cleaner way?

Many thanks once again!

Alex

"Mischa Sandberg" <mi*************@telus.net> wrote in message news:<mVhNc.65614$iw3.57870@clgrps13>...
Okay it looks like you want your query to do two things:
- generate a row (exCode, sgCode, smCode) for every
tSymExch/tSymGrp/tSymMain joined by exId and sgId
- independently, generate a row (exCode, sgCode, NULL) for every
tSymExch/tSymGrp joined by exId
- independently, generate a row (exCode, NULL) for every tSymExch.
That would be the kind of thing someone would do in a
traditional report with break totals or headings. Did I get this right?

In that case, what you want is:

SELECT tSymExch.exCode, tSymGrp.sgCode, tSymMain.smCode
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
JOIN dbo.t.SymMain ON tSymGrp.sgID = tSymMain.smsgID
UNION ALL
SELECT tSymExch.exCode, tSymGrp.sgCode, NULL
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
UNION ALL
SELECT tSymExch.exCode, NULL, NULL
FROM dbo.tSymExch

If you want to get selective about WHICH tSymExch rows
(and children) are returned, I suggest you wrap that all in a nested query,
something like:

SELECT *
FROM (
SELECT tSymExch.exCode, tSymGrp.sgCode, tSymMain.smCode
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
JOIN dbo.t.SymMain ON tSymGrp.sgID = tSymMain.smsgID
UNION ALL
SELECT tSymExch.exCode, tSymGrp.sgCode, NULL
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
UNION ALL
SELECT tSymExch.exCode, NULL, NULL
FROM dbo.tSymExch
) AS ExGrMn
WHERE ExGrMn.exCode LIKE 'CB%'

"alexcn" <al****@writeme.com> wrote in message
news:e1**************************@posting.google.c om...
I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =
dbo.tSymGrp.sgexID FULL OUTER JOIN
dbo.tSymMain ON dbo.tSymGrp.sgID =
dbo.tSymMain.smsgID

Which produces:
exCode sgCode smCode
CBOT CBOTTRS CB2N
CBOT CBOTTRS CB30B
CBOT CBOTTRS CB10N
CBOT CBOTTRS CB5N
CBOT CBOTEQ
CME

I want it to produce:
exCode sgCode smCode
CBOT CBOTTRS CB2N
CBOT CBOTTRS CB30B
CBOT CBOTTRS CB10N
CBOT CBOTTRS CB5N
CBOT CBOTTRS
CBOT CBOTEQ
CBOT
CME

Jul 20 '05 #7
'Frowned on'? 'Cleaner way'? :-)

Even relational theorists can only work up a tiny snit
over how SQL implements UNION/UNION ALL.
It's perfectly correct, is handled properly and efficiently
by any query engine you're likely to deal with.

More to the point, it is precisely the correct way,
and the most concise way, to generate what you requested.
Best of all, it is straightforward to understand.
Any sort of single three-table join would be a contrived answer,
with much less chance of an efficient query plan.

Sorry if this sounds a bit flamey.
I've spent too long solving DB problems,
and unquantifiable aesthetic comments about solutions
get to be a bit wearing. Thanks for listening.

"alexcn" <al****@writeme.com> wrote in message
news:e1**************************@posting.google.c om...
Hi Mischa,

Thank you for your support, the union solution you suggested was my
failsafe solution but I seem to remember someone telling me that UNION
queries like that are frowned upon. However, it does give me exactly
what I need, perhaps this is the only way to achieve the desired
results but I was wondering if there was a cleaner way?

Many thanks once again!

Alex

"Mischa Sandberg" <mi*************@telus.net> wrote in message

news:<mVhNc.65614$iw3.57870@clgrps13>...
Okay it looks like you want your query to do two things:
- generate a row (exCode, sgCode, smCode) for every
tSymExch/tSymGrp/tSymMain joined by exId and sgId
- independently, generate a row (exCode, sgCode, NULL) for every
tSymExch/tSymGrp joined by exId
- independently, generate a row (exCode, NULL) for every tSymExch.
That would be the kind of thing someone would do in a
traditional report with break totals or headings. Did I get this right?

In that case, what you want is:

SELECT tSymExch.exCode, tSymGrp.sgCode, tSymMain.smCode
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
JOIN dbo.t.SymMain ON tSymGrp.sgID = tSymMain.smsgID
UNION ALL
SELECT tSymExch.exCode, tSymGrp.sgCode, NULL
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
UNION ALL
SELECT tSymExch.exCode, NULL, NULL
FROM dbo.tSymExch

If you want to get selective about WHICH tSymExch rows
(and children) are returned, I suggest you wrap that all in a nested query, something like:

SELECT *
FROM (
SELECT tSymExch.exCode, tSymGrp.sgCode, tSymMain.smCode
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
JOIN dbo.t.SymMain ON tSymGrp.sgID = tSymMain.smsgID
UNION ALL
SELECT tSymExch.exCode, tSymGrp.sgCode, NULL
FROM dbo.tSymExch
JOIN dbo.tSymGrp ON tSymExch.exID = tSymGr.sgexID
UNION ALL
SELECT tSymExch.exCode, NULL, NULL
FROM dbo.tSymExch
) AS ExGrMn
WHERE ExGrMn.exCode LIKE 'CB%'

"alexcn" <al****@writeme.com> wrote in message
news:e1**************************@posting.google.c om...
I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =
dbo.tSymGrp.sgexID FULL OUTER JOIN
dbo.tSymMain ON dbo.tSymGrp.sgID =
dbo.tSymMain.smsgID

Which produces:
exCode sgCode smCode
CBOT CBOTTRS CB2N
CBOT CBOTTRS CB30B
CBOT CBOTTRS CB10N
CBOT CBOTTRS CB5N
CBOT CBOTEQ
CME

I want it to produce:
exCode sgCode smCode
CBOT CBOTTRS CB2N
CBOT CBOTTRS CB30B
CBOT CBOTTRS CB10N
CBOT CBOTTRS CB5N
CBOT CBOTTRS
CBOT CBOTEQ
CBOT
CME

Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dave | last post by:
Hi I have the following 4 tables and I need to do a fully outerjoin on them. create table A (a number, b number, c char(10), primary key (a,b)) create table B (a number, b number, c ...
0
by: reneecccwest | last post by:
SELECT d.code, d.description, v.code AS divCode, v.descripton AS divDescript, b.code AS brhCode, b.description AS brhDescript FROM Department d FULL OUTER JOIN Division v
4
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
2
by: Lee | last post by:
I have two identical schema tables (one in a production library and another in a development library) and I need to be able to easily compare the data in them for errors and omissions. With...
2
by: Cory | last post by:
i have two tables, Event1 and Event2. They both have fields called contactID and eventID in them. Some of the contactID's between the tables are equal and some are not. I want to do a full outer...
1
by: Martijn van Oosterhout | last post by:
Today I got the error: ERROR: FULL JOIN is only supported with mergejoinable join conditions Which is really annoying since a full join is exactly what I wanted. I guess the alternative is to...
20
by: FloggingDolphin | last post by:
Hi there, I'm kinda new to this so I hope I've explained it properly. Please ask if you need any additional info. Here is a simplified version of the issue I'm experiencing: I have two...
1
by: shilpasharma | last post by:
Hi, Can anybody let me know how I can optimise following Query. Select * from reports where ( exists ( SELECT 1 FROM results_required rr, item_claims_trials ict, results res WHERE...
0
by: brunodamato | last post by:
In the example that follows, I am receiving an incorrect result set in the View. I am looking to get the FULL result set from View_LY and Table_TY. Instead, this View is returning the Common (Equi)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.