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  6069 
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 discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by Dave |
last post: by
|
reply
views
Thread by reneecccwest |
last post: by
|
4 posts
views
Thread by thilbert |
last post: by
|
2 posts
views
Thread by Lee |
last post: by
|
2 posts
views
Thread by Cory |
last post: by
|
1 post
views
Thread by Martijn van Oosterhout |
last post: by
| | | | | | | | | | | | | | | | | | | | | | | | | |
