Join 3 tables with where and sorting clauses

Question

I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too.

Each camera has a reference of vendor_model as model_id.

10 cameras can have one same vendor_model.

I am doing this query

SELECT v0."id", v0."vendor_id", v0."exid", v0."name", v0."username",
   v0."password", v0."jpg_url", v0."h264_url",  v0."mjpg_url", v0."mpeg4_url",
   v0."mobile_url", v0."lowres_url", v0."shape", v0."resolution", 
   v0."official_url", v0."more_info", v0."audio_url", v0."poe",
   v0."wifi", v0."upnp", v0."ptz", v0."infrared", v0."varifocal",
   v0."sd_card", v0."audio_io", v0."discontinued", v0."onvif",
   v0."psia", v0."channel", v0."created_at", v0."updated_at"
FROM "vendor_models" AS v0
INNER JOIN "vendors" AS v1 ON v0."vendor_id" = v1."id"
WHERE ((lower(v0."name") LIKE $1))
  OR (lower(v1."name") LIKE $2)
ORDER BY v1."name" ["%%", "%%"]

after getting all those values. I am doing another query. such as

SELECT c0."id", c0."owner_id", c0."model_id", c0."exid",
   c0."name", c0."timezone", c0."thumbnail_url",
   c0."is_online", c0."offline_reason", c0."is_public",
   c0."is_online_email_owner_notification", c0."alert_emails",
   c0."discoverable", c0."config", c0."mac_address",
   c0."location", c0."last_polled_at", c0."last_online_at",
   c0."created_at", c0."updated_at"
FROM "cameras" AS c0
WHERE (c0."model_id" = $1) [4]

which then give me the total number of cameras which are using this model.

my problem is I want to merge this query to in single one.

where I can get all the values of VendorModel as well as vendor. and also the count of cameras which are using each vendor model. which I am unable to do right now any help would be appreciable.

Update:

I have got here

SELECT vendor_models.id, vendor_models.name, count(cameras.id) as count 
  FROM "vendor_models" 
  INNER JOIN "vendors" ON "vendor_models"."vendor_id" = "vendors"."id" 
  INNER JOIN "cameras" ON "vendor_models"."id" = "cameras"."model_id"
  GROUP BY vendor_models.id, vendor_models.name
  ORDER BY count desc

But I cannot get vendor.name.. can you help?

Update:

SELECT vendor_models.id, vendor_models.name, v.name, count(cameras.id) as count 
  FROM vendor_models
  INNER JOIN vendors as v ON vendor_models.vendor_id = v.id 
  INNER JOIN cameras ON vendor_models.id = cameras.model_id
  GROUP BY vendor_models.id, vendor_models.name, v.name
  ORDER BY v.name asc

I got the final one. can you help to make it better?

Erwin Brandstetter · Accepted Answer · 2019-02-06T10:48:27.767

Still room for improvement. This is your last version, untangled, formatted and shortened with table aliases:

SELECT vm.id, v.name, vm.name, count(c.id) AS count 
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id 
JOIN   cameras       c  ON c.model_id = vm.id
GROUP  BY vm.id, v.name, vm.name
ORDER  BY v.name;

Remaining problems:

You have the name name twice in the result, which is unhelpful to say the least.
(A shining example why non-descriptive names like "name" are bad names.)
Sort order not fully deterministic.
Not as fast as it could be.

I suggest:

SELECT vm.id
     , v.name AS vendor, vm.name AS model  -- column aliases to disambiguate
     , c.camera_count
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id 
JOIN  (
   SELECT model_id, count(*) AS camera_count  -- count(*) slightly faster
   FROM   cameras
   GROUP  BY 1           -- aggregate first, join later
   ) c ON c.model_id = vm.id
ORDER  BY v.name, vm.name, vm.id; -- add "id" if the names are not unique

If you count cameras per model first, you have far fewer join operations and don't have to aggregate outer columns at all. Should be faster.

count(*) is a bit faster than count(expression) (separate implementation) while doing the same in the absence of NULL values.

Since columns from the outer tables (vendors, vendor_models) are not aggregated at all now, you can put any of these columns in the SELECT list at will - including vm.*.

Join 3 tables with where and sorting clauses

1 Answers1