Question 22216 in Mysql Question posted in
The official MySQL documentation can be found here.

every derived table must have it is own alias join mysql

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
Tags:

3

Answers


  1. 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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    • 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search