Tripling end results of a query

[Jeffrey Sheldon via SQLMonster.com]

I need to add expense types to various projects in our ERP database. I
perform this query

select DISTINCT a.project_id
from project as a, project_cost_element as b
where a.project_id = b.project_id
and a.project_status in ('T','A')
and b.cost_element_id between '7012000' and '7012999'

The result is projects that this query is affected.

00010
00020
00162
00187
00242

I need to put this in a load file where for every 4 lines is the same project.
...I would like the end result

00010
00010
00010
00010
00020
00020
00020
00020
00162
00162
00162
00162
etc
--
Message posted via http://www.sqlmonster.com

[AK]

create table seq(i int)
insert into seq values(1)
insert into seq values(2)
insert into seq values(3)
insert into seq values(4)
create table t(c char(2))
insert into t values('aa')
insert into t values('bb')
insert into t values('cc')
select c from t cross join seq
drop table seq
drop table t

c
----
aa
aa
aa
aa
bb
bb
bb
bb
cc
cc
cc
cc

(12 row(s) affected)

[Jeffrey Sheldon via SQLMonster.com]

AK,

Thanks for the reponse a bit lost though, the project numbers are sometimes
not sequential.

i.e. it may go from 00010, 00020, but then jump to 00213, then 00457, etc...

Looking at the script on the insert values 'aa', I wouldn't be able to enter
these values (all 350 of them)

Jeff

AK wrote:

create table seq(i int)
insert into seq values(1)
insert into seq values(2)
insert into seq values(3)
insert into seq values(4)
create table t(c char(2))
insert into t values('aa')
insert into t values('bb')
insert into t values('cc')
select c from t cross join seq
drop table seq
drop table t

c
----
aa
aa
aa
aa
bb
bb
bb
bb
cc
cc
cc
cc

(12 row(s) affected)

--
Message posted via http://www.sqlmonster.com

[Razvan Socol]

Try this:

SELECT X.* FROM (
select DISTINCT a.project_id
from project as a, project_cost_element as b
where a.project_id = b.project_id
and a.project_status in ('T','A')
and b.cost_element_id between '7012000' and '7012999'
) X CROSS JOIN (
SELECT 1 N UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
) Y

Razvan

[Jeffrey Sheldon via SQLMonster.com]

Razan,

THANK YOU, this did it

Jeff

Razvan Socol wrote:

Try this:

SELECT X.* FROM (
select DISTINCT a.project_id
from project as a, project_cost_element as b
where a.project_id = b.project_id
and a.project_status in ('T','A')
and b.cost_element_id between '7012000' and '7012999'
) X CROSS JOIN (
SELECT 1 N UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
) Y

Razvan

--
Message posted via http://www.sqlmonster.com