0

I'm using SQL in PDQ inventory to generate a report. I'm trying to provide a count of only one set of data, the Display Adapters (Should be between 0 and 4). So some systems have more than one display adapter and we want that information. Right now, count is giving us huge numbers, probably counting things I don't want it to.

select
    Computers.Name as "Computer Name",
    CPUs.Name,
    Computers.Memory as "RAM",
    HardwareDevices.Name as "Display Adapters",
    count(*)
from Computers, CPUs, HardwareDevices
where (<ComputerFilter> and HardwareDevices.Name like '%this%' and HardwareDevices.ComputerID == Computers.ComputerID)
or (<ComputerFilter> and HardwareDevices.Name like '%that%' and HardwareDevices.ComputerID == Computers.ComputerID)
GROUP BY "Computer Name"

These are actually only bring back 0-4 rows. We just want a count of the total rows of both of these and nothing else:

  where (<ComputerFilter> and HardwareDevices.Name like '%this%' and HardwareDevices.ComputerID == Computers.ComputerID)
    or (<ComputerFilter> and HardwareDevices.Name like '%that%' and HardwareDevices.ComputerID == Computers.ComputerID)
2
  • 1
    "counting things I don't want it to" -- you'll need to learn how joins work.
    – mustaccio
    Dec 10, 2021 at 17:31
  • Instead of old-style , cross-joins (which were deprecated 30 years ago), use proper JOIN...ON... syntax. At the moment you just have a huge cross-join Dec 12, 2021 at 4:03

1 Answer 1

2

It's because you join the table "CPUs" without a single condition in the where -clause. The result is the lines from computers x hardwaredevices (joined on computerid) x all possible CPUs.

The DB's I know would refuse to run your query, because it lacks "group by" columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.