Hi,
spacemarc wrote:
2007/6/8, Baron Schwartz <[EMAIL PROTECTED]>:
Use a LEFT OUTER JOIN, but reverse it; join from tableA to tableB.
You may be looking for a FULL OUTER JOIN, I'm not sure. MySQL doesn't
support that,
but see:
http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
I've inverted, like you said, the tables and used LEFT OUTER JOIN:
SELECT tableA. * , COUNT( tableB.uid ) AS Tot
FROM tableA
LEFT OUTER JOIN tableB ON tableB.uid = tableA.uid
GROUP BY tableA.uid
and it works.
I also tried this other:
SELECT tableA.*, COUNT(tableB.uid) AS Tot
FROM tableA
LEFT JOIN tableB ON tableA.uid=tableB.uid
GROUP BY tableA.username
HAVING COUNT(tableB.uid)>=0
ORDER BY tableA.username ASC
and also it works well.
Now, which of these two are faster in execution from web page e for
database?
That is hard to say. It depends on your tables, data, indexes and so on. Generally
simpler queries will run faster; the ORDER BY in the second query might cause it to use
a temporary table and filesort because you are not ordering by the same thing you are
grouping by. The easiest thing to do is EXPLAIN the query, the next easiest is measure
it and see how much work it does. This can also be very good practice and education
for understanding how MySQL executes queries. I wrote a query profiler which may help:
http://sourceforge.net/projects/mysqltoolkit.
Cheers
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
