This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.
My first suggestion would be to determine if you have a calendar table or a table that contains all of the dates that you want to display. If not, then I would suggest creating one similar to the following:
CREATE TABLE calendar (`Date` datetime) ;
INSERT INTO calendar (`Date`)
VALUES
('2013-06-01 00:00:00'),
('2013-06-02 00:00:00'),
('2013-06-03 00:00:00'),
('2013-06-04 00:00:00'),
('2013-06-05 00:00:00'),
('2013-06-06 00:00:00'),
('2013-06-07 00:00:00'),
('2013-06-08 00:00:00'),
('2013-06-09 00:00:00'),
('2013-06-10 00:00:00');
This will make it possible for you to generate a list of all of the dates that you want to display.
Second, you will need to generate the list of each student and each date. You can do this by using a CROSS JOIN between your tbl_admission and the calendar table:
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a;
See Demo. Once you have this list, then you can use a LEFT JOIN to your existing tbl_absentees table to get the result:
select
ca.studentname,
ca.rollno,
ca.class,
max(case when ca.date = '2013-06-01' then coalesce(p.status, 'P') end) `2013-06-01`,
max(case when ca.date = '2013-06-02' then coalesce(p.status, 'P') end) `2013-06-02`,
max(case when ca.date = '2013-06-03' then coalesce(p.status, 'P') end) `2013-06-03`,
max(case when ca.date = '2013-06-04' then coalesce(p.status, 'P') end) `2013-06-04`,
max(case when ca.date = '2013-06-05' then coalesce(p.status, 'P') end) `2013-06-05`,
max(case when ca.date = '2013-06-06' then coalesce(p.status, 'P') end) `2013-06-06`,
max(case when ca.date = '2013-06-07' then coalesce(p.status, 'P') end) `2013-06-07`,
max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-08`,
max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-09`,
max(case when ca.date = '2013-06-10' then coalesce(p.status, 'P') end) `2013-06-10`
from
(
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a
) ca
left join tbl_absentees p
on ca.rollno = p.rollno
and ca.date = p.date
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno;
See SQL Fiddle with Demo. Of course for your request, you most likely want to query the data based on a date range so you will not want to hard-code the values. If that is the case, then you will need to look at using a prepared statement to generate dynamic SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(CASE WHEN ca.date = ''',
date_format(date, '%Y-%m-%d'),
''' THEN coalesce(p.status, ''P'') END) AS `',
date_format(date, '%Y-%m-%d'), '`'
)
) INTO @sql
FROM calendar
where date>='2013-06-01'
and date <= '2013-06-05';
SET @sql
= CONCAT('SELECT ca.studentname,
ca.rollno,
ca.class, ', @sql, '
from
(
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a
) ca
left join tbl_absentees p
on ca.rollno = p.rollno
and ca.date = p.date
where ca.date>=''2013-06-01''
and ca.date <= ''2013-06-05''
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo. Both of these queries will give a result similar to:
| STUDENTNAME | ROLLNO | CLASS | 2013-06-01 | 2013-06-02 | 2013-06-03 | 2013-06-04 | 2013-06-05 | 2013-06-06 | 2013-06-07 | 2013-06-08 | 2013-06-09 | 2013-06-10 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Naren | 1 | 22 | A | A | A | A | P | P | P | P | P | P |
| Srinu | 2 | 22 | P | P | P | P | P | P | P | P | P | P |
| Blah | 3 | 22 | A | P | P | P | P | P | P | P | P | P |
First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:
SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
Now you can build a join to retrieve not only sid, but all of the data of tablethree:
SELECT a.*
FROM tablethree a
INNER JOIN (
SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate
This is the result set you actually want to "left join in". You have to insert this into your original query:
SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
ON t1.cid = t2.id
LEFT OUTER JOIN (
SELECT a.*
FROM tablethree a
INNER JOIN (
SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
ON t3.sid = t2.sid
WHERE t1.fieldone = 1
AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
What should also work is the following:
SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
ON t3.sid = t2.sid
LEFT OUTER JOIN (
SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate
WHERE t1.fieldone = 1
AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
AND mostrecent.enddate IS NULL
This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.
Best Answer
If you want all the rows that have Orders and Payments but maybe don't have user go for the Inner Join/Left join
If all the data must match, go for this:
If you are talking about query projection (columns to be returned regardless of the matching data) go for select pg.*