Count records in a join?

[lurtnowski]

I have the query,

SELECT total_power_requirement, number_of_slots, location, 
elevation, title, circuit_breaker, power_panel FROM racks 
LEFT JOIN (
SELECT rack_id, 
SUM(power_requirement) AS total_power_requirement 
FROM devices 
GROUP BY rack_id) AS tpr ON tpr.rack_id = racks.rack_id 
WHERE racks.rack_id = 2

which works

Is there a way to get the total number of records from the query on the LEFT JOIN?

[r937]

SELECT tpr.total_power_requirement
     , x.records
     , racks.number_of_slots
     , racks.location
     , racks.elevation
     , racks.title
     , racks.circuit_breaker
     , racks.power_panel 
  FROM racks 
CROSS
  JOIN ( SELECT COUNT(*) AS records
           FROM devices ) AS x     
LEFT 
  JOIN ( SELECT rack_id
              , SUM(power_requirement) AS total_power_requirement 
           FROM devices 
         GROUP 
             BY rack_id ) AS tpr 
    ON tpr.rack_id = racks.rack_id 
 WHERE racks.rack_id = 2

[lurtnowski]

when I ran the query in phpmyadmin, I get

the error: unrecognized keyword near cross

[Mittineague]

@lurtnowski

unrecognized keyword near cross

There are some differences between MySQL and standard SQL

search “mysql join syntax”:
https://dev.mysql.com/doc/refman/8.0/en/join.html

…
In MySQL, JOIN , CROSS JOIN , and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise
…

Try that.

* Note

...
joined_table:
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
...

i.e. looks to be a problem with phpmyadmin not mysql ?

[r937]

@Mittineague

There are some differences between MySQL and standard SQL

although CROSS JOIN is both

[lurtnowski]

It appears to work when I run the query not through phpmyadmin, but why is t he # so high

Thats the total number of devices, but I only count 10, and the total power requirement reflect that (its all to added up)

[r937]

@lurtnowski

but why is t he # so high

come on, man, you can do this

your WHERE condition on the racks table , and the joined tps.rack_id = racks.rack_id data in the tpr subquery, both of these will return results for only one rack

how many rows does the x subquery count?

[lurtnowski]

I didnt realize you can put a where clause in the cross join,

$sql = "SELECT tpr.total_power_requirement
     , x.records
     , racks.number_of_slots
     , racks.location
     , racks.elevation
     , racks.title
     , racks.circuit_breaker
     , racks.power_panel 
  FROM racks 
CROSS
  JOIN ( SELECT COUNT(*) AS records
           FROM devices WHERE rack_id = ".$rack_id.") AS x     
LEFT 
  JOIN ( SELECT rack_id
              , SUM(power_requirement) AS total_power_requirement 
           FROM devices 
         GROUP 
             BY rack_id ) AS tpr 
    ON tpr.rack_id = racks.rack_id 
WHERE racks.rack_id = ".$rack_id;

thanks.