Question 20724 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL query about a right or full join with date table – Mysql

I am new to the sql world and trying to archive a certain result.

I have two tables: Table A contains, among other things, the following columns: Market_ID, Date, Event

| Market_ID | Date           | Event |
| --------  | -------------- |------ |
| 1         | 2023-01-06     | F     |
| 1         | 2023-04-07     | F     |
| 2         | 2023-01-06     | F     |

where the column event also contains other strings than ‘F’, but only ‘F’ is relevant for me. In addition, the column date does not contain all dates within a year. It only has an entry if there is an event on this day.

Table B is a date table just containing all dates in different formats within a year from the past and future

| Date_Day   | Date_Week      |        
| ---------- | -------------- |
| 2023-01-01 | 2023/01        |
| 2023-01-02 | 2023/01        |
| 2023-01-03 | 2023/01        |
| 2023-01-04 | 2023/01        |
| 2023-01-05 | 2023/01        |
| 2023-01-06 | 2023/01        |
| ...        | ...            |

My expected output should be a table containing all dates and an additional column whether there was an Event F on that day or not:

| Market_ID | Date           | Is_EVENT_F|
| --------  | -------------- |---------- |
| 1         | 2023-01-01     | 0         |
| 1         | 2023-01-02     | 0         |
| 1         | 2023-04-03     | 0         |
| 1         | 2023-04-04     | 0         |
| 1         | 2023-04-05     | 0         |
| 1         | 2023-01-06     | 1         |

I have tried using either an right or outer join but in both cases, the result was not correct.

Tags:

5

Answers


  1. 0 
    SELECT A.Market_ID, B.Date_Day AS Date, 
       CASE WHEN A.Event = 'F' THEN 1 ELSE 0 END AS Is_EVENT_F
FROM TBL_Date B
LEFT JOIN TBL_MarketPlace A ON B.Date_Day = A.Date
    Login or Signup to reply.
  2. 0 
    SELECT markets.market_id,
       dates.date_day `date`,
       EXISTS ( SELECT NULL
                FROM table_a
                WHERE market_id = markets.market_id 
                  AND date1 = dates.date_day
                  AND event = 'F' ) is_event_f
FROM table_b dates
CROSS JOIN ( SELECT DISTINCT market_id 
             FROM table_a ) markets
-- ORDER BY ...

    Subquery markets gather all markets list. If markets table exisrts then use it instead.

    Joining this subquery and dates list generates all possible pairs market-date.

    Subquery in EXISTS checks does the row with 'F' exists for each market at each date.

    https://dbfiddle.uk/-CVLgaV4

    Login or Signup to reply.
  3. 0 
    SELECT 
    A.Market_ID, 
    B.Date_Day as Date, 
    CASE WHEN A.Event = 'F' 
        THEN 1
        ELSE 0
    END as Is_EVENT_F
FROM TABLE_B B
LEFT JOIN TABLE_A A
ON A.Date = B.Date_day

    But this query is obviously going to return null Market_id for the dates in which there is no record in TABLE_A.

    Login or Signup to reply.
  4. 0

    I’ll use Inner SELECT statement:

    SELECT
    market_id
    , b.date_day
    , IFNULL((SELECT 1 FROM table_a a2 WHERE to_char(a2.date_day) = to_char(b.date_day) AND a2.market_id = a.market_id), 0) is_event_f
FROM
    table_b b
        CROSS JOIN table_a a;
    Login or Signup to reply.
  5. 0

    You could try this query

    SELECT C.Market_ID, B.Date_Day, 
       CASE WHEN A.Event IS NULL THEN 0 ELSE 1 END AS Is_EVENT_F  
FROM table_b B 
     CROSS JOIN (SELECT DISTINCT Market_ID FROM table_a) C
     LEFT JOIN table_a A ON (A.Market_ID = C.Market_ID AND A.Date1 = B.Date_Day AND Event = 'F') 
ORDER BY C.Market_ID, B.Date_Day
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search