58937

How to get number of specific rows from a different table in a subquery

I know it's possible, but I'm not experienced enough to know how to do subqueries. Here's the situation:

Table 1: +--------------------+--------------------+ | v_id | v_name | +--------------------+--------------------+ | 1 | v_name1 | +--------------------+--------------------+ | etc... Table 2: +--------------------+--------------------+ | a_id | a_name | +--------------------+--------------------+ | 1 | a_name1 | +--------------------+--------------------+ | etc... Table 3: +--------------------+--------------------+ | v_id | a_id | +--------------------+--------------------+ | 1 | 1 | +--------------------+--------------------+ | 1 | 2 | +--------------------+--------------------+ | 1 | 3 | +--------------------+--------------------+ | 2 | 3 | +--------------------+--------------------+ | 2 | 1 | +--------------------+--------------------+

I believe this is a quite common situation. So, I have unique entries in Table 1 and Table 2. I want to SELECT all rows from Table 1 and get (as the last cell in each row) the number of rows with the corresponding value in Table 3.

This doesn't work:

SELECT t1.* , COUNT(SELECT t3.* FROM `table_3` t3 WHERE t3.v_id = t1.v_id) as entries FROM `table 1` t1;

I'm sure I'm gonna be told off by experts here that it's all wrong, but frankly, that's what I'm looking for (and some helpful solution as well!). ;)

Answer1:

Use:

SELECT t1.*, COALESCE(x.num_rows, 0) AS entries FROM `table 1` t1 LEFT JOIN (SELECT t3.v_id, COUNT(*) 'num_rows' FROM `table_3` t3 GROUP BY t3.v_id) x ON x.v_id = t1.v_id

Answer2:

SELECT t1.* , (SELECT COUNT(*) FROM `table_3` t3 WHERE t3.v_id = t1.v_id) as t3Count as entries FROM `table 1` t1;

Answer3:

SELECT T1.v_id, COALESCE(COUNT(T3.v_id), 0) FROM Table1 AS T1 LEFT JOIN Table3 AS T3 ON T1.v_id = T3.v_id GROUP BY T1.v_id

Recommend

  • TypeScript Mapped Types: Get element type of array
  • I have a SQLite syntax error in DELETE statement
  • Unselect column after pasting data
  • Edge-case: When (only) reversing order of template parameters in specialization, can non-specialized
  • Aspect advising other aspects
  • Can't get LogCat (Alcatel OneTouch Evolve)
  • Defining variable by logical subseting on time interval in data.table
  • How to use ResourceDictionary in Windows Phone class library project
  • Partial specialization of a class template in derived class affects base class
  • SQL: Getting the physical size of a subset of a table
  • Julia: How to give multiple workers access to functions that are 'include(…)' into a modul
  • How can I get the choice “H2” back in the H2 consol?
  • How do I shift the decimal place in Python?
  • Add dynamic data to line chart from mysql database with highcharts
  • Center align outputs in ipython notebook
  • Using Sax parsing to edit and write XML in VB6
  • custom UITableViewCell with image for highlighting
  • Django: Count of Group Elements
  • Is there a javascript serializer for JSON.Net?
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • Where to put my custom functions in Wordpress?
  • vba code to select only visible cells in specific column except heading
  • ORA-29908: missing primary invocation for ancillary operator
  • Do I've to free mysql result after storing it?
  • How to get next/previous record number?
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Unanticipated behavior
  • Transpose CSV data with awk (pivot transformation)
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Buffer size for converting unsigned long to string
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • Can't mass-assign protected attributes when import data from csv file
  • Sorting a 2D array using the second column C++
  • Binding checkboxes to object values in AngularJs
  • Unable to use reactive element in my shiny app
  • How to load view controller without button in storyboard?