SQL Join Two Table

Join Two Tables based on a third table's data

If you don't need any of the contents of the Discount table, use the exists() funtion to execute a sub query in the where clause. This will give you the fastest results.

SELECT * 
FROM Product as a
   left join Price as b on a.Ref# = b.Ref#
WHERE EXISTS (
      SELECT *
      FROM Discount as c
      WHERE c.ProductID = a.ProductID
   )

If however you do need one or more of the columns of Discount, do an inner join between Product and Discount, joining them on the ProductID. This will result in only the products that have discount, and then do another left join to Price to get the columns from Price into the resultset too. Do be aware though that in case multiple rows exist in Discount for the one Product row, this will result in the same product shown on multiple rows.

SELECT * 
FROM Product as a
   inner join Discount as c on c.ProductID = a.ProductID
   left join Price as b on a.Ref# = b.Ref#

SQL - Join two tables and conditionally select rows based on value from a categorical column

Consider below approach

select a.ID, string_agg(CatCol, '' order by if(CatCol = 'blue', 1, 2) limit 1) CatCol
from table_a a left join table_b b
on a.ID = b.ID and CatCol in ('blue', 'green')
group by ID                

if applied to sample data in your question - output is

How to join two tables with three pairs

This is a way you can achieve the result:

select p.First_name as Borrower_FN, p.Last_name as Borrower_LN,
 p2.First_name as resident1_FN, p2.Last_name as resident1_LN,
 p3.First_name as resident2_FN, p3.Last_name as resident2_LN
     from Loans l
     inner join Persons p on p.Person_id = l.Borrower_id
     inner join Persons p2 on p2.Person_id = l.resident1_id
     inner join Persons p3 on p3.Person_id = l.resident2_id

How to correctly join two tables that each have Start Date and Stop Date columns?

Assuming I'm not getting something wrong here, the desired output should be

Start Date	Stop Date	ID	Desk Number	Team Number
01/20	04/20	0100	55	2
02/20	05/20	0100	55	3
03/20	04/20	0100	56	2
03/20	06/20	0100	56	3
02/22	04/22	0200	91	8

Joining and combining two tables that have the same ID for different rows

You have to use UNION, not JOIN for your purposes like this:

CREATE TABLE tab_a (
    id int PRIMARY KEY,
    utc text,
    name text
);
CREATE TABLE tab_b (
    id int PRIMARY KEY,
    utc text,
    name text
);

INSERT INTO tab_a (id, utc, name)
VALUES (1, "utc1", "name1"), (2, "utc2", "name2");

INSERT INTO tab_b (id, utc, name)
VALUES (1, "utc3", "name3"), (2, "utc4", "name4");

SELECT id, name, utc, "tab_a" as tab from tab_a
UNION SELECT id, name, utc, "tab_b" as tab from tab_b;

Response:

id	name	utc	tab
1	name1	utc1	tab_a
1	name3	utc3	tab_b
2	name2	utc2	tab_a
2	name4	utc4	tab_b

SQL Query to Join Two Tables where data in one table is transposed

You could use CTEs to split up the impact from outcome. Also, your screenshot had an error for task_id 6.

with t_impact as (
  select task_id, value
  from table2
  where name = 'task_impact'
  ), 
t_outcome as (
  select task_id, value
  from table2
  where name = 'task_outcome'
  )
select distinct t1.id, 
  t1.title, 
  i.value as task_impact, 
  o.value as task_outcome
from table1 t1
left join t_impact i
  on t1.id = i.task_id
left join t_outcome o
  on t1.id = o.task_id
order by t1.id

DB-fiddle found here.

---

Related Topics

Physical Vs. Logical (Hard Vs. Soft) Delete of Database Record

Find Most Frequent Value in SQL Column

Fastest Way to Remove Non-Numeric Characters from a Varchar in SQL Server

How to Delete from Multiple Tables Using Inner Join in SQL Server

Conditional Count on a Field

How to Include "Zero"/"0" Results in Count Aggregate

How to Set Table Name in Dynamic SQL Query

Simple Query to Grab Max Value For Each Id

Can a Table Field Contain a Hyphen

Dynamic Pivot Columns in SQL Server

How to Specify Condition in Count()

Getting All Buildings in Range of 5 Miles from Specified Coordinates

Difference Between Cte and Subquery

MySQL on Duplicate Key Update For Multiple Rows Insert in Single Query

Getting Only Month and Year from SQL Date

Is There a Max Function in SQL Server That Takes Two Values Like Math.Max in .Net

SQL Select Between Dates

Select Count(*) from Multiple Tables

---

Leave a reply

Submit