TheDirtyJenks TheDirtyJenks - 1 year ago 82
SQL Question

SSRS 2008 R2 Data Region Embedded in Another Data Region

I have two unrelated tables (Table A and Table B) that I would like to join to create a unique list of pairings of the two. So, each row in Table A will pair with each row in Table B creating a list of unique pairings between the two tables.

My ideas of what can be done:

  1. I can either do this in the query (SQL) by creating one dataset and having two fields outputted (each row equaling a unique pairing).

  2. Or by creating two different datasets (one for each table) and have a data region embedded within a different data region; each data region pulling from a different dataset (of the two created for each table).

I have tried implementing the second method but it would not allow me to select a different dataset for the embedded data region from the parent data region.

The first method I have not tried but do not understand how or even if it is possible through the SQL language.

Any help or guidance in this matter would be greatly appreciated!

Answer Source

The first is called a cross join:

select t1.*, t2.*
from t1 cross join

Whether you should do this in the application or in the database is open to question. It depends on the size of the tables and the bandwidth to the database -- there is an overhead to pulling rows from a database.

If each table has 2 rows, this is a non-issue. If each table has 100 rows, then you would be pulling 10,000 rows from the database and it might be faster to pull 2*100 rows and do the looping in the application.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download