Select all fields from a table where a field in another table with an ID is equal to a string

debugcn Published at Dev

ᔕᖺᘎᕊ

I have 2 tables:

Ads: Fields ID, A, B and C:

+----+---+-------+------+
| ID | A | B     | C    |
+----+---+-------+------+
|  1 | x | y     | z    |
|  2 | c | v     | b    |
|  3 | n | n     | m    |
+----+---+-------+------+

Requests: Fields ID, AdID, and Status:

+----+------+----------+
| ID | AdID |  Status  |
+----+------+----------+
|  3 |    1 | pending  |
|  4 |    2 | approved |
|  5 |    3 | pending  |
+----+------+----------+

ID (from Ads) = AdID (from Requests).

Now, I want to get all records from Ads where AdID's (from Requests) Status equals pending. AdId here would be the value ID from Ads.

So, with the above tables, the result I'd get would be ID 1 and 3 from Ads:

+----+---+---+---+
| ID | A | B | C |
+----+---+---+---+
|  1 | x | y | z |
|  3 | n | n | m |
+----+---+---+---+

This is the closest I've got so far, but this obviously doesn't work because it can only select one row - whereas I need to select many:

SELECT * FROM Ads WHERE ID = (SELECT AdID FROM Requests WHERE Status = 'pending')

This might not make sense - please ask if I haven't explained it well - I'll help as much as possible :)

Giorgos Betsos

Use IN in place of =:

SELECT * 
FROM Ads 
WHERE ID IN (SELECT AdID FROM Requests WHERE Status = 'pending')

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-8

Comments

0 comments

From Dev

select fields from table where id not in another table in mysql [not working]

From Dev

MYSQL - Select all rows in a table where it's ids in another table as string field

From Dev

MySQL - SELECT all from TABLE_1 where id does not exist in two fields of TABLE_2

From Dev

Linq select Item where it is equal to ID in another table

From Dev

Select from one table where id (from another table) exists

From Dev

SQLite, select where field 'like' field from another table

From Dev

MySql: insert into table SELECT from another table where first_table ID =another_table.id

From Dev

Select all fields from table A but single field from B?

From Dev

Select all rows from a table except where row in another table with same id has a particular value in another column

From Dev

select multiple columns from another table where field contains array

From Dev

select from one table, count from another where id is not linked

From Dev

Select all columns from table where one field is duplicated

From Dev

PHP/MySQL - Select all the rows where column from table 1 is equal to column from table 2

From Dev

PHP/MySQL - Select all the rows where column from table 1 is equal to column from table 2

From Dev

Select all from table where another query retuns no results

From Dev

Select all from table where another query retuns no results

From Dev

select field from table in another field from another table

From Dev

Select rows in a table where id is equals to another id in another table

From Dev

Select rows in a table where id is equals to another id in another table

From Dev

MySQL - How to select rows in a table where id value is in a comma delimited field in another table?

From Dev

MySql select by excluding all id from another table

From Dev

Select fields from table with DISTINCT field

From Dev

Select from table where columns names equal data in a different table

From Dev

SAS: Select rows where the ID is in another table

From Dev

Select from table where column in select from another table in laravel

From Dev

Select from table where column in select from another table in laravel

From Dev

Select from Table Where field equals a variable

From Dev

Select from mysql table WHERE field in '$array'?

From Dev

Select Additional Field from another Table

Related Related

Article