3 Inner joins - 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

SQL Server 2000 Forums

SQL Server Development (2000)

3 Inner joins

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-08-12 : 13:10:24

I have an inner join that works:
select Distinct SERVE_NO,a.ACC_HEADER,SE AS Network_Access,a.MOB AS Airtime,a.mob_01 AS Discount,a.mob_02 as MISC_CHARGES,a.div AS Data, a.TAX, trans_total as MonthlyTotal,datepart(month,bill_Date) as Month,datepart(year,bill_Date)as year
from Transactions a
inner Join AccountHeader b
on b.acc_header = a.acc_header
where 0 = 0
and serve_no IN (SELECT SERVE_NO FROM Services WHERE (LINE_STATUS = 'ACTIVE') AND (SERV_CLASS = 'WIRELESS')
)
and b.bill_date > (
select DATEADD(month,-1,max(bill_date))
from transactions a
inner join AccountHeader b
on b.acc_header = a.acc_header
where serve_no IN (SELECT SERVE_NO FROM Services WHERE (LINE_STATUS = 'ACTIVE') AND (SERV_CLASS = 'WIRELESS'))
)
Order by serve_No

this works fine.

But I want to add a costcenter for the Service Number, but everything I try fails. Here is the simple query.SERVE_NO = Serve_No.

select distinct serve_no, costCentre
from CostCentres
where serve_no = @serve_no
and startDate in (
select distinct max(startDate)
from CostCentres
where serve_no = @serve_no
)

Thanks for anyhelp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-12 : 13:14:50

But what is your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-08-12 : 13:18:58

Crashes doesnt work at all when I do the the third inner join - basically I am doing it horibly wrong, and am too Embarrassed to show everyone what I did. :>

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-12 : 13:25:43

So CostCentres is in relation to Transactions via serve_no.
Is there a match for each serve_no so we can use an inner join?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-12 : 13:32:11

[code]SELECT DISTINCT a.serve_no,
a.acc_header,
se AS network_access,
a.mob AS airtime,
a.mob_01 AS discount,
a.mob_02 AS misc_charges,
a.div AS data,
a.tax,
trans_total AS monthlytotal,
Datepart(MONTH,bill_date) AS MONTH,
Datepart(YEAR,bill_date) AS YEAR
FROM transactions a
INNER JOIN accountheader b
ON b.acc_header = a.acc_header
INNER JOIN CostCentres cc
ON a.serve_no = cc.serve_no AND cc.startDate in (select distinct max(startDate)
from CostCentres cc2
where cc2.serve_no = cc.serve_no)
WHERE 0 = 0
AND a.serve_no IN (SELECT serve_no
FROM services
WHERE (line_status = 'ACTIVE')
AND (serv_class = 'WIRELESS'))
AND b.bill_date > (SELECT Dateadd(MONTH,-1,Max(bill_date))
FROM transactions a
INNER JOIN accountheader b
ON b.acc_header = a.acc_header
WHERE serve_no IN (SELECT serve_no
FROM services
WHERE (line_status = 'ACTIVE')
AND (serv_class = 'WIRELESS')))
ORDER BY a.serve_no
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-08-12 : 16:28:20

Wow looks alot cleaner from the code that I have, added cc.Costcentre
now I have everything I ever wanted - well except for that million dollars

Thanks for the help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-12 : 16:58:55

Oh I see, I have forgotten the cc.Costcentre in the SELECT-list.
So that was your part of the solution