How to Perform a Left Join in SQL Server Between Two Select Statements

How to perform a LEFT JOIN in SQL Server between two SELECT statements?

SELECT * FROM 
(SELECT [UserID] FROM [User]) a
LEFT JOIN (SELECT [TailUser], [Weight] FROM [Edge] WHERE [HeadUser] = 5043) b
ON a.UserId = b.TailUser

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

SQL: How to Join 2 SELECT statements

you cannot use order by in subquery. You can edit your query. Try this.

select 
    t1.AutoNumber
  , t1.last_autonumber
  , t1.ContactID
  , t2.KeyValue_String
from (
    select 
        la.autonumber
      , last_autonumber = lag(la.autonumber, 1) over (
        partition by la.ContactID 
            order by la.issuedate asc
        )
    , la.ContactID
   from loanagreements la
   --order by la.ContactID desc, la.issuedate asc
   ) as t1
  left join (
   select 
      la.autonumber
    , es.KeyValue_String
    from loanagreements la
    inner join Enum.LoanStatuses es 
      on la.LoanStatus = es.KeyValue_Int
   ) as t2 
    on t1.last_autonumber = t2.autonumber

Joining two select statements together with outer join

Summary

outer join is not valid in SQL Server/t-sql I believe. It should either be left outer join, right outer join or full outer join.

In your situation, I suspect you want it to be a left outer join.

Explanation/longer version

In left and right outer joins, the 'left' and 'right' refer to the tables/etc literally to the left and right on the join (e.g., before and after the join, respectively).

• In a left outer join, it takes all the values from the table on the left (first table) and any matching rows in the table on the right
• In a right outer join, it takes all the values from the table on the right (second table) and any matching rows in the table on the left

A full outer join gets all rows from both tables, and matches them when they can.

Here is an example

/* Data setup */
CREATE TABLE #T1 (T1_ID int);
CREATE TABLE #T2 (T2_ID int);
INSERT INTO #T1 (T1_ID) VALUES (1), (2);
INSERT INTO #T2 (T2_ID) VALUES (1), (3);

/* Example joins */
SELECT #T1.T1_ID, #T2.T2_ID
FROM #T1
LEFT OUTER JOIN #T2 ON #T1.T1_ID = #T2.T2_ID;

SELECT #T1.T1_ID, #T2.T2_ID
FROM #T1
RIGHT OUTER JOIN #T2 ON #T1.T1_ID = #T2.T2_ID;

SELECT #T1.T1_ID, #T2.T2_ID
FROM #T1
FULL OUTER JOIN #T2 ON #T1.T1_ID = #T2.T2_ID;

/* Results
-- LEFT OUTER JOIN
T1_ID   T2_ID
1       1
2       NULL

-- RIGHT OUTER JOIN
T1_ID   T2_ID
1       1
NULL    3

-- FULL OUTER JOIN
T1_ID   T2_ID
1       1
2       NULL
NULL    3
*/

Given you have WHERE s.learners_id in (...) in your WHERE clause, it implies you do not want rows where s.learners would be NULL.

• If you had a right outer join, that WHERE requirement will effectively turn the right outer join into an inner join (as it would exclude all rows where s.learners_id is NULL).
• If you had a full outer join, that WHERE requirement would effectively turn the full outer join into a left outer join - along similar logic lines.

SQL left join on select statements with where clause

This should also work:

select
   tab1.foo_id
  ,tab1.start_date
  ,tab1.end_date
  ,sum(tab2.total) as total
  ,sum(tab2.worker) as worker
from foo tab1
left join bars tab2
  on tab2.work_date between tab1.start_date and tab1.end_date
  and tab1.foo_id = tab2.id
where tab1.rownum <= 10
group by
   tab1.foo_id
  ,tab1.start_date
  ,tab1.end_date

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

join two select statements side by side Sql

you need to use a join to join the two tables together, something like:

select t1.id, t1.Fname, t2.Lname
from Table1 t1
inner join Table2 t2 on t1.id = t2.id

How to join two sql select statements side by side

Try this

SELECT kernel.dog_id, D.name as dog , C.name as cat
FROM appointment A
LEFT JOIN animal D ON  kernel.dog_id=D.animal_id;
LEFT JOIN animal C ON  kernel.cat_id=C.animal_id;

---

Related Topics