SQL - merge two queries-CodePudding

I have the two below queries, the first query returns a list of externalId.

select e.externalId from data_entity db 
inner join assign_entity a on db.fk_assign = a.assignmentId 
inner join exercise_entity e on a.fk_exercice = e.externalId;

The second qurey returns an internalId of each externalId returned by the first query.

select me.internal_id from entity me 
inner join entity_roles r on me.id = r.entity_id
WHERE r.roles = 'c9237a21-f6da-4ebe-81b0-ddecd51da86b';

How can i merge the two queries in a single query to return for each externalId its internalId like this.

CodePudding user response：

You can use EXISTS:

select me.internal_id, me.externalId 
from entity me 
inner join entity_roles r on me.externalid = r.entity_externalid 
WHERE r.roles = 'c9237a21-f6da-4ebe-81b0-ddecd51da86b'
and exists (
select * from data_entity db 
inner join assign_entity a on db.fk_assign = a.assignmentId 
inner join exercise_entity e on a.fk_exercice = e.externalId
where e.externalId = me.externalId
);

Probably this query is simpler than what it is now, but based only on your pictures and queries this would do.

PS: Next time, please provide a good sample of your data tables. Pictures do not help much to anyone.

CodePudding user response：

This should do what is required I think. Since there is no fiddle/data present, I can not test it.

with external as
(select e.externalId as externalId  from data_entity db 
inner join assign_entity a on db.fk_assign = a.assignmentId 
inner join exercise_entity e on a.fk_exercice = e.externalId)
select distinct e.externalId, me.internal_id from entity me 
inner join entity_roles r on me.externalid = r.entity_externalid 
inner join external e on r.roles = e.externalId;