CODEWITHSUNDEEP
mysqlsqljoindb2subquery
Richa Garg
Richa Garg

Reputation: 47

join nested queries in from clause

Can you tell me what I need to manipulate in this query to get it working?

select C.ID from 
(select A.ID from CUSTOMERS A inner join PROFILES B on A.ID=B.ID where CTR='67564' and CST_CD in 
('G','H')) as C 
inner join
(select ID from RELATION_CODES where R_CD='KC') as R
on C.ID=R.ID

The individual inner queries are working just fine and giving correct results, not sure what is the problem with inner join in from clause..

Upvotes: 1

Views: 199

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Not completely sure I'm understanding your question, but this should be able to be rewritten without the subqueries:

select c.id 
from customers c
    join profiles p on c.id = p.id 
    join relation_codes rc on rc.id = c.id
where ctr = '67564' 
    and cst_cd in ('G','H')
    and rc.r_cd = 'KC'

If this isn't working, please provide your table structure and sample data and expected results. This should get you pretty close though.

I have to ask, is the id field in the relation_codes table and the profiles table the same as the id in the customers table. Perhaps you need to identify how your tables are related.

Upvotes: 2

Related Questions