Display two fields of two UNRELATED tables WITHOUT crossjoin

Question

Display two fields of two UNRELATED tables WITHOUT crossjoin

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

david.s.patterson - Tuesday, March 28, 2017 6:31 AM
GilaMonster - Tuesday, February 5, 2013 12:41 PM
In this particular case, since you want the two counts...SELECT q1, q2 FROM(SELECT Count(a.PK) as q1 FROM Table1 as a) t1, (SELECT Count(b.PK) as q2 FROM Table2 as b) t2Only in this case though. In general the question can't be answered. How can you meaningfully join two completely unrelated tables and have the columns related to each other? If they are completely unrelated, then they probably shouldn't even be in the same query
Thank you. Actually this was exactly what I needed to do. In my particular case, I simply needed to return two column summations from Table 1, and the row count from Table 2. The values are all related in a business sense, but not in any direct way in the schema.
SELECT q1, q2, q3 FROM (SELECT SUM(t.ext_reg_deny) as q1, SUM(t.ext_reg_err) as q2 FROM traffic t) t1, (SELECT COUNT(r.id) as q3 FROM registration r) t2;

There are numerous options at your disposal:

SELECT

q1 = SUM(t.ext_reg_deny),

q2 = SUM(t.ext_reg_err),

q3 = MAX(SELECT COUNT(r.id) as q3 FROM registration r)

FROM traffic t;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

david.s.patterson SSC Enthusiast Points: 136 More actions · Answer 2

ChrisM@Work - Tuesday, March 28, 2017 8:55 AM
david.s.patterson - Tuesday, March 28, 2017 6:31 AM
GilaMonster - Tuesday, February 5, 2013 12:41 PM
In this particular case, since you want the two counts...SELECT q1, q2 FROM(SELECT Count(a.PK) as q1 FROM Table1 as a) t1, (SELECT Count(b.PK) as q2 FROM Table2 as b) t2Only in this case though. In general the question can't be answered. How can you meaningfully join two completely unrelated tables and have the columns related to each other? If they are completely unrelated, then they probably shouldn't even be in the same query
Thank you. Actually this was exactly what I needed to do. In my particular case, I simply needed to return two column summations from Table 1, and the row count from Table 2. The values are all related in a business sense, but not in any direct way in the schema.
SELECT q1, q2, q3 FROM (SELECT SUM(t.ext_reg_deny) as q1, SUM(t.ext_reg_err) as q2 FROM traffic t) t1, (SELECT COUNT(r.id) as q3 FROM registration r) t2;
There are numerous options at your disposal:
SELECT
q1 = SUM(t.ext_reg_deny),
q2 = SUM(t.ext_reg_err),
q3 = MAX(SELECT COUNT(r.id) as q3 FROM registration r)
FROM traffic t;

Hmmm. I could not get this to work in MySQL. But the following tweaked version did:
SELECT SUM(t.ext_reg_deny) as q1, SUM(t.ext_reg_err) as q2, (SELECT COUNT(r.id) FROM registration r) as q3 FROM traffic t;