SQL Join on where condition

filter condition in join vs where clause
sql join multiple tables with conditions
sql join on multiple conditions
sql inner join with where clause
sql left join
inner join vs where clause performance
sql join types
sql on clause

How do I join these two tables

select m.caseno,
sum(m.BalanceAmount) GOVT from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('a','b','c') 
group by m.caseno

OUTPUT CASE | GOVT

select m.caseno,
sum(m.BalanceAmount) MIF from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('d','e')
group by m.caseno

OUTPUT CASE | MIF

I want them to join in a single query CASE | GOVT | MIF


use case when to show as 2 columns.

select m.caseno,
sum(case when g.alpha in ('d','e') then m.BalanceAmount else 0 end)  MIF ,
sum(case when g.alpha in ('a','b','c') then m.BalanceAmount else 0 end)  GOVT 
from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('a','b','c','d','e')
group by m.caseno

SQL JOIN - WHERE clause vs. ON clause, The way I do it is: Always put the join conditions in the ON clause if you are doing an INNER JOIN . So, do not add any WHERE conditions to the ON clause, put them in the WHERE clause. If you are doing a LEFT JOIN , add any WHERE conditions to the ON clause for the table in the right side of the join. Putting the condition in the join seems "semantically wrong" to me, as that's not what JOINs are "for". But that's very qualitative. Additional problem: if you decide to switch from an inner join to, say, a right join, having the condition be inside the JOIN could lead to unexpected results.


If you don't want to touch the existing queries, you can do a FULL OUTER JOIN with them:

select
  coalesce(a.caseno, b.caseno) as caseno,
  a.govt,
  b.mif
from ( -- first query
  select m.caseno,
  sum(m.BalanceAmount) GOVT from TABLE1 m
  inner join TABLE2 g on m.Code = g.Code
  where g.alpha in ('a','b','c') 
  group by m.caseno
) a
full outer join ( -- second query
  select m.caseno,
  sum(m.BalanceAmount) MIF from TABLE1 m
  inner join TABLE2 g on m.Code = g.Code
  where g.alpha in ('d','e')
  group by m.caseno
) b on a.caseno = b.caseno

The FULL OUTER JOIN is necessary -- as well as the COALESCE() -- to join non-matching rows that may show up in either queries.

Difference between WHERE and ON in SQL to JOIN data, Is there a difference between the WHERE and ON clause? Yes. ON should be used to define the join condition and WHERE should be used to� Conditional Joins in SQL Server. Tue Apr 3, 2007 by Jeff Smith in t-sql, report-writing, joins-relations. Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort, so that the join processed will vary for each row depending on the data.


you have to perform a full outer join i think. try this.

SELECT *
FROM (
  select m.caseno,
    sum(m.BalanceAmount) GOVT from TABLE1 m
  inner join TABLE2 g on m.Code = g.Code
  where g.alpha in ('a','b','c') 
  group by m.caseno
) AS tab1
FULL OUTER JOIN (
  select m.caseno,
    sum(m.BalanceAmount) MIF from TABLE1 m
  inner join TABLE2 g on m.Code = g.Code
  where g.alpha in ('d','e')
  group by m.caseno
) AS tab2 
  ON tab2.caseno = tab1.caseno

SQL Server LEFT JOIN By Practical Examples, This tutorial introduces you to the SQL Server LEFT JOIN clause and shows you how to use it to query data from multiple associated tables. It is a sql join condition which makes use of some comparison operator other than the equal sign like >, , >=, = 1) SQL Equi Joins: An equi-join is further classified into two categories: a) SQL Inner Join b) SQL Outer Join . a) SQL Inner Join: All the rows returned by the sql query satisfy the sql join condition specified. SQL Inner Join Example:


The Difference Between SQL's JOIN .. ON Clause and the Where , A question that is frequently occurring among my SQL training's participants is: What's the difference between putting a predicate in the JOIN . So what we really need is to join on number and letter for 1's and number only for 2's. Enter the conditional column join. A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join.


Specifying a Join in the WHERE Clause, You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row. A join condition defines the way two tables are related in a query by: Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table. Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.


SQL INNER JOIN, An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables. Syntax: SELECT * FROM table1 INNER JOIN table2 ON� Basic SQL Join Types. There are four basic types of SQL joins: inner, left, right, and full. The easiest and most intuitive way to explain the difference between these four types is by using a Venn diagram, which shows all possible logical relations between data sets.