There is my problem :
enter code here
I have a table called test that looks like this
id| service | sub service | Qt | date
1 | service_1 | sub_service_11 | 3 | 2011-12-03
2 | service_1 | sub_service_12 | 6 | 2011-12-03
3 | service_1 | sub_service_13 | 4 | 2011-12-03
Later I have a table called datedim that looks like this
id| date
1 | 2011-12-01
2 | 2011-12-02
3 | 2011-12-03
4 | 2011-12-04
5 | 2011-12-05
What I am trying to do is that for each sub_service bring back all the date from datedim even if开发者_开发技巧 there is no match.
So basically something that would look like this
sub_service_11 | 2011-12-01 | NULL
sub_service_11 | 2011-12-02 | NULL
sub_service_11 | 2011-12-03 | 3
sub_service_11 | 2011-12-04 | NULL
sub_service_11 | 2011-12-05 | NULL
sub_service_12 | 2011-12-01 | NULL
sub_service_12 | 2011-12-02 | NULL
sub_service_12 | 2011-12-03 | 6
sub_service_12 | 2011-12-04 | NULL
sub_service_12 | 2011-12-05 | NULL
sub_service_13 | 2011-12-01 | NULL
sub_service_13 | 2011-12-02 | NULL
sub_service_13 | 2011-12-03 | 4
sub_service_13 | 2011-12-04 | NULL
sub_service_13 | 2011-12-05 | NULL
I did try RIGHT JOIN, UNIONS and stuff but I can't figure it out.
Does anyone know how I can accomplish that ?
Thank you,
This is what you need:
select
ss.sub_service,
dd.date,
ts.qt
from
(datedim dd, (select distinct sub_service from test) ss)
left join test ts on (dd.date = ts.date and ts.sub_service = ss.sub_service)
order by ss.sub_service, dd.date
As you have a table containing each sub_service (lets call it sub_service_list) you should be able to do the following:
SELECT
s.sub_service, d.date, t.Qt
FROM (sub_service_list s,datedim d)
LEFT JOIN test t ON s.sub_service=t.sub_service AND d.date=t.date
you can do this:
select t.id, sub_service, case when d.date = t.date then qt else null end as qt
from test t cross join datedim d
order by sub_service, d.date
精彩评论