mysql join two table rows in one table

Question

I have two tables e_sku and e_availability, In e_sku table i have 45 rows with the default availability as 1, below is an example

id  is_available    name
1   1               UN001N
2   1               UN002N
3   1               UN003N
4   1               UN004N
5   1               UN005N
6   1               UN006N
7   1               UN007N
8   1               UN008N
9   1               UN009N
10  1               UN010N
11  1               UN011N
12  1               UN012N
13  1               UN013N
14  1               UN014N
15  1               UN015N
16  1               UN016N
17  1               UN017N
18  1               UN018N
19  1               UN019N
20  1               UN020N
21  1               UN021N
22  1               UN022N
23  1               UN023N
24  1               UN024N
25  1               UN025N
26  1               UN026N
27  1               UN027N
28  1               UN028N
29  1               UN029N
30  1               UN030N
31  1               UN031N
32  1               UN032N
33  1               UN033N
34  1               UN034N
35  1               UN035N
36  1               UN036N
37  1               UN037N
38  1               UN038N
39  1               UN039N
40  1               UN040N
41  1               UN041N
42  1               UN042N
43  1               UN043N
44  1               UN044N
45  1               UN045N

Second table is e_availability in this table i am only storing the unavailability with dates below is the example

id  e_sku_id    is_available    working_date
1   5           0               10/20/2016
2   8           0               10/20/2016
3   10          0               10/20/2016
4   1           0               10/20/2016
5   15          0               10/20/2016
6   11          0               10/19/2016
7   1           0               10/19/2016

Because the data is capturing every date for unavailability so i am only gathering the unavailable product's data in e_availability table w.r.t. dates, Now i am looking to show the data for each day as complete skus in such a way that every day report will show 45 skus and for available sku it will show 1 from e_sku table's column is_available and for unavailable sku it will show the column of is_availability from e_availability table

I am using mysql database, I tried many join queries but not getting the report. can any one guide about which join i require

I got the result by using this query

SELECT * FROM (SELECT id,NAME,1 AS is_available FROM e_sku
WHERE company_id = 2
AND id NOT IN (SELECT id FROM (SELECT e_sku.id,e_sku.name, edge_availability.is_available FROM edge_availability
JOIN edge_working ON edge_working.`id` = edge_availability.`working_id`
JOIN e_sku ON e_sku.id = edge_availability.`sku_id`
WHERE edge_working.`working_date` = '2016-10-19' AND edge_availability.`store_id` = 84) X)
UNION
SELECT e_sku.id,e_sku.name, edge_availability.is_available FROM edge_availability
JOIN edge_working ON edge_working.`id` = edge_availability.`working_id`
JOIN e_sku ON e_sku.id = edge_availability.`sku_id`
WHERE edge_working.`working_date` = '2016-10-19' AND edge_availability.`store_id` = 84) Y
ORDER BY id

score 0 · Accepted Answer · edited May 23 '17 at 12:06

You can use a LEFT JOIN to detect when a date isn't found in the e_availability table. The join will return NULL for all the columns in that table, then you can default to the value from e_sku.

SELECT d.date, s.id, IFNULL(a.is_available, s.is_available) AS is_available
FROM all_dates AS d
CROSS JOIN e_sku AS s
LEFT JOIN e_availability ON d.working_date = a.date AND s.id = a.e_sku_id

You need to create an additional table all_dates that contains all the dates that you want to report on. See What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)? for how you can create such a table dynamically.

mysql join two table rows in one table

1 Answers1