Combine Multiple Select Statements

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 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 combine multiple select statement and limit for each of them

The required syntax is to wrap the individual limit queries in brackets like the following

(select id, title from arts where cat = 'lorem' order by ind asc limit 15)
union
(select id, title from arts where cat = 'ipsum' order by ind asc limit 15)
union
(select id, title from arts where cat = 'dolor' order by ind asc limit 15)

From the documentation:

To apply an ORDER BY or LIMIT clause to an individual SELECT [in a
union], parenthesize the SELECT and place the clause inside the
parentheses...

Merge multiple select statements and group them

It looks like you want count grouped by days:

select
    tbls.date,
    sum(tbls.num_created) as num_created,
    sum(tbls.num_updated) as num_updated,
    sum(tbls.num_deleted) as num_deleted 
from (
    select date, num_created, 0 as num_updated, 0 as num_deleted from tbl_create
    union all
    select date, 0 as num_created, num_updated, 0 as num_deleted from tbl_update
    union all
    select date, 0 as num_created, 0 as num_updated, num_deleted from tbl_delete) as tbls
group by tbls.date

Please don't abuse "with statement" when it is not needed (as seen in the other answers).

Edit - extra simple query:

    if object_id('tempdb..#tmp_dml_statement') is not null drop table #tmp_dml_statement 
    select date, num_created, 0 as num_updated, 0 as num_deleted
    into #tmp_dml_statement
    from tbl_create
    union all
    select date, 0 as num_created, num_updated, 0 as num_deleted
    from tbl_update
    union all
    select date, 0 as num_created, 0 as num_updated, num_deleted
    from tbl_delete

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);

MySQL - Joining multiple select statements

Simple inner join and a litle math should do the trick

CREATE TABLE Subcode
    (`Maincode` int, `Controlcode` int, `Subcode` int, `Description` varchar(12))
;
    
INSERT INTO Subcode
    (`Maincode`, `Controlcode`, `Subcode`, `Description`)
VALUES
    (01, 01, 123, 'Test Account'),
    (01, 02, 124, 'Test Account')
;

✓

✓

CREATE TABLE Voucher
    (`Voucherdate` Date, `Vouchertype` varchar(2), `Vouchernumber` int, `Maincode` int, `Controlcode` int, `Subcode` int, `Description` varchar(14), `Debit` int, `Credit` int)
;
    
INSERT INTO Voucher
    (`Voucherdate`, `Vouchertype`, `Vouchernumber`, `Maincode`, `Controlcode`, `Subcode`, `Description`, `Debit`, `Credit`)
VALUES
    ('2019-07-13', 'BV', 01, 01, 01, 123, 'Entering Test', 100, 0),
    ('2019-07-13', 'BV', 01, 01, 02, 124, 'Enterting Test', 0, 100)
;

✓

✓

SELECT * FROM Voucher;

Voucherdate | Vouchertype | Vouchernumber | Maincode | Controlcode | Subcode | Description    | Debit | Credit
:---------- | :---------- | ------------: | -------: | ----------: | ------: | :------------- | ----: | -----:
2019-07-13  | BV          |             1 |        1 |           1 |     123 | Entering Test  |   100 |      0
2019-07-13  | BV          |             1 |        1 |           2 |     124 | Enterting Test |     0 |    100

CREATE TABLE OpeningBalance
    (`Maincode` int, `Controlcode` int, `Subcode` int, `Debitbalance` int, `Creditbalance` int)
;
    
INSERT INTO OpeningBalance
    (`Maincode`, `Controlcode`, `Subcode`, `Debitbalance`, `Creditbalance`)
VALUES
    (01, 01, 123, 100, 0),
    (01, 02, 124, 100, 0)
;

✓

✓

SELECT 
s.Description
  ,o.Maincode
  ,o.Controlcode
  ,o.Subcode
  , o.`Debitbalance` Mainbalance
 , v.`Debit` + o.`Debitbalance` Totaldebit 
 , v.`Credit` + o.`Creditbalance` Totalcredit 
 , v.`Debit` + o.`Debitbalance` -( v.`Credit` + o.`Creditbalance`)  Openingbalance 
FROM OpeningBalance o inner Join Voucher v 
ON o.Maincode = v.Maincode AND o.Controlcode = v.Controlcode AND o.Subcode = v.Subcode
INNER JOIN Subcode s ON o.Maincode = s.Maincode AND o.Controlcode = s.Controlcode AND o.Subcode = s.Subcode
WHERE  MONTH(v.voucherdate) <= 7 AND YEAR(v.voucherdate) <= 2019

Description  | Maincode | Controlcode | Subcode | Mainbalance | Totaldebit | Totalcredit | Openingbalance
:----------- | -------: | ----------: | ------: | ----------: | ---------: | ----------: | -------------:
Test Account |        1 |           1 |     123 |         100 |        200 |           0 |            200
Test Account |        1 |           2 |     124 |         100 |        100 |         100 |              0

SELECT (SELECT sc.description AS description, 
           sc.maincode, 
           sc.controlcode, 
           sc.subcode, 
           ( ob.debitbalance - ob.creditbalance ) AS mainbalance 
    FROM   Subcode AS sc 
           LEFT JOIN OpeningBalance AS ob 
                  ON ( sc.maincode = ob.maincode 
                       AND sc.controlcode = ob.controlcode 
                       AND sc.subcode = ob.subcode ) 
    GROUP  BY sc.maincode, 
              sc.controlcode, 
              sc.subcode 
    ORDER  BY sc.maincode, 
              sc.controlcode, 
              sc.subcode ASC) AS test, 
   (SELECT Sum(v.debit)                               AS totaldebit, 
           Sum(v.credit)                              AS totalcredit, 
           ( mainbalance + totaldebit - totalcredit ) AS openingbalance 
    FROM   Subcode AS sc 
           LEFT JOIN Voucher AS v 
                  ON ( sc.maincode = v.maincode 
                       AND sc.controlcode = v.controlcode 
                       AND sc.subcode = v.subcode ) 
    WHERE  Substring(v.voucherdate, 1, 7) < '07-2019' 
    GROUP  BY sc.maincode, 
              sc.controlcode, 
              sc.subcode 
    ORDER  BY sc.maincode, 
              sc.controlcode, 
              sc.subcode ASC) AS test2 

Operand should contain 1 column(s)

SELECT Substring(voucherdate, 1, 7) FROM Voucher

| Substring(voucherdate, 1, 7) |
| :--------------------------- |
| 2019-07                      |
| 2019-07                      |

db<>fiddle here

---

Related Topics