select all from A + row count from B - If NOT B, then count = 0 How? 1

[southbeach]

I need to come up with a query where I can select all records from TABLEa and the a row count from TABLEB when matching keys between the two tables.

Right now, I am looping through TABLEA and running an UDF (PHP) to return a count from TABLEB. I hope there is a more efficient way.

Thanks!

 

[r937]

do you know how to write a LEFT OUTER JOIN?

r937.com | rudy.ca

 

[southbeach]

Can't say I do. I searched for 'outer join' in dev.mysql.com and perusing through the results confused me quite a bit.

I am not sure how to apply those samples to my needs ...

Regards,

 

[r937]

SELECT a.foo
     , a.bar
     , COUNT(b.fkey) AS b_rows
  FROM TABLEA AS a
LEFT OUTER
  JOIN TABLEB AS b
    ON b.fkey = a.pkey
GROUP
    BY a.foo
     , a.bar

r937.com | rudy.ca

 

[southbeach]

Now your sample is something I can make perfect sense of of.

Thank you so very much!

 

[r937]

thank you you





_{sorry!! couldn't help it it!!}

r937.com | rudy.ca

 

[southbeach]

I am working on a PHP script to emulate a directory tree. In lieu of an actual directory content, I am using MySQL to hold list of files.

I need to recursively display the tree showing all folders. The list of folder names are held in tableA and the list of documents are held in tableB.

I am trying to use your suggested query as follows:

SELECT a.*, count(b.*) AS count FROM cats AS a LEFT OUTER JOIN cats AS b ON b.catmain = a.catid WHERE a.catmain = 0 GROUP BY a.catid ORDER BY a.catname ASC

I also tried:

SELECT a. * , count( b. * ) AS count
FROM cats AS a
WHERE a.catmain < 1
LEFT OUTER JOIN cats AS b ON b.catmain = a.catid
GROUP BY a.catid
ORDER BY a.catname ASC
LIMIT 0 , 30

The need for the cound is to know if I have sub-directories under a folder. If the count is greater than 0, I need to show an image [ + ] to allow user the ability to expand folder. This could happen as deep as the user wants it, just as if you were using your explorer in a windows PC.

I keep on getting syntax error but I cannot see why. I even added a WHERE clause thinking it needed one.

The table cats has following fields

id int 3
catname charvars 30
catmain int 3

If an entry has a value in catmain, this means that it is a sub-directory and it should not appear on the query results (but in the count).

What am I doing wrong?

 

[r937]

usually when you get a syntax error, it's a good idea to mention what it said




change this --

SELECT a.*
     , count([red]b.*[/red]) AS count 
  FROM cats AS a 
LEFT OUTER 
  JOIN cats AS b 
    ON b.catmain = a.catid 
 WHERE a.catmain = 0 
GROUP 
    BY a.catid 
ORDER 
    BY a.catname ASC

to this --

SELECT a.*
     , count([blue]b.catmain[/blue]) AS count 
  FROM cats AS a 
LEFT OUTER 
  JOIN cats AS b 
    ON b.catmain = a.catid 
 WHERE a.catmain = 0 
GROUP 
    BY a.catid 
ORDER 
    BY a.catname ASC

r937.com | rudy.ca

 

[southbeach]

r937,

Sorry for not including the syntax error - I normally use phpMyAdmin to build/test my queries and then plug them in my scripts. The syntax error one normally get is very vague and not specific.

Your suggested change works!

Thank you so very much !!!!!!