SQL query, where = value of another table

Question

I want to make a query that simply makes this, this may sound really dumb but i made a lot of research and couldn't understand nothing.

Imagine that i have two tables (table1 and table2) and two columns (table1.column1 and table2.column2).

What i want to make is basically this:

SELECT column1 FROM table1 where table2.column2 = '0'

I don't know if this is possible.

Thanks in advance,

Is there a relation between these tables? A foreign key? – Adam May 31 '12 at 08:21 — Adam, May 31 '12 at 08:21
@Adam yes, there is a foreign key :) – Rodolfo Matos May 31 '12 at 08:30 — Rodolfo Matos, May 31 '12 at 08:30

score 42 · Accepted Answer · edited May 23 '17 at 11:46

42

You need to apply join between two talbes and than you can apply your where clause will do work for you

select column1 from table1 
   inner join table2 on table1.column = table2.column
   where table2.columne=0

for join info you can see this

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

alt text

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

edited May 23 '17 at 11:46

Community

1
1

answered May 31 '12 at 08:21

Pranay Rana

175,020
35
237
263

@Sajmon, thank you for the tutorial, so basically this is what i want right? `Select Id from CheckList INNER JOIN CheckRow on CheckList.Id = CheckRow.ListId where CheckRow.Custom='0'` – Rodolfo Matos May 31 '12 at 08:47
Can you do this with 3+ tables? – ryandawkins May 22 '13 at 14:21
This is a very useful diagram. Thanks. – Derek 朕會功夫 Nov 06 '16 at 01:10

t-clausen.dk · Answer 2 · 2022-02-17T09:09:10.230

7

SELECT column1 FROM table1 t1
where exists (select 1 from table2 t2 
    where t1.id = t2.table1_id and t2.column2 = '0')

assuming table1_id in table2 is a foreign key refering to id of table1 which is the primary key

edited Feb 17 '22 at 09:09

answered May 31 '12 at 08:29

t-clausen.dk

43,517
12
59
92

score 2 · Answer 3 · edited Jun 01 '12 at 11:40

2

You don't have any kind of natural join between two tables.

You're asking for

Select Houses.DoorColour from Houses, Cars where Cars.AreFourWheelDrive = '1'

You'd need to think about why you're selecting anything from the first table, there must be a shared piece of information between tables 1 and 2 otherwise a join is pointless and probably dangerous.

edited Jun 01 '12 at 11:40

Pranay Rana

175,020
35
237
263

answered May 31 '12 at 08:24

Jeff Watkins

6,343
16
19

SQL query, where = value of another table

3 Answers3