[fixed] Urgent SQL question: Is it possible to do this?

[Ken Shamrock]

I have two tables- table1 & table2

Table1                          Table2
+-----------+--------+          +-----------+----------+------+
| ip        | name   |          | ip        | time     | hits |
+-----------+--------+          +-----------+----------+------+
| 127.0.0.1 | local1 |          | 127.0.0.1 | 2002     | 100  |
| 127.0.0.2 | local2 |          | 127.0.0.1 | 2002     | 200  |
| 127.0.0.3 | local3 |          | 127.0.0.2 | 2003     | 300  |
+-----------+--------+          +-----------+----------+------+

I want to join them with 127.0.0.3 SHOWN in NEW TABLE even it have no
hits (in table2)
mysql> select a.ip,name,time,sum(hits) from table1 a left join table2 b
on a.ip=
b.ip group by a.ip,a.name;

+-----------+--------+------+-----------+
| ip        | name   | time | sum(hits) |
+-----------+--------+------+-----------+
| 127.0.0.1 | local1 | 2002 |       300 |
| 127.0.0.2 | local2 | 2003 |       300 |
| 127.0.0.3 | local3 | NULL |         0 |
+-----------+--------+------+-----------+

But if I specify the time ( > 2000 ) using where time>'2001' , I can't
get what my want (Also show 127.0.0.3)
What can the SQL do to do what I want? Many thanks!!
mysql> select a.ip,name,time,sum(hits) from table1 a left join table2 b
on a.ip=
b.ip where time>'2001' OR time=null group by a.ip,a.name;

+-----------+--------+------+-----------+
| ip        | name   | time | sum(hits) |
+-----------+--------+------+-----------+
| 127.0.0.1 | local1 | 2002 |       300 |
| 127.0.0.2 | local2 | 2003 |       300 |
+-----------+--------+------+-----------+

pls read here if you cant see above:
http://www.hk3s.com/db.txt
[ edited to preserve formatting using the [code] and [/code] UBB tags -ds ]
[ July 18, 2003: Message edited by: Dirk Schreckmann ]

[Leslie Chaim]

At least in Oracle you would say:

where time [B]is[/B] null

I am not sure about mysql
Please let us/me know.

[Praveen Garimella]

Please send the table descirptions i.e., using desc table1 and desc table2. And can we also know the mysql version you are using?

[Ken Shamrock]

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip    | char(10) | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.03 sec)

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip    | char(10) | YES  |     | NULL    |       |
| time  | char(10) | YES  |     | NULL    |       |
| hits  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

I want to join them with 127.0.0.3 SHOWN in NEW TABLE even it have no
hits (in table2)
mysql> select a.ip,name,time,sum(hits) from table1 a left join table2 b
on a.ip=
b.ip group by a.ip,a.name;

+-----------+--------+------+-----------+
| ip        | name   | time | sum(hits) |
+-----------+--------+------+-----------+
| 127.0.0.1 | local1 | 2002 |       300 |
| 127.0.0.2 | local2 | 2003 |       300 |
| 127.0.0.3 | local3 | NULL |         0 |
+-----------+--------+------+-----------+

But if I specify the time ( > 2000 ) using where time>'2001' , I can't
get what my want (Also show 127.0.0.3)
What can the SQL do to do what I want? Many thanks!!
mysql> select a.ip,name,time,sum(hits) from table1 a left join table2 b
on a.ip=
b.ip where time>'2001' OR time=null group by a.ip,a.name;

+-----------+--------+------+-----------+
| ip        | name   | time | sum(hits) |
+-----------+--------+------+-----------+
| 127.0.0.1 | local1 | 2002 |       300 |
| 127.0.0.2 | local2 | 2003 |       300 |
+-----------+--------+------+-----------+

PS: Pls be remind that this all can be do in MSSQL but not MYSQL. Thanks
[ edited to preserve formatting using the [code] and [/code] UBB tags -ds ]
[ July 18, 2003: Message edited by: Dirk Schreckmann ]

[Ken Shamrock]

I made this one, seem to be work, need to test:
SELECT a.ip, name, SUM(hits)
FROM table1 a LEFT JOIN
table2 b ON a.ip = b.ip
WHERE time > '2001' OR
time = NULL
GROUP BY a.ip, a.name
UNION
SELECT c.ip, c.name, 0
FROM table1 c
WHERE NOT EXISTS
(SELECT 'X'
FROM table2 d
WHERE d .ip = c.ip)