how to join all three query results to one in mysql
-
hi i have written 3 different select query in mysql based on yesterday,today, tommorow dates to get the birthday details as follows select concat(name,', ',title) as 'Yesterday 30-04-2014' from personal_details where DOB=curdate() - interval 1 day select concat(name,', ',title) as 'Today 01-05-2014' from personal_details where DOB=DATE(NOW()) select concat(name,', ',title) as 'Tommorow 02-05-2014' from personal_details where DOB=curdate() + interval 1 day how to join all 3 query results to one, i need to show as below Yesterday Today Tomorrow aaaa gggg nnnnn bbbb hhhhh mmmm dddd jjjjj eeee ffff How to achieve this, i am not able to do. If i tried with join there it wont match any condition and will not work because in each query result i will get unique values. I tried with another query by putting as below select a.* from (select CASE DOB WHEN (curdate() - interval 1 day) THEN concat(name,', ',title) ELSE '' END AS 'Yesterday', CASE DOB WHEN (DATE(NOW())) THEN concat(name,', ',title) ELSE '' END AS 'Today',CASE DOB WHEN (curdate() + interval 1 day) THEN concat(name,', ',title) ELSE '' END AS 'Tomorrow' from personal_details ) as a where a.Yesterday IS NOT NULL and a.Today IS NOT NULL and a.Tomorrow IS NOT NULL It is showing the result like in all the three column some rows will be null I dont want null values in any column. i want out as above. How to achieve this. If anybody knows please reply me. Thanks in advance.
-
hi i have written 3 different select query in mysql based on yesterday,today, tommorow dates to get the birthday details as follows select concat(name,', ',title) as 'Yesterday 30-04-2014' from personal_details where DOB=curdate() - interval 1 day select concat(name,', ',title) as 'Today 01-05-2014' from personal_details where DOB=DATE(NOW()) select concat(name,', ',title) as 'Tommorow 02-05-2014' from personal_details where DOB=curdate() + interval 1 day how to join all 3 query results to one, i need to show as below Yesterday Today Tomorrow aaaa gggg nnnnn bbbb hhhhh mmmm dddd jjjjj eeee ffff How to achieve this, i am not able to do. If i tried with join there it wont match any condition and will not work because in each query result i will get unique values. I tried with another query by putting as below select a.* from (select CASE DOB WHEN (curdate() - interval 1 day) THEN concat(name,', ',title) ELSE '' END AS 'Yesterday', CASE DOB WHEN (DATE(NOW())) THEN concat(name,', ',title) ELSE '' END AS 'Today',CASE DOB WHEN (curdate() + interval 1 day) THEN concat(name,', ',title) ELSE '' END AS 'Tomorrow' from personal_details ) as a where a.Yesterday IS NOT NULL and a.Today IS NOT NULL and a.Tomorrow IS NOT NULL It is showing the result like in all the three column some rows will be null I dont want null values in any column. i want out as above. How to achieve this. If anybody knows please reply me. Thanks in advance.
Firstly to add your 3 queries together to act as a single result set you should look into the UNION[^] keyword. To organise the layout so that the column headers become Yesterday, Today and Tomorrow you need to look into PIVOT[^] this example should show you how to create one.
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
Firstly to add your 3 queries together to act as a single result set you should look into the UNION[^] keyword. To organise the layout so that the column headers become Yesterday, Today and Tomorrow you need to look into PIVOT[^] this example should show you how to create one.
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON