You'll need to first create a list of every product_number
and date
combination. You can do this using a CROSS JOIN
of your table:
select distinct p.product_number, d.date
from yourtable p
cross join yourtable d;
See SQL Fiddle with Demo. This will create a list of data similar to:
| PRODUCT_NUMBER | DATE |
|----------------|---------------------------------|
| 100 | January, 01 2010 00:00:00+0000 |
| 200 | January, 01 2010 00:00:00+0000 |
| 100 | February, 01 2010 00:00:00+0000 |
You will then use the above query and LEFT JOIN
to your table to return the final result:
select
pd.product_number,
pd.date,
t.value
from
(
-- list of every product/date
select distinct p.product_number, d.date
from yourtable p
cross join yourtable d
) pd
left join yourtable t
on pd.date = t.date
and pd.product_number = t.product_number
order by pd.product_number, pd.date;
See SQL Fiddle with Demo. Giving a final result of:
| PRODUCT_NUMBER | DATE | VALUE |
|----------------|---------------------------------|--------|
| 100 | January, 01 2010 00:00:00+0000 | 1 |
| 100 | February, 01 2010 00:00:00+0000 | 1 |
| 100 | March, 01 2010 00:00:00+0000 | 1 |
| 200 | January, 01 2010 00:00:00+0000 | 1 |
| 200 | February, 01 2010 00:00:00+0000 | (null) |
| 200 | March, 01 2010 00:00:00+0000 | 1 |
The LEFT JOIN
returns all rows from your list of products and dates regardless of whether a matching row exists in the other table.
This could also be written as:
select
p.product_number,
d.date,
t.value
from
(
-- list of every product
select distinct product_number
from yourtable
) p
cross join
(
-- list of every date
select distinct date
from yourtable
) d
-- then join to the table
left join yourtable t
on d.date = t.date
and p.product_number = t.product_number
order by p.product_number, d.date ;
See SQL Fiddle with Demo. This may have better performance depending on your table size.
Now if you wanted to return a list of all dates, regardless of whether or not they appear in the table, then I would suggest creating a table of dates. This table would be used in a similar manner to create a list of all dates/products which you would then join.
The table would be similar to:
CREATE TABLE dates
(`date` datetime)
;
INSERT INTO dates
(`date`)
VALUES
('2010-01-01 00:00:00'),
('2010-02-01 00:00:00'),
('2010-03-01 00:00:00'),
('2010-04-01 00:00:00'),
('2010-05-01 00:00:00')
;
You'd then use the following query to get the list of dates/products:
select distinct p.product_number, d.date
from yourtable p
cross join dates d
And finally, you would join that back to your table:
select
pd.product_number,
pd.date,
t.value
from
(
-- list of every product/date
select distinct p.product_number, d.date
from yourtable p
cross join dates d
) pd
left join yourtable t
on pd.date = t.date
and pd.product_number = t.product_number
order by pd.product_number, pd.date;
See SQL Fiddle with Demo. Or an alternative:
select
p.product_number,
d.date,
t.value
from
(
-- list of every product
select distinct product_number
from yourtable
) p
cross join
dates
d
-- then join to the table
left join yourtable t
on d.date = t.date
and p.product_number = t.product_number
order by p.product_number, d.date ;
See SQL Fiddle with Demo. Again this may have better performance based on the table size. Using this type of solution, you'd return all dates even those not in your table:
| PRODUCT_NUMBER | DATE | VALUE |
|----------------|---------------------------------|--------|
| 100 | January, 01 2010 00:00:00+0000 | 1 |
| 100 | February, 01 2010 00:00:00+0000 | 1 |
| 100 | March, 01 2010 00:00:00+0000 | 1 |
| 100 | April, 01 2010 00:00:00+0000 | (null) |
| 100 | May, 01 2010 00:00:00+0000 | (null) |
| 200 | January, 01 2010 00:00:00+0000 | 1 |
| 200 | February, 01 2010 00:00:00+0000 | (null) |
| 200 | March, 01 2010 00:00:00+0000 | 1 |
| 200 | April, 01 2010 00:00:00+0000 | (null) |
| 200 | May, 01 2010 00:00:00+0000 | (null) |
Perhaps it is easier to see with the aid of some rewrites. Your first query can be rewritten to (using the same alias at different levels makes it a bit difficult to understand):
select *
from parent p1
where exists (
select 1
from parent p2
join child c on c.parentId = p2.id
where p2.age > 50
and p1.id = p2.id
)
Assuming id is unique in parent this can be rewritten as:
select *
from parent p1
where exists (
select 1
from child c
where c.parentId = p1.id
and p1.age > 50
)
which in turn can be rewritten to your query.
So you can eliminate the join against parent inside the sub-select. However, your second query is a bit difficult to understand as it is written. I would suggest (I'm guessing your intention)
select *
from parent p1
where exists (
select 1
from child c
where c.parentId = p1.id
)
and p1.age > 50
If parent.id is not unique the above wont hold
Best Answer
An alternative @RDFozz's answer would be to use
FILTER
:I think it is bit more intuitive, as this is the exact reason for
FILTER
to exist so its intention is more obvious than usingCASE
.