By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,537 Members | 2,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,537 IT Pros & Developers. It's quick & easy.

How can i avoid a subquery here..

P: n/a
Hello..

I am working on a PHP/PEAR web page that shows statistics of how many
people read a certain article during given period of time.

I have, say, two tables called 'books' and 'logs'. The 'books' table
has fields named 'bookNo', 'bookName', and so on, and 'logs' table has
fields named 'bookNo', and 'time'. Whenever somebody accesses a book,
I insert a row to the 'logs' table with 'INSERT INTO logs VALUES ($no,
NOW());'

Now, to create a statistic page I mentioned above, I first tried the
following query:

SELECT books.bookNo, books.bookName, COUNT(logs.time) FROM books LEFT
JOIN logs ON (logs.bookNo=books.bookNo) WHERE (logs.time between
'2004-06-01' and '2004-06-30') GROUP BY books.bookNo;

It works fine, but it wouldn't show the books that has been never
accessed, and I want to see all the books no matter how unpopular they
are. =) I found out that it was beause the joined table (before the
GROUP BY) would only have a (bookNo, NULL) row, and it doesn't fit the
WHERE clause. So what I did next is to add one more OR condition to
the WHERE clauses:

SELECT books.bookNo, books.bookName COUNT(logs.time) FROM books LEFT
JOIN logs ON (logs.bookNo=books.bookNo) WHERE ((logs.time between
'2004-06-01' and '2004-06-30') OR (logs.time IS NULL)) GROUP BY
books.bookNo;

First, it seemed to be working perfectly, but soon I realized that it
wouldn't show books that has never been acceesed during the given time
(between 2004-06-01 and 2004-06-30) but had some access other than
that time.

If I could use subquery, I could simply do some thing like: SELECT
books.bookNo, books.bookName, COUNT(logs.time) FROM books LEFT JOIN
(SELECT * FROM logs WHERE ((logs.time between '2004-06-01' and
'2004-06-30')) ON (logs.bookNo=books.bookNo) GROUP BY books.bookNo;
and it would do exactly what I need.

However, I need to do this in MySQL 4.0, which doesn't support
subqueries.

I had this idea, that I could achieve what I need if I can join the
tables in the specific way. Suppose the 'books' table has {(0,
'mysql') (1, 'mssql'), (2, 'oracle')} and the 'logs' table has {(0,
2004-05-30), (0, 2004-06-03), (1, 2004-06-10)}.

If I could join them so that I get {(0, 'mysql', NULL), (1, 'mysql',
'2004-05-30), (0, 'mysql', 2004-06-03), (1, 'mssql', NULL), (1,
'mssql', 2004-06-10), (2, 'oracle', NULL)}, then my problem would be
solved. I tried all kinds of JOIN to do this, but I couldn't.

Please could someone help me on this? Thanks in advance.
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Haisoo Shin wrote:
I found out that it was beause the joined table (before the
GROUP BY) would only have a (bookNo, NULL) row, and it doesn't fit the
WHERE clause. So what I did next is to add one more OR condition to
the WHERE clauses:
Why does the logs table have any records with NULL in the time field?
What is the significance of such rows in your recordkeeping?
SELECT books.bookNo, books.bookName COUNT(logs.time) FROM books LEFT
JOIN logs ON (logs.bookNo=books.bookNo) WHERE ((logs.time between
'2004-06-01' and '2004-06-30') OR (logs.time IS NULL)) GROUP BY
books.bookNo;

First, it seemed to be working perfectly, but soon I realized that it
wouldn't show books that has never been acceesed during the given time
(between 2004-06-01 and 2004-06-30) but had some access other than
that time.


This seems like it's causing confusion, because there's an ambiguity
between a NULL due to the data in that record, and a NULL due to the
outer join finding no matching records.

If you solve the issue of having records in the logs table with NULL for
time, and omit the term testing for logs.time IS NULL, then a NULL will
unambiguously indicate no matches in the left outer join.

Regards,
Bill K.
Jul 20 '05 #2

P: n/a
SELECT books.bookNo, books.bookName, COUNT(logs.time)
FROM books LEFT JOIN logs
ON (logs.bookNo=books.bookNo
AND logs.time between '2004-06-01' and '2004-06-30'
)
GROUP BY books.bookNo;
"Haisoo Shin" <da****@izdream.net> wrote in message
news:8d**************************@posting.google.c om...
Hello..

I am working on a PHP/PEAR web page that shows statistics of how many
people read a certain article during given period of time.

I have, say, two tables called 'books' and 'logs'. The 'books' table
has fields named 'bookNo', 'bookName', and so on, and 'logs' table has
fields named 'bookNo', and 'time'. Whenever somebody accesses a book,
I insert a row to the 'logs' table with 'INSERT INTO logs VALUES ($no,
NOW());'

Now, to create a statistic page I mentioned above, I first tried the
following query:

SELECT books.bookNo, books.bookName, COUNT(logs.time) FROM books LEFT
JOIN logs ON (logs.bookNo=books.bookNo) WHERE (logs.time between
'2004-06-01' and '2004-06-30') GROUP BY books.bookNo;

It works fine, but it wouldn't show the books that has been never
accessed, and I want to see all the books no matter how unpopular they
are. =) I found out that it was beause the joined table (before the
GROUP BY) would only have a (bookNo, NULL) row, and it doesn't fit the
WHERE clause. So what I did next is to add one more OR condition to
the WHERE clauses:

