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
7  6208 
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
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.
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.
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.
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
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
'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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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 ...
|
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
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
