How to Combine 2 Select Statements into One

How do I combine 2 select statements into one?

You have two choices here. The first is to have two result sets which will set 'Test1' or 'Test2' based on the condition in the WHERE clause, and then UNION them together:

select 
    'Test1', * 
from 
    TABLE 
Where 
    CCC='D' AND DDD='X' AND exists(select ...)
UNION
select 
    'Test2', * 
from 
    TABLE
Where
    CCC<>'D' AND DDD='X' AND exists(select ...)

This might be an issue, because you are going to effectively scan/seek on TABLE twice.

The other solution would be to select from the table once, and set 'Test1' or 'Test2' based on the conditions in TABLE:

select 
    case 
        when CCC='D' AND DDD='X' AND exists(select ...) then 'Test1'
        when CCC<>'D' AND DDD='X' AND exists(select ...) then 'Test2'
    end,
    * 
from 
    TABLE 
Where 
    (CCC='D' AND DDD='X' AND exists(select ...)) or
    (CCC<>'D' AND DDD='X' AND exists(select ...))

The catch here being that you will have to duplicate the filter conditions in the CASE statement and the WHERE statement.

SQL: Two select statements in one query

You can do something like this:

 (SELECT
    name, games, goals
    FROM tblMadrid WHERE name = 'ronaldo')
 UNION
 (SELECT
    name, games, goals
    FROM tblBarcelona WHERE name = 'messi')
ORDER BY goals;

See, for example: https://dev.mysql.com/doc/refman/5.0/en/union.html

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

Combining two SELECT statements into one result

One option, perhaps a bit brute force, would be to just use three separate subqueries in a single select:

SELECT
    (SELECT COUNT(*) FROM users WHERE email LIKE '%@rootix.de') AS postbox,
    (SELECT SUM(size) FROM users WHERE email LIKE '%@rootix.de') AS size,
    (SELECT COUNT(*) AS FROM aliases WHERE alias LIKE '%@rootix.de') AS aliases;

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 can I combine two select statements into one table with two seperate columns?

You can do this with conditional aggregation:

SELECT SUM(CASE WHEN Employee.Gender = 'M' THEN 1 ELSE 0 END) AS 'Male Employees',
       SUM(CASE WHEN Employee.Gender = 'F' THEN 1 ELSE 0 END) AS 'Female Employees'  
FROM HumanResources.Employee

But you can do this brutal, straightforward way also:

SELECT (SELECT COUNT(HumanResources.Employee.Gender)
        FROM HumanResources.Employee
        WHERE Employee.Gender = 'M') AS 'Male Employees',
       (SELECT COUNT(HumanResources.Employee.Gender) 
        FROM HumanResources.Employee
        WHERE Employee.Gender = 'F') AS 'Female Employees'

First approach is of course preferred way.

Combining 2 select statements in the same query on one column

Just join on column D:

select a, b, c, A.d, e, f, g 
from tablea A
INNER JOIN tableb B
  ON A.d = B.d

You could use subqueries:

SELECT a, b, c, A.d, e, f, g 
FROM
    (select a,b,c,d from tablea) A
INNER JOIN
    (select d,e,f,g from tableb) B
  ON A.d = B.d

or CTEs (which is a different way to use subqueries):

WITH A AS
(
    select a,b,c,d from tablea;
),
B AS 
(
    select d,e,f,g from tableb;
)
SELECT a, b, c, A.d, e, f, g 
FROM A
INNER JOIN B
  ON A.d = B.d

what my second select does is run an average on several rows and returns a number for that key. It is very taxing when I join on that table.

OK That changes the situation. A subquery or CTE is probably going to be your best bet.

Combine 2 Select queries with different conditions

Modify your second query such as to get the country list per GroupUpdateID (i.e. use GROUP BY):

SELECT
  p.ProviderName, 
  p.Country, 
  p.GroupUpdateID,
  c.result
FROM provider p
JOIN
(
  SELECT GroupUpdateID, GROUP_CONCAT(DISTINCT Country) AS result
  FROM provider 
  GROUP BY GroupUpdateID
) c ON c.GroupUpdateID = p.GroupUpdateID
WHERE p.Country = 'Vietnam'
AND p.ProviderName = 'Provider A';

Or use a correlated subquery in the SELECT clause:

SELECT
  ProviderName, 
  Country, 
  GroupUpdateID,
  (
    SELECT GROUP_CONCAT(DISTINCT c.Country)
    FROM provider c
    WHERE c.GroupUpdateID = p.GroupUpdateID
  ) AS result
FROM provider p
WHERE Country = 'Vietnam'
AND ProviderName = 'Provider A';

---

Related Topics