CODEWITHSUNDEEP
mysql
Kamlesh Gadekar
Kamlesh Gadekar

Reputation: 63

Mysql every derived table must have its own aliases error

I want to select distinct records from table sabha_cluster. I have used following query but "Mysql every derived table must have its own aliases error" occured.

mysql> select mps.scode,mps.tlit,mps.amnt,mps.pr from milk_payment_summary mps
join society_master s on mps.scode=s.scode inner join (select distinct c.scode
rom sabha_cluster c) where mps.date1 between '2014-01-16' and '2014-02-15' && c.scode=44;

Upvotes: 0

Views: 48

Answers (4)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Add an alias to the derived table, like this:

select mps.scode,mps.tlit, mps.amnt,mps.pr 
from milk_payment_summary mps
join society_master s 
on mps.scode=s.scode 
join (
  select distinct c.scode from sabha_cluster c
) AS t
on t.scode = s.scode
where mps.date1 between '2014-01-16' and '2014-02-15' and t.scode=44

Here t is the alias of derived table.

Upvotes: 0

Sandeep Vishwakarma
Sandeep Vishwakarma

Reputation: 576

You can go through this code ..........

 select mps.scode,mps.tlit,mps.amnt,mps.pr from milk_payment_summary mps
    join society_master s on mps.scode=s.scode inner join (select distinct scode
    from sabha_cluster) as c where mps.scode=c.scode and ( mps.date1 between '2014-01-16' and '2014-02-15') and c.scode=44;

Upvotes: 0

elixenide
elixenide

Reputation: 44823

You need an alias for your subquery: 

mysql> select mps.scode,mps.tlit,mps.amnt,mps.pr from milk_payment_summary mps
join society_master s on mps.scode=s.scode inner join (select distinct c.scode
from sabha_cluster c) x where mps.date1 between '2014-01-16' and '2014-02-15' && x.scode=44;

Note that you have to use that same alias, not c, in the WHERE clause.

Upvotes: 2

Abdul Manaf
Abdul Manaf

Reputation: 4888

Try this

select 
    mps.scode, mps.tlit, mps.amnt, mps.pr
from
    milk_payment_summary mps
        join
    society_master s ON mps.scode = s.scode
        inner join
    (select distinct
        c.scode
    from
        sabha_cluster c) d
where
    mps.date1 between '2014-01-16' and '2014-02-15'
        AND d.scode = 44;

Upvotes: 0

Related Questions