Joining tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining tables

Author

Topic

Nemis79
Starting Member

1 Post

Posted - 2009-10-16 : 08:50:00

Hi,

I have two tables.

One is called TMSDAT.TRANSMAIN and contains transactions.

TMSDAT.TRANSMAIN:
TRANSMAIN.TRANSIK - transaction number
TRANSMAIN.NOMVAL - nominal/number

The other is called TMSDAT.TRANSCOSTTAX and contains costs on the transactions.

TMSDAT.TRANSCOSTTAX:
TRANSCOSTTAX.TRANSIK - transaction number
TRANSCOSTTAX.COSTIK - cost type
TRANSCOSTTAX.AMOUNTPC - cost amount

The tables are joined on the following

SELECT *
FROM TMSDAT.TRANSMAIN, TMSDAT.TRANSCOSTTAX
WHERE TRANSMAIN.TRANSIK = TRANSCOSTTAX.TRANSIK

A transaction can have several different costs. Each cost type has an own identifier:

COMMISSION: TRANSCOSTTAX.COSTIK = 1
INTEREST ON INTEREST FUND: TRANSCOSTTAX.COSTIK = 83

A transaction can also have null entries for cost.

Now what I'm trying to do, is pull the following data from the two tables:

Transaction number, nominal/number, cost amount for COSTIK = 1, cost amount for COSTIK = 83

Could anyone please help?

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-10-16 : 14:24:11

Please try the following & see what happens.

DECLARE @TRANSMAIN TABLE
(
TRANSIK INT,
NOMVAL INT
)

INSERT @TRANSMAIN
SELECT 1, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 5, 6 UNION ALL
SELECT 6, 4

DECLARE @TRANSCOSTTAX TABLE
(
TRANSIK INT,
COSTIK INT,
AMOUNTPC Money
)
INSERT @TRANSCOSTTAX
SELECT 1, 1, 10 UNION ALL
SELECT 2, 2, 20 UNION ALL
SELECT 3, 1, 33 UNION ALL
SELECT 4, 83, 43 UNION ALL
SELECT 5, 6, 55 UNION ALL
SELECT 3, 1, 33 UNION ALL
SELECT 4, 83, 22 UNION ALL
SELECT 5, 6, 12 UNION ALL
SELECT 6, 83, 22

select a.Transik, a.Nomval, b.AmountPC, b.COSTIK
from @Transmain a join @TRANSCOSTTAX b on a.TRANSIK = b.TRANSIK
where b.COSTIK in (1, 83)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources