Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a script that cross joins about 4 tables:
SELECT *
FROM Districts
CROSS JOIN Seasons
CROSS JOIN Sectors
CROSS JOIN Sites
The above query will combine all the tables into one, see a sample picture:
I want to add two more tables Group and Client, but not the entire tables, just the COUNT of both tables. so after the print column in the picture below, there should be two more columns, GroupCount for group table count and ClientCount for client table count. How do I do it?
It's probably easiest to add one correlated sub-query for each count:
SELECT D.*, S1.*, S2.*, S3.*
, ( SELECT COUNT(1) FROM Group G WHERE G.<key> = ... ) as GroupCount
, ( SELECT COUNT(1) FROM Client C WHERE C.<key> = ... ) as ClientCound
FROM Districts as D
CROSS JOIN Seasons as S1
CROSS JOIN Sectors as S2
CROSS JOIN Sites as S3
Since I don't know how Group and Client relates to the other tables, I left that as an exercise for the reader ;-)
It's quite unusual to do a cartesian product between 4 tables in a query, but perhaps you have your reason for that. Beware that the cardinality of the result is the product of the cardinality for the individual tables. If the cardinality for the tables is 100, the result will contain 100^4=100000000 rows. For a cardinality of 1000, you end up with 1000^4=1000000000000. If each row is 1 byte, the result in the latter case is 1Tb.
