select the fields from the subquery selection

Question

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;

Which fields do you want to return from the subquery? NB: You'll likely want to perform an `inner join` against the subquery instead of using `in`. See https://stackoverflow.com/a/3005314/361842 for an example. — JohnLBevan, Apr 19 '18 at 09:34
@JohnLBevan the fields from the `ads` table `The second in`and the fields from `ad_params`table , the purpose of working with `in` is that i 'am handling with a huge data to return for just one day — Medone, Apr 19 '18 at 09:40
@JohnLBevan could you please see the Second answer, (Mine) Thanks indeed — Medone, Apr 19 '18 at 10:51
I'm not sure what you're asking/saying... If providing new information it's best to [edit your original question](https://stackoverflow.com/posts/49917368/edit) to include it. If you have a new question it's best to [post as a new question](https://stackoverflow.com/questions/ask). Answers should only be used for answers. See also https://stackoverflow.com/help/asking for tips on asking questions. — JohnLBevan, Apr 19 '18 at 11:31

score 1 · Answer 1 · answered Apr 19 '18 at 09:44

1

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';

answered Apr 19 '18 at 09:44

Pankaj Kumar

550
2
6
22

if i added `coalesce(max(CASE when ad_params.name='model' then ad_params.value end ),NULL) ` in the selection and i want to make a condtion where on this field as my `ad_paramter ` table is a `crosstab` – Medone Apr 19 '18 at 10:55
@Mendone: I'm not sure what you're `coalesce(` is doing. This statement is used to say "If the result of the first argument is null, replace it with the result of the second argument". In your case the second argument is `null`, so you're replacing `null` with `null`; i.e. you could remove the coalesce / just write `max(CASE when ad_params.name='model' then ad_params.value end )` for the same effect. – JohnLBevan Apr 19 '18 at 11:35
@Mendone: I didn't understand your question itself, so can't offer much more help; sorry. Please consider carefully how to present all information about what you're hoping to achieve and provide any relevant information to support that (see [MCVE](https://stackoverflow.com/help/mcve)) and ask as a [new question](https://stackoverflow.com/questions/ask). – JohnLBevan Apr 19 '18 at 11:38
Thanks for your collaboration mr @JohnLBevan i made it in another question could you please review it ? thanks very much indeed https://stackoverflow.com/questions/49920898/making-a-join-condition-on-a-case-when-value-as-field-name – Medone Apr 19 '18 at 13:12

score 0 · Answer 2 · answered Apr 19 '18 at 09:49

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.

select the fields from the subquery selection

2 Answers2

Linked