I have a script that cross joins about 4 tables:

FROM Districts

The above query will combine all the tables into one, see a sample picture: cross join sample 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?

  • need to supply more information about the tables being crossed join, column names and where ClientCount and GroupCount columns come from, are these suppose to be the aggregate values? – zsheep Feb 26 '20 at 13:51
  • OK, GroupCount is actually an alias for Group (which is a table in the db). same with ClientCount. – Finch Feb 26 '20 at 13:54
  • How is this related to the above sample code? how are these two tables related/joined to the above code? Need to edit question describing the tables and the desired results. – zsheep Feb 26 '20 at 13:56
  • I have done that – Finch Feb 26 '20 at 14:20

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.