How can i avoid a subquery here..

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.

re: How can i avoid a subquery here..

Haisoo Shin wrote:[color=blue]
> 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:[/color]

Why does the logs table have any records with NULL in the time field?
What is the significance of such rows in your recordkeeping?
[color=blue]
> 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.[/color]

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.

re: How can i avoid a subquery here..

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" <dasony@izdream.net> wrote in message
news:8de7d805.0407051723.501cdf5d@posting.google.c om...[color=blue]
> 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.[/color]

re: How can i avoid a subquery here..

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. =)
[color=blue]
> Why does the logs table have any records with NULL in the time field?
> What is the significance of such rows in your recordkeeping?[/color]
[color=blue]
> 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.[/color]

re: How can i avoid a subquery here..

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

"Isaac Blank" <izblank_removethis_@yahoo.com> wrote in message news:<1KzGc.12058$rS2.8741@newssvr27.news.prodigy. com>...[color=blue]
> 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;
>[/color]

re: How can i avoid a subquery here..

How about UNOIN?

ccton
--
www.vicdir.com
"Haisoo Shin" <dasony@izdream.net> ????
news:8de7d805.0407051723.501cdf5d@posting.google.c om...[color=blue]
> 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.[/color]