every derived table must have it is own alias join mysql

Question

on mysql I see this error but can fix it. Anyone can help please?

   select * from 
    (
    (select a.* from sessions as a)
    join
    (
     select b.customer_id, min(b.timestamp), 
     b.marketing_source as first_touch_source, 
     b.marketing_medium as first_touch_medium 
     from sessions as b
     group by b.customer_id
     ) on a.customer_id = b=customer_id
    ) as T

are you just trying to get the minimum date column in your main session table ? — trillion, Nov 18 '22 at 08:31

score 0 · Answer 1 · answered Nov 18 '22 at 07:58

0

I believe your query should read

select * 
from (
    select a.* 
    from sessions as a
    join
        (select b.customer_id, min(b.timestamp), b.marketing_source as first_touch_source, b.marketing_medium as first_touch_medium 
        from sessions as b
        group by b.customer_id
    ) c USING (customer_id) # or c.customer_id = a.customer_id
) as T

answered Nov 18 '22 at 07:58

Antony

3,875
30
32

I am not very used to mySQL, if possible can you please explain the part of query after USING ? Would the following yield similar results ? `SELECT a.* ,b.* FROM sessions a INNER JOIN ( SELECT customer_id ,min(TIMESTAMP) ,marketing_source AS first_touch_source ,marketing_medium AS first_touch_medium FROM sessions GROUP BY customer_id ) b ON a.customer_id = b.customer_id` Asking just for my learning....thank you... – rainingdistros Nov 18 '22 at 08:00
When a field name is the same on a `JOIN` in mysql you don't need to do `ON field = field` you can just do `USING(field)` – Antony Nov 18 '22 at 10:59
If this query works for you (equally the other answer), please do set an answer - always appreciated – Antony Nov 18 '22 at 11:00
Thank you very much for your comments - it is ok - just wanted to understand and learn..sorry to impose again - I also thought the same, but the # kind of confused me - thought maybe mySQL had a special meaning ? Can I assume that we can either use `USING (customer_id)` or `c.customer_id=a.customer_id` ? – rainingdistros Nov 18 '22 at 11:09
Indeed either is possible. – Antony Nov 18 '22 at 14:38

Lennart - Slava Ukraini · Answer 2 · 2022-11-18T08:45:46.057

Some DBMS require you to name all derived tables. Your query (I removed the unnessesary derived table T):

select * 
from (select a.* from sessions as a)
join (select b.customer_id, min(b.timestamp)
           , b.marketing_source as first_touch_source
           , b.marketing_medium as first_touch_medium 
      from sessions as b
      group by b.customer_id
     )
  on a.customer_id = b=customer_id

can be changed to:

select * 
from (select a.* from sessions as a) AS c
join (select b.customer_id, min(b.timestamp)
           , b.marketing_source as first_touch_source
           , b.marketing_medium as first_touch_medium 
      from sessions as b
      group by b.customer_id
     ) AS d
  on c.customer_id = d.customer_id

To avoid confusion, you should choose another alias at the outer level, despite that the inner alias is not visible there.

Side note: The derived table d may or may not be valid SQL. It is not allowed in SQL92, but it is allowed in SQL99 if marketing_* is functionally dependent of customer_id.

You can further simplify it as:

select * 
from sessions AS c
join (select b.customer_id, min(b.timestamp) as ts
           , b.marketing_source as first_touch_source
           , b.marketing_medium as first_touch_medium 
      from sessions as b
      group by b.customer_id
     ) AS d
  on c.customer_id = d.customer_id

I assume you meant to also join with c.timestamp = d.ts. If that is the case and you are on a recent version of MySQL (8+) you can use a window function instead of a self join

select customer_id, ...
from (
    select b.customer_id
         , b.marketing_source
         , b.marketing_medium
         , row_number() over (partition by customer_id
                              order by b.timestamp) as rn 
    from sessions as b
) as T
where rn = 1

score 0 · Answer 3 · answered Nov 18 '22 at 08:35

If you are just trying to bring in the minimum date column you could try two approaches, second one will work if your version supports windows function
subqueries are always a mess so I would suggest cte's if supported

with main as (
select 
b.customer_id, 
b.marketing_source as first_touch_source, 
b.marketing_medium as first_touch_medium,
min(b.timestamp) as min_time,
from sessions as b
group by b.customer_id
)
select 
 s.*,
 main.min_time
from sessions as s
inner join main
on s.customer_id = main.customer_id

select 
*, 
min(timestamp) 
over(partition by customer_id,marketing_medium, marketing_source) as min_date_per_cust_med_source
from sessions

every derived table must have it is own alias join mysql

3 Answers3