remedios_


Hi,

I made two tables. The Policy table's rate_limit column references the RateLimit table's id as foreign key.

Here is the table definitions.

CREATE TABLE RateLimit
(
id INT CONSTRAINT PK_RateLimit_id PRIMARY KEY CLUSTERED,
name VARCHAR(20),
min INT,
max INT
);

CREATE TABLE Policy
(
id INT CONSTRAINT PK_Policy_id PRIMARY KEY CLUSTERED,
rate_limit INT CONSTRAINT FK_Policy_rate_limit FOREIGN KEY(rate_limit) REFERENCES RateLimit(id),
min INT,
max INT
);

Now, I inserted value each table;

INSERT INTO RateLimit VALUES(1,'RateLimit1',1000,10000);
INSERT INTO RateLimit VALUES(2,'RateLimit2',2000,20000);
INSERT INTO RateLimit VALUES(3,'RateLimit3',3000,30000);
INSERT INTO RateLimit VALUES(4,'RateLimit4',4000,40000);
INSERT INTO RateLimit VALUES(5,'RateLimit5',5000,50000);

INSERT INTO Policy VALUES(1,1,10,100);
INSERT INTO Policy VALUES(2,2,10,100);
INSERT INTO Policy VALUES(3,3,10,100);
INSERT INTO Policy VALUES(4,4,10,100);
INSERT INTO Policy VALUES(5,5,10,100);
INSERT INTO Policy VALUES(6,5,10,100);
INSERT INTO Policy VALUES(7,4,10,100);
INSERT INTO Policy(id,min,max) VALUES(8,10,100);

=========================================================================

SELECT A.id,B.name,B.min,B.max FROM Policy A,RateLimit B WHERE A.rate_limit=B.id;

The result of above query statement is like this.

---------------------------------------------

id rate_limit min max

---------------------------------------------

1 RateLimit1 1000 10000
2 RateLimit2 2000 20000
3 RateLimit3 3000 30000
4 RateLimit4 4000 40000
5 RateLimit5 5000 50000
6 RateLimit5 5000 50000
7 RateLimit4 4000 40000

---------------------------------------------

How can I get whole recordset using join, that is, if Policy table's rate_limit is null, return Policy's min/max otherwise, return RateLimit's min/max, just like below result.

---------------------------------------------

id rate_limit min max

---------------------------------------------

1 RateLimit1 1000 10000
2 RateLimit2 2000 20000
3 RateLimit3 3000 30000
4 RateLimit4 4000 40000
5 RateLimit5 5000 50000
6 RateLimit5 5000 50000
7 RateLimit4 4000 40000

8 10 100

---------------------------------------------





Re: Using foreign key has null value...

Rafael Krisller


hi,

Use left join

To see:

SELECT A.id,B.name,B.min,B.max FROM Policy A left join RateLimit B on A.rate_limit = B.id;

or in sql2000 too.

SELECT A.id,B.name,B.min,B.max FROM Policy A,RateLimit B WHERE A.rate_limit *= B.id;

Good Luck.

[]'s

Rafael Krisller







Re: Using foreign key has null value...

Konstantin Kosinsky

I could use LEFT JOIN for this task:

Code Block

SELECT A.id,B.name,A.min,A.max

FROM Policy A LEFT JOIN RateLimit B ON( A.rate_limit=B.id)






Re: Using foreign key has null value...

hunchback

As indicated in the other posting, you need to use an OUTER JOIN, but you also need to use functions ISNULL or COALESCE to get the min / max values from the table [RateLimit], when the is a match, and min / max value from table [Plicy] when there is no match.

select p.[id], coalesce(r.min, p.min) as [min], coalesce(r.max, p.max) as max

from Policy as p left outer join RateLimit as r on p.rate_limit = r.[id]

go

AMB