|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Combining Two Tables ?I have two tables, TABLE 1 ---------- AccRef Debit Credit ------------------------- LL001 100 200 LL002 150 300 LL003 300 250 TABLE 2 ---------- AccRef Dbt Crdt ------------------------- LL001 300 400 LL002 950 700 What I would like to do is combine these two tables in this form I wrote this SQL SELECT AccRef, Debit, Credit, Dbt, Crdt FROM TABLE1 A, TABLE2 B WHERE A.AccRef = B.AccRef SQL OUTPUT -------------------------- AccRef Debit Credit Dbt Crdt --------------------------------------- LL001 100 200 300 400 LL002 150 300 950 700 But I wanna get This Output >>>>> REQUESTED OUTPUT -------------------------- AccRef Debit Credit Dbt Crdt --------------------------------------- LL001 100 200 300 400 LL002 150 300 950 700 LL003 300 250 0 0 How could I do it? Thanks That should do the work:
SELECT AccRef, Debit, Credit, ISNULL(Dbt,0), ISNULL(Crdt,0) FROM TABLE1 A LEFt JOIN TABLE2 B ON A.AccRef = B.AccRef HTH, Jens Suessmeyer. Show quote "Bongee" wrote: > Hello, > > I have two tables, > > TABLE 1 > ---------- > AccRef Debit Credit > ------------------------- > LL001 100 200 > LL002 150 300 > LL003 300 250 > > TABLE 2 > ---------- > AccRef Dbt Crdt > ------------------------- > LL001 300 400 > LL002 950 700 > > What I would like to do is combine these two tables in this form > > I wrote this SQL > > SELECT AccRef, Debit, Credit, Dbt, Crdt > FROM TABLE1 A, TABLE2 B > WHERE A.AccRef = B.AccRef > > SQL OUTPUT > -------------------------- > AccRef Debit Credit Dbt Crdt > --------------------------------------- > LL001 100 200 300 400 > LL002 150 300 950 700 > > But I wanna get This Output >>>>> > > REQUESTED OUTPUT > -------------------------- > AccRef Debit Credit Dbt Crdt > --------------------------------------- > LL001 100 200 300 400 > LL002 150 300 950 700 > LL003 300 250 0 0 > > > How could I do it? > > Thanks > > > > > > > SELECT t1.AccRef, t1.Debit, t1.Credit, COALESCE(t2.Dbt, 0) AS Dbt,
COALESCE(t2.Crdt, 0) AS Crdt FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.AccRef = t2.AccRef Show quote "Bongee" <bong***@bonbon.net> wrote in message news:eryjG8HiFHA.3700@TK2MSFTNGP10.phx.gbl... > Hello, > > I have two tables, > > TABLE 1 > ---------- > AccRef Debit Credit > ------------------------- > LL001 100 200 > LL002 150 300 > LL003 300 250 > > TABLE 2 > ---------- > AccRef Dbt Crdt > ------------------------- > LL001 300 400 > LL002 950 700 > > What I would like to do is combine these two tables in this form > > I wrote this SQL > > SELECT AccRef, Debit, Credit, Dbt, Crdt > FROM TABLE1 A, TABLE2 B > WHERE A.AccRef = B.AccRef > > SQL OUTPUT > -------------------------- > AccRef Debit Credit Dbt Crdt > --------------------------------------- > LL001 100 200 300 400 > LL002 150 300 950 700 > > But I wanna get This Output >>>>> > > REQUESTED OUTPUT > -------------------------- > AccRef Debit Credit Dbt Crdt > --------------------------------------- > LL001 100 200 300 400 > LL002 150 300 950 700 > LL003 300 250 0 0 > > > How could I do it? > > Thanks > > > > > > You will need to use an OUTER JOIN.
SELECT AccRef = COALESCE(a.AccRef, b.AccRef), Debit = COALESCE(a.Debit, 0), Credit = COALESCE(a.Credit, 0), Dbt = COALESCE(a.Dbt, 0), Crdt = COALESCE(a.Crdt, 0) FROM Table1 a FULL OUTER JOIN Table2 b ON a.AccRef = b.AccRef Have you considered fixing this design? Also, I suggest proper ANSI syntax for inner joins... Show quote "Bongee" <bong***@bonbon.net> wrote in message news:eryjG8HiFHA.3700@TK2MSFTNGP10.phx.gbl... > Hello, > > I have two tables, > > TABLE 1 > ---------- > AccRef Debit Credit > ------------------------- > LL001 100 200 > LL002 150 300 > LL003 300 250 > > TABLE 2 > ---------- > AccRef Dbt Crdt > ------------------------- > LL001 300 400 > LL002 950 700 > > What I would like to do is combine these two tables in this form > > I wrote this SQL > > SELECT AccRef, Debit, Credit, Dbt, Crdt > FROM TABLE1 A, TABLE2 B > WHERE A.AccRef = B.AccRef > > SQL OUTPUT > -------------------------- > AccRef Debit Credit Dbt Crdt > --------------------------------------- > LL001 100 200 300 400 > LL002 150 300 950 700 > > But I wanna get This Output >>>>> > > REQUESTED OUTPUT > -------------------------- > AccRef Debit Credit Dbt Crdt > --------------------------------------- > LL001 100 200 300 400 > LL002 150 300 950 700 > LL003 300 250 0 0 > > > How could I do it? > > Thanks > > > > > > SELECT COALESCE(A.AccRef,B.AccRef),
COALESCE(A.Debit,0), COALESCE(A.Credit,0), COALESCE(B.Dbt,0), COALESCE(B.Crdt,0) FROM TABLE1 A FULL JOIN TABLE2 B ON A.AccRef = B.AccRef -- David Portas SQL Server MVP -- in the previous case you can use the following query
SELECT A.AccRef, Debit, Credit, isnull(Dbt, 0) as Dbt, isnull(Crdt, 0) as Crdt FROM TABLE1 A, TABLE2 B WHERE A.AccRef *= B.AccRef Stay away from the depracated *= syntax. It's being phased out for a
reason. Show quote "Sai" <sbillan***@gmail.com> wrote in message news:1121351519.278468.262920@g44g2000cwa.googlegroups.com... > in the previous case you can use the following query > > SELECT A.AccRef, Debit, Credit, isnull(Dbt, 0) as Dbt, isnull(Crdt, 0) > as Crdt > FROM TABLE1 A, TABLE2 B > WHERE A.AccRef *= B.AccRef > NO, NO, NO. Please stop advocating the use of old-style joins, they are
being deprecated and they can lead to unreliable and incorrect results. Show quote > WHERE A.AccRef *= B.AccRef  |
|||||||||||||||||||||||
Other interesting topics