Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- how can I rewrite this join in Mysql 3.2?
December 12, 2006, 5:22 pm
rate this thread
I am trying to select all the fields from ACS and at the same time
check if in ACC there is a certain link to another table. This should
be expressed in the counter field. However, the query below doe not
work as it only selects ACS rows without any link in ACC (is null) or
ones with a link through to ACT.
Here is the sql:
SELECT `ACS` . * , COUNT( `ACC`.`ID` ) counter
LEFT JOIN `ACC` ON `ACS`.`ID` = `ACC`.`ACS_ID`
`ACC`.`ID` IS NULL OR
`ACC`.`ACT_ID` = "480"
GROUP BY `ACS`.`ID`
ORDER BY counter DESC , `NAM`
ACC has the following fields:
ACS_ID = link to ACS
ACT_ID = link to ACT
Right now, it only give me 13 rows - while ACS has about fifty rows in
Re: how can I rewrite this join in Mysql 3.2?
Maybe I am using the wrong joint. Basically I want to list all the
rows from ACS and at the same time find out if there is a related row
in ACC (using ACC.ACS_ID = ACS.ID) that has the ACC.ACT_ID = "480" (or
whatever ID I will use).
Hope that makes sense. For now, I have resolved the problem (in PHP)
by loading all the entries for ACS and then doing an SQL call for each
row. Obviously, this is highly inefficient.
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum