Cody Raspien Cody Raspien - 7 months ago 36
SQL Question

Subquery - MySQL - PHP

I have 2 tables.

table2 structure:

name
os
count


table1 structure:

name


Examples of table2:
Fred Android 50
Tom iOS 3
Tom iOS 3
Fred Android 1
Fred Android 1
James iOS 20

Table1 has a list of names (unique).

My current query (stored in PHP variable $sqlx) is

$sqlx = "SELECT COUNT(*) AS numberOfRows FROM table2 where name = 'Fred' AND count < '6' AND os = 'iOS' GROUP BY name";


How do I make a subquery so that I don't have to enter 'Fred' so that the name is selected from table1?

Answer

Adding the IN means you will check for all items in this list. Then using the second select query you are just pulling all names from table1. Here you can impose more conditions if you need to.

$sqlx = "SELECT COUNT(*) AS numberOfRows FROM table2 WHERE name IN (SELECT name FROM table1) AND count < '6' AND os = 'iOS' GROUP BY name";