Unusual Query problem - 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)

Unusual Query problem

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-07-16 : 00:12:46

I want to create and fill up a new memory table with rows from the join and an additional string column, this would be straight forward, except the I have 23 string and I need to repeat each row 23 times with each of the strings
i.e
strings are 'ColTypes', 'Balances', 'Matirity', 'CutOffDate' etc and there are 23 of them....which will surely increase

I have an unknown amount of rows returned from the select query.

I cringe at having to do something like

select c.PName, cg.GId, cg.SName, ci.PId, ci.PItemId, cgm.GNumber, ci.LId, 'ColTypes'
from tblCola ci
inner join tblColb c on c.PId = ci.PId
inner join tblColc cg on c.PId = cg.PId
inner join tblCold cgm on cg.GId = cgm.GId and ci.LId = cgm.LId
where ci.Pid = @PID
UNION ALL
select c.PName, cg.GId, cg.SName, ci.PId, ci.PItemId, cgm.GNumber, ci.LId, 'Balances'
from tblCola ci
inner join tblColb c on c.PId = ci.PId
inner join tblColc cg on c.PId = cg.PId
inner join tblCold cgm on cg.GId = cgm.GId and ci.LId = cgm.LId
where ci.Pid = @PID
UNION ALL
select c.PName, cg.GId, cg.SName, ci.PId, ci.PItemId, cgm.GNumber, ci.LId, 'Matirity'
from tblCola ci
inner join tblColb c on c.PId = ci.PId
inner join tblColc cg on c.PId = cg.PId
inner join tblCold cgm on cg.GId = cgm.GId and ci.LId = cgm.LId
where ci.Pid = @PID
UNION ALL
etc ... 23 times...is there a better way to achieve this...I just need to repeat each row with each of the 23 strings for now

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-07-16 : 00:55:30

If I understand you correctly, I think you just need to put those 23 values into a separate table and then do a cartesian join to it, something like this:

select c.PName, cg.GId, cg.SName, ci.PId, ci.PItemId, cgm.GNumber, ci.LId, YourTable.YourColumn
from tblCola ci
inner join tblColb c on c.PId = ci.PId
inner join tblColc cg on c.PId = cg.PId
inner join tblCold cgm on cg.GId = cgm.GId and ci.LId = cgm.LId
cross join YourTable
where ci.Pid = @PID

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-07-16 : 10:12:48

Thank you so much sir,

very simple solution, but I had no idea it existed....Thanks a million sir

quote:
Originally posted by nosepicker

If I understand you correctly, I think you just need to put those 23 values into a separate table and then do a cartesian join to it, something like this:

select c.PName, cg.GId, cg.SName, ci.PId, ci.PItemId, cgm.GNumber, ci.LId, YourTable.YourColumn
from tblCola ci
inner join tblColb c on c.PId = ci.PId
inner join tblColc cg on c.PId = cg.PId
inner join tblCold cgm on cg.GId = cgm.GId and ci.LId = cgm.LId
cross join YourTable
where ci.Pid = @PID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources