how can I rewrite this join in Mysql 3.2?

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

Re: how can I rewrite this join in Mysql 3.2?

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.

Re: how can I rewrite this join in Mysql 3.2?

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

Re: how can I rewrite this join in Mysql 3.2?

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