Without providing the actual DLL and sample data not possible to say why
duplicate is there in your query.
Check whether you missed any join conditions.
Try the following. I have created tables and data.
-- Create temporary tables
CREATE TABLE #TableA (Col1 INT, Col2 INT, Col3 INT)
CREATE TABLE #TableB (Col1 INT, Cola INT, Colb INT)
CREATE TABLE #TableC (Col1 INT, Cola1 INT, Colb1 INT)
-- Insert sample data
INSERT INTO #TableA VALUES (1, 10, 100)
INSERT INTO #TableA VALUES (2, 20, 200)
INSERT INTO #TableA VALUES (3, 30, 300)
INSERT INTO #TableA VALUES (4, 40, 400)
INSERT INTO #TableA VALUES (5, 50, 500)
INSERT INTO #TableB VALUES (2, 50, 600)
INSERT INTO #TableB VALUES (1, 30, 300)
INSERT INTO #TableB VALUES (5, 50, 200)
INSERT INTO #TableC VALUES (3, 60, 700)
--
SELECT * FROM #TableA
SELECT * FROM #TableB
SELECT * FROM #TableC
-- Query
SELECT
a.Col1, a.Col2, b.ColA, c.ColB1
FROM
#TableA a
LEFT OUTER JOIN
#TableB b
ON
b.Col1 = a.Col1 AND
LEFT OUTER JOIN
#TableC c
ON
c.Col1 = a.Col1
-- Drop the temporary tables
DROP TABLE #TableA
DROP TABLE #TableB
DROP TABLE #TableC
--
Krishnakumar S
What lies behind you and what lies before you is nothing compared to what
lies within you
"RickSean" wrote:
> The following query return duplicate records:
> select TableA.Col1, TableA.Col2, TableB.Cola, TableC.Colb1
> from TableA
> left outer join TableB on TableA.Col1=TableB.Col1
> and left outer join TableC on TableA.Col1=TableC.Col1
>
> "Krishnakumar S" wrote:
>
> > Use OUTER JOINs between the table. Always post DDL and sample data so that we
> > can understand the problem easly.
> > --
> > Krishnakumar S
> >
> > What lies behind you and what lies before you is nothing compared to what
> > lies within you
> >
> >
> > "RickSean" wrote:
> >
> > > I have a table TableA (Col1, Col2, Col3) which contains 5000 records. Col1 is
> > > the key field.
> > > Another table TableB (Col1, Cola, Colb) contains 300 records, Col1 is the
> > > key field.
> > > Another table TableC (Col1, Cola1, Colb1) contains 100 records, Col1 is the
> > > key field.
> > >
> > > I want to return all 5000 records from TableA in the following format:
> > > Output:
> > > Col1 Col2 Col3 Cola Colb1
> > >
> > > If TableA or TableB does not contain a value for Col1 then Cola and Colb1
> > > should output NULL in the result set.
> > >
> > > If my question can be answered without a ddl; it would be highly
> > > appreciated. Otherwise, i will try to post a ddl.