CODEWITHSUNDEEP
mysqlsql
John Smith
John Smith

Reputation: 209

How to join two tables in one query?

How do I get table C from table A and table B in one select statement? Thanks.

Table A -
Product, Date, Price 
P1, 2018-01-01, 10
P2, 2018-01-02, 20

Table B - 
Date
2018-01-01
2018-01-02
2018-01-03
2018-01-04

Table C
Product, Date, Price
P1, 2018-01-01, 10 
P1, 2018-01-02, null
P1, 2018-01-03, null
P1, 2018-01-04, null
P2, 2018-01-01, null
P2, 2018-01-02, 20 
P2, 2018-01-03, null
P2, 2018-01-04, null

Upvotes: 0

Views: 93

Answers (3)

Barmar
Barmar

Reputation: 782564

CROSS JOIN will create a full cross product between the tables. Then you can fill in the Price column when the dates match.

SELECT
  Product,
  TableB.Date,
  IF(TableA.Date = TableB.Date, Price, NULL END) AS Price
FROM TableA
CROSS JOIN TableB
ORDER BY Product, Date

DEMO

Upvotes: 2

sia
sia

Reputation: 577

since you need all the rows from table A with all the dates in tables B, you need to use FULL OUTER JOIN:

SELECT * FROM Table_A a
FULL OUTER JOIN Table_B b
ON a.date = b.date

but since, there is no full outer join in My sql you can emulate it by using this post: How to do a FULL OUTER JOIN in MySQL? in MYSQL:

SELECT * 
FROM Table_A a
LEFT JOIN Table_B b ON a.date=B.date
UNION ALL
SELECT * 
FROM Table_A a 
RIGHT JOIN table_B b
ON a.date=b.date

Upvotes: 0

Fabian Peñaloza
Fabian Peñaloza

Reputation: 1

select ta.product , tb.Date , ta.Price from TableA ta, Table

Upvotes: -1

Related Questions