mySQL error: #1248 - Every derived table must have its own alias

Question

How would I correct this error by setting an alias? error: #1248 - Every derived table must have its own alias

SELECT 
    entry_id,
    author_id,
    title,
    status
FROM exp_channel_titles

LEFT JOIN
(SELECT
    entry_id, 
    field_id_14,
    field_id_15,
    field_id_25,
    field_id_27, 
    field_id_28, 
    field_id_29, 
    field_id_30,
    field_id_31,
    field_id_32,
    field_id_33,
    field_id_34,
    field_id_35
FROM exp_channel_data
WHERE entry_id = exp_channel_titles.entry_id)

LEFT JOIN
(SELECT   
    member_id,
    email
FROM exp_members
WHERE member_id = exp_channel_titles.author_id)

WHERE title LIKE %Member% 
AND status = 'complete'

Nanne · Answer 1 · 2012-05-08T09:13:03.183

20

Well, as the error says, you have to name every derived table. For instance

(SELECT   
    member_id,
    email
FROM exp_members
WHERE member_id = exp_channel_titles.author_id)

Is a derived table. Add a name like so:

(SELECT   
    member_id,
    email
FROM exp_members
WHERE member_id = exp_channel_titles.author_id) tempTableNameGoesHere

(I think I'm sure there is no need for an as between the bracket and the name, but I suppose you can try it, or look it up from here ;) )

Your follow-up question (how long are we going to do this? :) )

 WHERE title LIKE %Member%

should be

WHERE title LIKE '%Member%'

edited May 08 '12 at 09:13

answered May 08 '12 at 08:48

Nanne

64,065
16
119
163

(this goes for all derived tables ofcourse, not just for this one) – Nanne May 08 '12 at 08:48
thanks ... now i'm getting another error after using Imdad's example. any advice? syntax to use near 'WHERE ect.title LIKE %Member% AND ect.status = 'complete' LIMIT 0, 30' at line 33 – acctman May 08 '12 at 09:03
1

You should put `'` around the `like` – Nanne May 08 '12 at 09:12
Great , you just saved my day. – amilaishere Aug 06 '13 at 03:26

score 1 · Answer 2 · answered May 08 '12 at 08:59

1

SELECT 
    ect.entry_id,
    ect.author_id,
    ect.title,
    ect.status
FROM exp_channel_titles as ect

LEFT JOIN
(SELECT
    entry_id, 
    field_id_14,
    field_id_15,
    field_id_25,
    field_id_27, 
    field_id_28, 
    field_id_29, 
    field_id_30,
    field_id_31,
    field_id_32,
    field_id_33,
    field_id_34,
    field_id_35
FROM exp_channel_data) as ecd 
ON ecd.entry_id = ect.entry_id

LEFT JOIN
(SELECT   
    member_id,
    email
FROM exp_members) as exm
ON exm.member_id = ect.author_id

WHERE ect.title LIKE '%Member%' 
AND ect.status = 'complete'

answered May 08 '12 at 08:59

Imdad

5,942
4
33
53

thanks now i'm getting this error... #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ect.title LIKE %Member% AND ect.status = 'complete' LIMIT 0, 30' at line 33 – acctman May 08 '12 at 09:01
thanks Imadad that worked sort of. I think my queries are wrong. I'm getting all the results Entry_id Author_id Title Status ... but I want all the field_* and email to appear in the results – acctman May 08 '12 at 09:12

mySQL error: #1248 - Every derived table must have its own alias

2 Answers2

Linked

Related