how can I rewrite this join in Mysql 3.2?

[windandwaves]

Hi Folk

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
FROM `ACS`
LEFT JOIN `ACC` ON `ACS`.`ID` = `ACC`.`ACS_ID`
WHERE
`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
it.

Cheers

Nicolaas

[strawberry]

windandwaves wrote:

Hi Folk

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
FROM `ACS`
LEFT JOIN `ACC` ON `ACS`.`ID` = `ACC`.`ACS_ID`
WHERE
`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
it.

Cheers

Nicolaas

Can the pivot table's id (presumably a primary key) ever really by
NULL?
That sounds like a structural error.

[windandwaves]

strawberry wrote:

Can the pivot table's id (presumably a primary key) ever really by
NULL?
That sounds like a structural error.

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.

Nicolaas

[strawberry]

windandwaves wrote:

strawberry wrote:

Can the pivot table's id (presumably a primary key) ever really by
NULL?
That sounds like a structural error.

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.

Nicolaas

If you're allowed to use subqueries then I guess you're looking for
something like this:

SELECT *
FROM `ACS`
LEFT JOIN (

SELECT *
FROM ACC
WHERE ACC.ACT_ID = 480
)b ON b.ACS_ID = ACS.ID