Creating a dynamic view

[douggy]

I have a list of people_IDS in a view as follows:

32
74
241
261
289

I also have a list of Other communication ID's (in a view)which relates to the ID of a newsletter.

4306
4307
4308
4309

I want to produce a view that puts a communication key next to each people_ID record. I need it to be dymanmic. ie when someone adds a new communication key it is added to the view. The table would then look as follows

32, 4306
74, 4306
241, 4307
261, 4308
289, 4309
32, 4307
74, 4307
241, 4307
261, 4307
289, 4307

Can anyone help??

Many thanks
Mark

 

[mrdenny]

Basically you need to write a join without actually joining the tables together. Assuming your tables are names People_IDS and Comm_IDS

select People_IDS.ID, Comm_IDS.ID
from People_IDS, Comm_IDS

This code will return every record in the People_IDS table with every record in the Comm_IDS table. If both tables have 2 records each you will get back 4 records.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[micha123]

You didn't ask - but I have to tell you ...

Beware that these kinds of queries can result a poor performance for very big tables, if it is issued on-the-fly many times! If this is your case - I would be glad to help you find a solution...

M.

 

[douggy]

Thankyou Denny this worked well.

As far as performance is concerned we can address this after so any advice would be great.

Many thanks
Mark