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)

joins

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2001-11-28 : 22:03:55

hi

i need to join 3 tables and get all the row from the first table
the structure are..

1. bill_process
owner_id
Account_ID
date
hr
Qty_usg
Rate_Scheme

2. Rate_Scheme
Sch_Id
Rate_Scheme

3. Rate_Sch_det
Sch_Det_Id
Hour
type
Amt

my query is

SELECT Owner_ID,IMSI,usg_Dt,Account_ID, a.Rate_Scheme,type, Qty_usg, Amt,IMEI,Fleet_ID,mem_id,HOUR FROM bill_process A,Rate_Scheme B ,Rate_Sch_det C WHERE Sch_Id =Sch_Det_Id and
a.Rate_Scheme *=b.Rate_Scheme AND HR=Hour

then i tried out drived tables

SELECT Owner_ID,IMSI,usg_Dt,Account_ID,TB1.Rt,type, Qty_usg,Amt,IMEI, Fleet_ID, mem_id,HOUR FROM bill_process A, (SELECT Rate_Scheme
'RT', type,Amt,HOUR FROM Rate_Scheme B ,Rate_Sch_det C WHERE
Sch_Id=Sch_Det_Id ) AS TB1 WHERE TB1.Rt=A.Rate_Scheme AND HR=Hour

same problem

then i tried view

CREATE VIEW HR_RATING AS
SELECT Rate_Scheme 'RT',type,Amt,HOUR FROM Rate_Scheme B ,Rate_Sch_det C WHERE Sch_Id=Sch_Det_Id

query :-

SELECT Owner_ID,IMSI,usg_Dt,Account_ID, B.Rt,type,Qty_usg,Amt, IMEI,Fleet_ID,mem_id FROM bill_process A,HR_RATING B WHERE A.Rate_Scheme*=B.Rt AND HR=Hour

but still the problem

Server: Msg 303, Level 16, State 1, Line 1
The table 'Rate_Scheme' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-11-28 : 22:32:01

What version do you have?

SELECT Owner_ID,IMSI,usg_Dt,Account_ID, a.Rate_Scheme,type, Qty_usg, Amt,IMEI,Fleet_ID,mem_id,HOUR
FROM bill_process a
left outer join Rate_Scheme b on a.Rate_Scheme = b.Rate_Scheme
inner join Rate_Sch_det c on b.Sch_Id = c.Sch_Det_Id
WHERE HR = Hour

you don't say whether hr = hour is a filter or should be included in the outer join.
If it is in the outer join then just move it in there.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-11-28 : 22:36:29

Well you haven't done a really good job of explaining what the tables are and what you are trying to achieve.

But I think it is something like this:

You want all the rows in bill_process and join Rate_scheme and rate_sch_det where the rate_Scheme and the hour matches. Is that right ?

I would LEFT JOIN onto a derived table of Rate_Scheme and rate_sch_det.



SELECT *

FROM

bill_process
LEFT JOIN (

SELECT *
FROM
Rate_Scheme
INNER JOIN Rate_Sch_Det ON SCH_ID = SCH_Det_ID

) RS ON RS.Rate_Scheme = bill_process.Rate_Scheme AND hr = Hour

Or something like that

Damian

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2001-11-29 : 02:49:50

Merkin u guess right but i need to get data from all 3 table
the data should be . all rows from bill_process and matching rate_scheme and hr....

sorry i was in hurry so not able to explain ..

i have the call details.. i need to charge subscriber who's rate schem match with the rate_scheme tabel and the call made hour should macth to rate scheme details table so that i can get the price...

i did not try it but Merkin u query is not what i was looking for.. i must check nr...

thanks for the help

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-11-29 : 06:02:27

well we can't help if we can't understand what you want. Why don't you post an example of what your data looks like and what you want the result to look like.

Better still, post your DDL (Create Table statements) and some DML (Insert statements) and we can get the query running.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources