Join Two Select Statement Results

JOIN two SELECT statement results

SELECT t1.ks, t1.[# Tasks], COALESCE(t2.[# Late], 0) AS [# Late]
FROM 
    (SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1
LEFT JOIN
    (SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON (t1.ks = t2.ks);

joining two select statements

Not sure what you are trying to do, but you have two select clauses. Do this instead:

SELECT * 
FROM ( SELECT * 
       FROM orders_products 
       INNER JOIN orders ON orders_products.orders_id = orders.orders_id 
       WHERE products_id = 181) AS A
JOIN ( SELECT * 
       FROM orders_products 
       INNER JOIN orders ON orders_products.orders_id = orders.orders_id
       WHERE products_id = 180) AS B

ON A.orders_id=B.orders_id

Update:

You could probably reduce it to something like this:

SELECT o.orders_id, 
       op1.products_id, 
       op1.quantity, 
       op2.products_id, 
       op2.quantity
FROM orders o
INNER JOIN orders_products op1 on o.orders_id = op1.orders_id  
INNER JOIN orders_products op2 on o.orders_id = op2.orders_id  
WHERE op1.products_id = 180
AND op2.products_id = 181

How to combine two select statements into one select statement in MSSQL?

Normally, you would use an inner join for things like that. But inner join needs some common columns between the two objects it joins, and you don't have that.

Since your queries also does not contain an ORDER BY clause, there is no reliable way to join them so that each row in table1 will always be joined to the same row in table2.

However, since both tables have a time column, you can use that:

;WITH CTE1 AS
(
    SELECT  a1, 
            b1, 
            c1,
            ROW_NUMBER() OVER(ORDER BY [time]) AS rn
    FROM Table1
    WHERE time between '2018-03-05' and '2018-03-06'
), CTE2 AS
(
    SELECT  a2, 
            b2, 
            c2,
            ROW_NUMBER() OVER(ORDER BY [time]) AS rn
    FROM Table2
    WHERE time between '2018-03-05' and '2018-03-06'
)

SELECT t1.a1, t1.b1, t1.c1, t2.a2, t2.b2, t2.c2                     
FROM cte1 as t1
INNER JOIN cte2 as t2 ON t1.rn = t2.rn

MySQL - Joining two SELECT statements that have aliases and joins inside them

You only use the aliases you defined . . . X and Y. Here is an example throughout the query:

SELECT x.ID_Number, x.Price, x.Quantity, y.ID_Number, y.Currency, y.Purchased
FROM (SELECT b.ID_Number, b.Price, s.Quantity
      FROM tbl_bills b LEFT JOIN
           tbl_stock s
           ON b.Price = s.Price
     ) X LEFT JOIN
     (SELECT c.ID_Number, c.Currency, p.Purchased
      FROM tbl_currency c
      WHERE blahblahblah
     ) Y
     ON X.ID_Number = Y.ID_Number;

Note that p.purchased still has the same error, of the unrecognized column name.

To me, this is a silly way to write the query. I would instead use:

SELECT b.ID_Number, b.Price, s.Quantity, c.Currency, p.Purchased  -- c.ID_Number, is not needed unless you want to see if there is a match
FROM tbl_bills b LEFT JOIN
     tbl_stock s
     ON b.Price = s.Price LEFT JOIN
     tbl_currency c
     ON b.ID_Number = c.ID_Number AND blahblahblah;

This is shorter, easier to write, easier to read, and should have better performance no subqueries are being materialized.

Inner Join on Two Select Statements SQL

assuming your schema is:

user_grades
username: varchar(255)
grade: int

Then I would do this query

SELECT 
    user_grades.username, 
    round(grades_above.total / user_grades.total,2) percent_above 
FROM (
    SELECT username, count(*) total FROM user_grades GROUP BY 1
)  user_grades
INNER JOIN (SELECT username, count(*) total FROM user_grades WHERE grade > 75 
GROUP BY 1) grades_above ON user_grades.username = grades_above.username

There is probably more than one way to do it but that's done with two select statements and an inner join. I think the example results are wrong but here's what I would expect to see.

username    percent_above
Bob         1.00
Joe         0.67
Sam         0.50

But just make sure you understand what's going on and let me know if you have any questions. It's more important you understand how to get the answer than the answer itself.

combine two select query result on some conditions

One way would be to use a cross apply (though a join would also work, it just gets a bit more complicated to write):

SELECT tbl1.EndDate, tbl1.PEL, tbl1.FL, C.CumEL, C.CumFL
FROM tbl1
CROSS APPLY 
(
    SELECT SUM(CumEL) AS CumEL, SUM(CumFL) AS CumFL
    FROM tbl2
    WHERE tbl2.EndDate <= tbl1.EndDate
) AS C;

How to combine two select query with different columns

Consider your first query as Query1 and second query as Query2, you can use simple join between these two.

As you said PatientAppointment table is common in both, use its primary key(CDRId) as joining between these two. So your query would look like.

SELECT * 
FROM ( Query1 ) AS table1
INNER JOIN ( Query2) AS table2 ON table1.CDRId = table2.CDRId;

How to Join two SELECT queries having same column names but different row values in both tables

UNION and UNION ALL are SQL operators used to concatenate 2 or more result sets. This allows us to write multiple SELECT statements, retrieve the desired results, then combine them together into a final, unified set.

The main difference between UNION and UNION ALL is that:

UNION: only keeps unique records

UNION ALL: keeps all records, including duplicates

UNION Example:

SELECT column1 AS datacheck from table1 
  UNION 
SELECT column1 AS datacheck from table2

Result:

+-----------+
| datacheck |
+-----------+
|   data2   |
+-----------+

UNION ALL example:

SELECT column1 AS datacheck from table1 
  UNION ALL
SELECT column1 AS datacheck from table2

Result:

+-----------+
| datacheck |
+-----------+
|   data2   |
|   data2   |
+-----------+

How to Merge Two Select Query in Mysql

SELECT r.user_id as userId, u.rider_avg_ratings as ratings 
FROM user as u 
INNER JOIN request as r on u.id = r.user_id 

UNION 

SELECT r1.driver_id as userId, u1.driver_avg_ratings as ratings 
FROM user as u1 
INNER JOIN request as r1 on u1.id = r1.driver_id

This query will fetch the desired result.

---

Related Topics