CODEWITHSUNDEEP
mysqlsqlsubquery
jb10210
jb10210

Reputation: 1176

Select in where clause, access to current parent select columns

I have a query which contains a select statement in it's where clause. My question is now, how can I access the parent's select's data.

Example:

select * from TABLE_1 as t1 INNER JOIN TABLE_2 as t2
where (... and ...) OR 
      (not exists(select * from TABLE_3 as t3 
                                inner join TABLE_1 ON t3.t1_id = t1.id

The last line is where the error occurs: t1.id is not a column.

How can I access the current value from the table t1? I'm using MySql 5.1

Upvotes: 2

Views: 2325

Answers (2)

Richard Nixon
Richard Nixon

Reputation: 169

First of all, you need to declare what you will JOIN TABLE_2 on TABLE_1. 

SELECT *
FROM TABLE_1 AS t1 
INNER JOIN TABLE_2 AS t2 ON t2.t1_id = t1.id

t1.id = t2.t1_id is just an example, you will need to decide which columns you wish you join on. Then in your WHERE clause, you do not need to INNER JOIN on TABLE_1 again as you are already selecting from it. 

SELECT *
FROM TABLE_1 AS t1 
INNER JOIN TABLE_2 AS t2 ON t2.t1_id = t1.id
WHERE (... AND ...) OR 
(
    NOT EXISTS
    (
        SELECT * 
        FROM TABLE_3 AS t3 
        WHERE t3.t1_id = t1.id
    )
)

Upvotes: 0

Pittsburgh DBA
Pittsburgh DBA

Reputation: 6783

SELECT
  *
FROM
  TABLE_1 as t1 
  INNER JOIN TABLE_2 as t2 ON
    t2.PK = t1.FK --Whatever your keys are
WHERE
  (... and ...)
  OR
  (
  NOT EXISTS (select * from TABLE_3 as t3 WHERE t3.t1_id = t1.id)
  )

Upvotes: 1

Related Questions