ERROS sintax using Mysql

[rbrtpch]

Hello, I am in the proccess of migrating my DB from Access to Mysql, my queries need some refining but been new to mysql I am running into different issues:


********** FIRST ISSUE *********************
the following syntax fails when I try to use for mysql :

sqlstr= "SELECT *, (SELECT COUNT(*) FROM tbl_Block WHERE " & CONDICION & ") AS TotalItems FROM tbl_block WHERE " & CONDICION & " ORDER BY POS ASC"

In order to bypass the error I have found a workaround, that is independent queries

The first will only capture the totalItem
sqlstr= "SELECT COUNT(*) AS TotalItems FROM tbl_Block WHERE " & CONDICION & "


and later I use:
sqlstr = "SELECT * FROM tbl_Block WHERE " & CONDICION & " ORDER BY POS ASC"

I am sure ther must be a better way to do this:

I have another query a bit more complex, but I bet not for many of you:
**** SECOND ISSUE ******************************
SELECT * , (SELECT COUNT (*) FROM tbl_art WHERE APR <> 1 AND TYPE = 'R') AS countArt , (SELECT COUNT (*) FROM tbl_art WHERE APR <> 1 AND TYPE = 'N') AS CountArt2, (SELECT COUNT (*) FROM tbl_Dom WHERE DombadD = 1) AS CountDom,
FROM tbl_hits


I read somewhere that UNION would do the trick can anyone help? the version I am using is mysql 4.0, I think this has been resolved in 5.0 but in the meantime there must be a neat way to accomplish this in one query.

 

[guelphdad]

You can't do subselects at all in version 4.0 you can if you upgrade to 4.1