CODEWITHSUNDEEP
mysql
AnchovyLegend
AnchovyLegend

Reputation: 12538

Subquery as a condition in a WHERE statement

I haven't had much practice with subqueries and am having an extremely difficult time writing this query. What I basically need to do is select all records from the user_info table that match the following criteria.

user_info and otherTable contain user_id field that relates the two tables. I need to select all fields from user_info if the $state variable equals ot.state AND the ot.user_id = ui.user_id while still maintaining all the conditions of the user_info WHERE statement.

SELECT * FROM users_info ui
WHERE cond1=1 AND cond2=2 AND (ot.state = $state AND ui.user_id = ot.user_id)
(
   SELECT * FROM otherTable as ot
   WHERE ui.user_id = ot.user_id
)

Note I am confident I am wayyy off with my query, I appreciate any clarification on how to accomplish this.

Many thanks in advance!

Upvotes: 0

Views: 60

Answers (1)

Andrew
Andrew

Reputation: 8703

It sounds to me like you just need to join the 2 tables together and apply the rest of your constraints:

select
from
users_info ui
inner join otherTable  ot
on ui.user_id = ot.user_id
where
cond1=1 AND 
cond2=2 
AND ot.state = $state

If you want to use the sub-query:

select
from
users_info ui
inner join (select user_id from otherTable where state = $state) ot
on ui.user_id = ot.user_id
where
cond1=1 AND 
cond2=2

or you could just do use the where clause instead:

    select
    from
    users_info ui
    where
    cond1=1 AND 
    cond2=2 and
ui.user_id in (select user_id from otherTable where state = $state)

Upvotes: 2

Related Questions