How to name 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

Transact-SQL (2000)

How to name joins

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-27 : 08:59:29

I know - stupid title.

Peso once wrote:

			select	s1.id,
				s1.cat,
				(select count(*) from @sample as s2 where s2.id = s1.id and s2.dt >= s1.dt) as seq
			from	@sample as s1

What if I need to do something similar on a join? I.e. instead of @sample being one table, I'm constructing it like:

SELECT kra.ex_id
, kra.ce_id
, kra.dt
, krat.ex_rt_1
FROM table_2 krat
INNER JOIN table_1 kra
ON krat.ex_id=kra.ex_id AND krat.ce_id=kra.ce_id
WHERE krat.ex_type='P'

Any help appreciated.

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-27 : 13:54:10

Any chance of someone helping me out?

To be more clear:
Instead of ...

declare @sample table (id int, cat varchar(2), dt datetime)
insert	@sample
select	1, 'A', '11/02/2006' union all
select	2, 'C', '05/04/2005' union all
select	2, 'B', '07/11/2003' union all
select	2, 'D', '12/28/2004'

select	s1.id,
	s1.cat,
	(select count(*) from @sample as s2 where s2.id = s1.id and s2.dt >= s1.dt) as seq
from	@sample as s1

I have:

declare @kra table (ex_id int, ce_id int, dt datetime)
insert	@kra
select	1, 101, '1999/1/13' union all
select	1, 101, '2004/7/21' union all
select	1, 101, '2001/8/20' union all
select	1, 107, '2004/7/21' union all
select	1, 107, '2002/11/5' union all
select	2, 101, '2002/7/11' union all
select  2, 101, '2001/3/19' union all
select  2, 110, '2002/10/8' union all
select  3, 101, '2003/9/18'

declare @krat table (ex_id int, ce_id int, ex_rt_1 varchar(6), ex_type char(1))
insert	@krat
select	1, 101, 'WIDE', 'P' union all
select	2, 101, 'WIDE', 'P' union all
select	2, 101, 'FRT',  'R' union all
select	3, 101, 'CVS',  'R' union all
select	4, 101, 'WIDE', 'S' union all
select	4, 101, 'MRK',  'U' union all
select  5, 101, 'FRT',  'V'

And want to do the same as previously, only not on @sample but on the following join:

SELECT kra.ex_id
, kra.ce_id
, kra.dt
, krat.ex_rt_1
FROM @krat krat
INNER JOIN @kra kra
ON krat.ex_id=kra.ex_id AND krat.ce_id=kra.ce_id
WHERE krat.ex_type='P'

In other words, how do I replace references to @sample in

select	s1.id,
	s1.cat,
	(select count(*) from @sample as s2 where s2.id = s1.id and s2.dt >= s1.dt) as seq
from	@sample as s1

with the result of the join shown above?

Thanks in advance...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-27 : 14:24:42

If you just want to replace @sample with your query, then this is called a derived table.

select s1.id,
s1.cat,
(select count(*) from (PutYourQueryInHere) as s2 where s2.id = s1.id and s2.dt >= s1.dt) as seq
from (PutYourQueryInHere) as s1

If that's not what you want, then you need to provide the expected result set that should be displayed after the query runs given the sample data that was provided.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 14:26:47

This?

-- prepare sample data
declare @kra table (ex_id int, ce_id int, dt datetime)

insert	@kra
select	1, 101, '1999/1/13' union all
select	1, 101, '2004/7/21' union all
select	1, 101, '2001/8/20' union all
select	1, 107, '2004/7/21' union all
select	1, 107, '2002/11/5' union all
select	2, 101, '2002/7/11' union all
select  2, 101, '2001/3/19' union all
select  2, 110, '2002/10/8' union all
select  3, 101, '2003/9/18'

declare @krat table (ex_id int, ce_id int, ex_rt_1 varchar(6), ex_type char(1))

insert	@krat
select	1, 101, 'WIDE', 'P' union all
select	2, 101, 'WIDE', 'P' union all
select	2, 101, 'FRT',  'R' union all
select	3, 101, 'CVS',  'R' union all
select	4, 101, 'WIDE', 'S' union all
select	4, 101, 'MRK',  'U' union all
select  5, 101, 'FRT',  'V'

-- show the expected result
SELECT		kra.ex_id,
		kra.ce_id,
		kra.dt,
		krat.ex_rt_1,
		(select count(*) from @kra as s2 where s2.ex_id = kra.ex_id and s2.ce_id = kra.ce_id and s2.dt >= kra.dt) as seq
FROM		@krat as krat
INNER JOIN	@kra as kra ON kra.ex_id = krat.ex_id AND kra.ce_id = krat.ce_id
WHERE		krat.ex_type = 'P'
order by	1,
		2,
		3 desc,
		4

Peter Larsson
Helsingborg, Sweden

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-27 : 15:08:58

Peso,

That's exactly what I was after - thank you. Now I can see what goes were - very helpful!

Tkizer,

That's what I was trying to do, and I was doing it that way - replacing name of table with the entire select statement. Must have been spelling something incorrectly... Now I know it's called derived table. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources