[SOLVED] Mysql query problem

[otixz]

Hi

 

I have a query like this:

 

select total , (select total from tblinquiries where inq_date='22/04/2008' and course='Comp Mgt') as Total2 from tblinquiries where inq_date='21/04/2008' and course='Comp Mgt'

 

if the column total and total2 has result, the database will show the result. however, if either the total or total2 has a null value, it will not show the result.

 

What I want is at least there will be a number 0 in the column which has a null value.

 

example

 

total | total2

0      | 58

 

or

 

total | total2

58    | 0

 

how can I have this result? so that I can show it in a table in PHP?

[Gamic]

I'm not sure if this is exactly what you want, but I think it'll help get you some of the way there.

 

select o.total, t.total as Total2 
from tblinquiries as o left join tblinquiries as t 
where o.inq_date = '21/04/2008'
and t.inq_date   = '22/04/2008'
and t.course     = 'Comp Mgt'
and t.course     = o.course;

[otixz]

Thank you for your help...however i have a mysql querry error....

 

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where o.inq_date = '21/04/2008' and t.inq_date  = '22/04/2008' and t.course    ' at line 3

(0 ms taken)

 

[Gamic]

Just to figure out where I went wrong I did this:

 

create table tblinquiries(
inq_date char(10),
total int,
course varchar(30));

insert into tblinquiries values('21/04/2008',58,'Comp Mgt');
insert into tblinquiries values('22/04/2008',null,'Comp Mgt');

 

This query seems to work:

 

select ifnull(o.total,0) as total, ifnull(t.total,0) as Total2 
from tblinquiries as o left join tblinquiries as t on t.course=o.course
where o.inq_date = '21/04/2008'
and t.inq_date   = '22/04/2008'
and t.course     = 'Comp Mgt';

output:
total | total2
    58|      0

[otixz]

Thank you!

that answered my question...

 

But I have a harder problem

 

Let's say that there is no 04/21/2008 date...

can it still produce the same 2 columned result?

 

total | total2

    0      |  25 // example there is no date of 04/21/2008 for the course 'Comp Mgt'

 

Thank you very much

 

 

 

 

 

[Gamic]

select 
ifnull(
	(select o.total from tblinquiries o where o.inq_date='21/04/2008' and o.course='Comp Mgt')
,0) as total, 
ifnull(
	(select t.total from tblinquiries t where t.inq_date='23/03/2008' and t.course='Comp Mgt')
,0) as Total2;

+-------+--------+
| total | Total2 |
+-------+--------+
|    58 |      0 |
+-------+--------+
1 row in set (0.00 sec)

mysql> select * from tblinquiries;
+------------+-------+----------+
| inq_date   | total | course   |
+------------+-------+----------+
| 21/04/2008 |    58 | Comp Mgt |
| 22/04/2008 |  NULL | Comp Mgt |
+------------+-------+----------+
2 rows in set (0.00 sec)

[otixz]

Wow! that really works! thank you very much! I really hope I can learn more from you guys! Thanks!