CODEWITHSUNDEEP
sqlpostgresql
Medone
Medone

Reputation: 127

select the fields from the subquery selection

I have this query that getting the fields just from ad_media , but i can't figure out how to select also the fields that are in the subquery selection as well as the fields from the left join with ad_params any help thanks indeed 

select ad_media.ad_media_id
, ad_media.ad_id 
from ad_media 
where ad_media.ad_id in
(
    select action_states.ad_id 
    from action_states 
    where action_states.state = 'reg'   
    and action_states.action_id = '1' 
    and action_states.timestamp::date between '2018-04-17' and '2018-04-17' 
    and action_states.ad_id in
    (
        select ads.ad_id 
        from ads  
        where ads.category = '2010' 
        and ads.name = 'joe'
    ) 
)  
left join ad_params 
on ad_media.ad_id = ad_params.ad_id;

Upvotes: 0

Views: 95

Answers (2)

JohnLBevan
JohnLBevan

Reputation: 24470

Without seeing your data it's hard to say how to optimally write your query, but from information gleaned from your comments this may work well:

select ad_media.ad_media_id
, ad_media.ad_id 
, ads.* --or pick whatever individual fields from ads that you want
from ad_media 
inner join ads
    on ads.ad_id = ad_media.ad_id
    and ads.category = '2010' 
    and ads.name = 'joe'
left join ad_params 
    on ad_media.ad_id = ad_params.ad_id
where exists
(
    select 1
    from action_states 
    where action_states.state = 'reg'   
    and action_states.action_id = '1' 
    and action_states.timestamp::date between '2018-04-17' and '2018-04-17'  
    and action_states.ad_id = ad_media.ad_id
);

NB: There are many ways this query could be written to produce the results you're after; but what performs best depends on how much data's in each table, what that data looks like (i.e. how many records we filter out with each condition), and what indexes are present on the tables.

Upvotes: 0

Pankaj Kumar
Pankaj Kumar

Reputation: 570

You may try inner join instead of subquery like this:

SELECT ad_media.ad_media_id, ad_media.ad_id 
FROM ad_media 
INNER JOIN action_states ON action_states.ad_id = ad_media.ad_id
INNER JOIN ads ON ads.ad_id = action_states.ad_id
LEFT JOIN ad_params ON ad_media.ad_id = ad_params.ad_id
WHERE action_states.state = 'reg'   
AND action_states.action_id = '1' 
AND action_states.timestamp::DATE BETWEEN '2018-04-17' AND '2018-04-17'
AND ads.category = '2010' AND ads.name = 'joe';

Upvotes: 1

Related Questions