SELECT books.bookNo, books.bookName COUNT(logs.time) FROM books LEFT
JOIN logs ON (logs.bookNo=books.bookNo) WHERE ((logs.time between
'2004-06-01' and '2004-06-30') OR (logs.time IS NULL)) GROUP BY
books.bookNo;

First, it seemed to be working perfectly, but soon I realized that it
wouldn't show books that has never been acceesed during the given time
(between 2004-06-01 and 2004-06-30) but had some access other than
that time.

If I could use subquery, I could simply do some thing like: SELECT
books.bookNo, books.bookName, COUNT(logs.time) FROM books LEFT JOIN
(SELECT * FROM logs WHERE ((logs.time between '2004-06-01' and
'2004-06-30')) ON (logs.bookNo=books.bookNo) GROUP BY books.bookNo;
and it would do exactly what I need.

However, I need to do this in MySQL 4.0, which doesn't support
subqueries.

I had this idea, that I could achieve what I need if I can join the
tables in the specific way. Suppose the 'books' table has {(0,
'mysql') (1, 'mssql'), (2, 'oracle')} and the 'logs' table has {(0,
2004-05-30), (0, 2004-06-03), (1, 2004-06-10)}.

If I could join them so that I get {(0, 'mysql', NULL), (1, 'mysql',
'2004-05-30), (0, 'mysql', 2004-06-03), (1, 'mssql', NULL), (1,
'mssql', 2004-06-10), (2, 'oracle', NULL)}, then my problem would be
solved. I tried all kinds of JOIN to do this, but I couldn't.

Please could someone help me on this? Thanks in advance.


Jul 20 '05 #3

P: n/a
logs table does not have any records with NULL in the time field.

I added NULL condition because of a NULL due to the outer join finding
no matching records.

Thanks for the reply anyway. =)
Why does the logs table have any records with NULL in the time field?
What is the significance of such rows in your recordkeeping? This seems like it's causing confusion, because there's an ambiguity
between a NULL due to the data in that record, and a NULL due to the
outer join finding no matching records.

If you solve the issue of having records in the logs table with NULL for
time, and omit the term testing for logs.time IS NULL, then a NULL will
unambiguously indicate no matches in the left outer join.

Regards,
Bill K.

Jul 20 '05 #4

P: n/a
Thank you! It works perfectly.
I can't believe I didn't think of this. guess I need a lot more studying.

"Isaac Blank" <iz*****************@yahoo.com> wrote in message news:<1K******************@newssvr27.news.prodigy. com>...
SELECT books.bookNo, books.bookName, COUNT(logs.time)
FROM books LEFT JOIN logs
ON (logs.bookNo=books.bookNo
AND logs.time between '2004-06-01' and '2004-06-30'
)
GROUP BY books.bookNo;

Jul 20 '05 #5

P: n/a
How about UNOIN?

ccton
--
www.vicdir.com
"Haisoo Shin" <da****@izdream.net> ????
news:8d**************************@posting.google.c om...
Hello..

I am working on a PHP/PEAR web page that shows statistics of how many
people read a certain article during given period of time.

I have, say, two tables called 'books' and 'logs'. The 'books' table
has fields named 'bookNo', 'bookName', and so on, and 'logs' table has
fields named 'bookNo', and 'time'. Whenever somebody accesses a book,
I insert a row to the 'logs' table with 'INSERT INTO logs VALUES ($no,
NOW());'

Now, to create a statistic page I mentioned above, I first tried the
following query:

SELECT books.bookNo, books.bookName, COUNT(logs.time) FROM books LEFT
JOIN logs ON (logs.bookNo=books.bookNo) WHERE (logs.time between
'2004-06-01' and '2004-06-30') GROUP BY books.bookNo;

It works fine, but it wouldn't show the books that has been never
accessed, and I want to see all the books no matter how unpopular they
are. =) I found out that it was beause the joined table (before the
GROUP BY) would only have a (bookNo, NULL) row, and it doesn't fit the
WHERE clause. So what I did next is to add one more OR condition to
the WHERE clauses:

SELECT books.bookNo, books.bookName COUNT(logs.time) FROM books LEFT
JOIN logs ON (logs.bookNo=books.bookNo) WHERE ((logs.time between
'2004-06-01' and '2004-06-30') OR (logs.time IS NULL)) GROUP BY
books.bookNo;

First, it seemed to be working perfectly, but soon I realized that it
wouldn't show books that has never been acceesed during the given time
(between 2004-06-01 and 2004-06-30) but had some access other than
that time.

If I could use subquery, I could simply do some thing like: SELECT
books.bookNo, books.bookName, COUNT(logs.time) FROM books LEFT JOIN
(SELECT * FROM logs WHERE ((logs.time between '2004-06-01' and
'2004-06-30')) ON (logs.bookNo=books.bookNo) GROUP BY books.bookNo;
and it would do exactly what I need.

However, I need to do this in MySQL 4.0, which doesn't support
subqueries.

I had this idea, that I could achieve what I need if I can join the
tables in the specific way. Suppose the 'books' table has {(0,
'mysql') (1, 'mssql'), (2, 'oracle')} and the 'logs' table has {(0,
2004-05-30), (0, 2004-06-03), (1, 2004-06-10)}.

If I could join them so that I get {(0, 'mysql', NULL), (1, 'mysql',
'2004-05-30), (0, 'mysql', 2004-06-03), (1, 'mssql', NULL), (1,
'mssql', 2004-06-10), (2, 'oracle', NULL)}, then my problem would be
solved. I tried all kinds of JOIN to do this, but I couldn't.

Please could someone help me on this? Thanks in advance.

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.