I cant for the life of me figure out why this is erroring
SELECT * FROM
SELECT
c.city_id,
p.prop_ynow_id,
p.propertyid,
p.prop_add_value,
p.name,
picture,
ifnull(p.address,'') as`location`,
ifnull(city,'')as`city`,
ifnull(ShortCut,'') as `state`,
ifnull(p.zip,'') as `zip`,
min(if(pr.minrent = 0,99999999,pr.minrent)) as minrent,
max(pr.maxrent) as maxrent,
'' as service,
hood_id,
ifnull(p.phone,'') as `phone`,
latitude,
longitude,
min(CAST(pu.fullBath AS UNSIGNED)) as`minbath`,
max(CAST(pu.fullBath AS UNSIGNED)) as`maxbath`,
min(CAST(pu.Bed AS UNSIGNED)) as`minbed` ,
max(CAST(pu.Bed AS UNSIGNED)) as`maxbed`,
'' as url,
'' as source_id,
'' as source_name,
'' as addresscode,
'' as citycode,
'' as ctime,
'' as paid,
'' as similar_url,
'' as created_at,
'' as updated_at,
'' as city_name,
'' as service_listing_id
FROM
wiki_city_list c join propertyinfo p on c.city_id=p.city
join ynow_rentwiki.Property_Unitlayout pu on p.prop_ynow_id=pu.P_Ident
join (SELECT CAST(substring_index(if(Rent >0 ,Rent,RentLow),'.',1) AS UNSIGNED) as minrent, CAST(substring_index(if(Rent >0,Rent,Renthigh),'.',1) AS UNSIGNED) as maxrent,PRE_Ident,P_Ident,UNL_Ident,RTY_Ident from ynow_rentwiki.Property_rents where P_Ident in (3958, 4576, 4577) and (Rent!='' or (Rentlow!='' and Renthigh!='')) ) as pr on pu.UNL_Ident=pr.UNL_Ident
join state s on (p.state = s.stateid OR p.state = s.ShortCut )
WHERE
pu.Status='Active'
and p.delete_date='0000-00-00'
GROUP BY
c.city_id, p.prop_ynow_id
UNION
SELECT
c.city_id,
p.prop_ynow_id,
p.propertyid,
p.prop_add_value,
p.name,
picture,
ifnull(p.address,'') as`location`,
ifnull(city,'')as`city`,
ifnull(ShortCut,'') as `state`,
ifnull(p.zip,'') as `zip`,
min(if(pr.minrent = 0,99999999,pr.minrent)) as minrent,
max(pr.maxrent) as maxrent,
'' as service,
hood_id,
ifnull(p.phone,'') as `phone`,
latitude,
longitude,
min(CAST(pu.fullBath AS UNSIGNED)) as`minbath`,
max(CAST(pu.fullBath AS UNSIGNED)) as`maxbath`,
min(CAST(pu.Bed AS UNSIGNED)) as`minbed` ,
max(CAST(pu.Bed AS UNSIGNED)) as`maxbed`,
'' as url,
'' as source_id,
'' as source_name,
'' as addresscode,
'' as citycode,
'' as ctime,
'' as paid,
'' as similar_url,
'' as created_at,
'' as updated_at,
'' as city_name,
'' as service_listing_id
FROM
wiki_city_list c join propertyinfo p on c.city_id=p.city
join ynow_rentwiki.Property_Unitlayout pu on p.prop_ynow_id=pu.P_Ident
join (SELECT CAST(substring_index(if(Rent >0 ,Rent,RentLow),'.',1) AS UNSIGNED) as minrent, CAST(substring_index(if(Rent >0,Rent,Renthigh),'.',1) AS UNSIGNED) as maxrent,PRE_Ident,P_Ident,UNL_Ident,RTY_Ident from ynow_rentwiki.Property_rents where P_Ident in (9744) and (Rent!='' or (Rentlow!='' and Renthigh!='')) ) as pr on pu.UNL_Ident=pr.UNL_Ident
join state s on (p.state = s.stateid OR p.state = s.ShortCut )
WHERE
pu.Status='Active'
and p.delete_date='0000-00-00'
GROUP BY
c.city_id, p.prop_ynow_id
UNION
SELECT
'' as prop_ynow_id,
id as propertyid,
0 as prop_add_value,
t.name as name,
'' as picture,
t.address as location,
t.city as city,
s.ShortCut as state,
t.zip as zip,
CAST(REPLACE(REPLACE(t.price,'$',''),',','') as UNSIGNED) as minrent,
'' as maxrent,
t.service as service,
'' as hood_id,
'' as phone,
t.latitude as latitude,
t.longitude as longitude,
t.bathrooms as minbath,
'' as maxbath,
t.bedrooms as minbed,
'' as maxbed,
t.url as url,
t.source_id as source_id,
t.source_name as source_name,
t.addresscode as addresscode,
t.citycode as citycode,
t.ctime as ctime,
t.paid as paid,
t.similar_url as similar_url,
t.created_at as created_at,
t.updated_at as updated_at,
SUBSTRING_INDEX(c.city_name,'_',1) as city_name,
t.service_listing_id as service_listing_id
FROM LBCPrimary.third_party_properties as t, LBCPrimary.wiki_city_list as c, LBCPrimary.state as s
WHERE
t.city in ( '230' )
and
address <> '' and
t.city = c.city_id and
c.city_state = s.stateid
order by t.ctime
desc
limit 46 as a limit 0,50
Are you writing a query in MySQL and getting an error of “1248: Every derived table must have its own alias”? If so, it’s a simple error to fix.
So you’ve got this error in MySQL. How do you fix it?
The short answer is you need to give your subqueries an alias in your SELECT statement. Add an alias after the closing bracket of the FROM clause subquery.
In other SQL vendors, this is not required, but MySQL requires you to alias your subqueries.
What does this mean?
Let’s take a look at an example.
Example of a Derived Table
A derived table is a SELECT subquery within the FROM clause of your main query. It’s treated like a table because it has columns and returns rows. It can be used in place of a table in the FROM clause, for example. It’s often called an inline view or just a subquery.
Here’s an example of a derived table:
SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
SELECT o.order_id,
o.customer_city,
o.order_amount
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id
)
GROUP BY customer_city;This query finds the city, number of orders, and the sum of the order amounts from the orders and customer tables.
Let’s break this query down.
The query has an outer query and an inner query. The inner query is used to get data from orders and customers:
SELECT o.order_id, o.customer_city, o.order_amount
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_idThis gets data from two tables, joining on a common field. This is the “derived table”.
The outer query selects a few columns from this subquery. Rather than using a table, the data comes from the results of this subquery.
SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
...
)
GROUP BY customer_city;It shows the customer_city, two aggregate functions, and groups by the city.
This is the entire query again:
SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
SELECT o.order_id,
o.customer_city,
o.order_amount
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id
)
GROUP BY customer_city;What happens if you run this query in MySQL?
You’ll get this error:
Error 1248: Every derived table must have its own alias
How do you resolve this?
Solution to “Every derived table must have its own alias”
The reason you get this error is that in MySQL, every derived table (subquery that is a SELECT query) needs to have an alias after it.
The query example here did not have an alias for the subquery. The alias for a subquery is the same as a column alias. It goes after the closing brackets for the FROM clause for the subquery.
So, in this example, all we need to do to resolve this error is to add an alias after the closing bracket.
The alias can be almost anything you like: a single letter, a word, a few letters. I often use the word “sub” if it’s a small query (for subquery), or something more descriptive if it’s a longer query.
SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
SELECT o.order_id,
o.customer_city,
o.order_amount
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id
) sub
GROUP BY customer_city;Notice how the word “sub” is added after the closing bracket on the second last line? That’s the alias for the subquery or derived table. This alias is required in MySQL but not other vendors.
Running this query should work and you should not get the “Every derived table must have its own alias” error anymore.
Optional: Add the AS Keyword
You could add the AS keyword, as this is an optional part of adding an alias, but it’s up to you. This will also work:
SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
SELECT o.order_id,
o.customer_city,
o.order_amount
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id
) AS sub
GROUP BY customer_city;So, that’s how you can resolve this derived table alias error. Add an alias to your subquery.
What do I do wrong with the update in mysql?
I tried many different ways and can not make it work.
Update is made on the same table. Corrected the sql as shown but I still get an described in later comment
update auctions A
SET A.active = -1
WHERE A.auction_id IN
(
SELECT auction_id
FROM
(
SELECT B.auction_id FROM
table auctions
WHERE B.auction_id = A.auction_id AND B.active = 0 AND B.ended_on < "2019-04-18" AND B.ended_on > "2018-01-06" AND B.item_id
not IN
(
SELECT item_id
FROM
(
SELECT C.item_id from auctions C
WHERE C.active = 1
AND C.item_id = B.item_id
) AS temp_c
)
) AS temp_b
);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209574, 20354, 1, 2, '2019-08-23 16:12:51', NULL, 'a:23', NULL, 0);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209575, 20354, 0, 2, '2018-03-13 16:12:51', NULL, 'a:23', '2018-03-23 16:30:31', 0);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209576, 20752, 0, 2, '2018-02-13 16:12:51', NULL, 'a:23', '2018-02-23 16:30:31', 0);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209577, 20752, 0, 2, '2018-02-13 16:12:51', NULL, 'a:23', '2018-02-23 16:30:31', 0);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209577, 20752, 0, 2, '2018-06-13 16:12:51', NULL, 'a:23', '2018-06-23 16:30:31', 0);
CREATE TABLE `auctions` (
`auction_id` BIGINT(20) NOT NULL,
`item_id` INT(11) NOT NULL,
`active` TINYINT(4) NULL DEFAULT '1',
`created_by` INT(11) NULL DEFAULT NULL,
`started_on` DATETIME NULL DEFAULT NULL,
`buy_price` DOUBLE NULL DEFAULT NULL,
`prefs` TEXT NULL COLLATE 'utf8_unicode_ci',
`ended_on` DATETIME NULL DEFAULT NULL,
`bids` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`auction_id`),
INDEX `item_id` (`item_id`),
INDEX `created_by` (`created_by`),
CONSTRAINT `auctions_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `data_1` (`id`),
CONSTRAINT `auctions_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `login` (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
Here is the select statement that contains the correct output for an update statement. Your answer contains auction_id that item_id has active = 1 but it shouldn’t.
SELECT * from auctions WHERE active = 0 AND ended_on < "2019-04-18" AND ended_on > "2018-01-06" AND item_id NOT IN (SELECT item_id FROM auctions WHERE active = 1);
Here is the output of EXPLAIN in the form of an INSERT
INSERT INTO `NieznanaTabela` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES (1, 'SIMPLE', 'A', 'ALL', 'item_id', NULL, NULL, NULL, 20554, 'Using where');
INSERT INTO `NieznanaTabela` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES (1, 'SIMPLE', 'B', 'ref', 'item_id', 'item_id', '4', 'dbauction.A.item_id', 10, 'Using where');
mysql сообщает об ошибке при выполнении многотабличного запроса:
Код коллекции sql code
[SQL] SELECT * from
(
select e.account from employee e
UNION
SELECT u.account from `user` u
UNION
SELECT a.account from agent a
)
[Err] 1248 - Every derived table must have its own alias Это предложение означает, что каждая производная таблица должна иметь собственный псевдоним
Эта ошибка обычно возникает в многотабличных запросах или подзапросах, поскольку во вложенных запросах результат подзапроса используется в качестве производной таблицы для запроса верхнего уровня, поэтому результат подзапроса должен иметь псевдоним.
В приведенном выше примере измените оператор запроса:
SELECT * from
(
select e.account from employee e
UNION
SELECT u.account from `user` u
UNION
SELECT a.account from agent a
)as total Как показано выше, добавление предложения в качестве итога после подзапроса эквивалентно присвоению псевдонима производной таблицы набора результатов подзапроса как итога, и проблема решена.
i have problem here,
when im trying to select with query below, i got error message
Error Code : 1248 Every derived table must have its own alias
SELECT B.BRANCH_CODE, B.BRANCH_NAME, C.COMPANY_CODE, C.NAME, TSK.DATE_CREATE,
CASE TB.BULK_TYPE
WHEN 1 THEN 'Bulk Transfer'
WHEN 2 THEN 'Bulk Payment'
ELSE 'Payroll'
END AS TRX_METHOD_E,
CASE TB.BULK_TYPE
WHEN 1 THEN 'Bulk Transfer'
WHEN 2 THEN 'Bulk Pembayaran Tagihan'
ELSE 'Pembayaran Gaji'
END AS TRX_METHOD_I,
TB.TOTAL_RECORD,
TB.ACCOUNT_NO,
TSK.TRX_TOTAL,
TC.TOTAL_CHARGE,
DATE(TSK.DATE_TRX) AS DATE_TRX,
TB.REF_ID,
CASE
WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'All Success'
WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'All Failed'
WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Partial Success (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
ELSE 'Pending Execution'
END AS TRX_STATUS_E,
CASE
WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'Berhasil Semua'
WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'Gagal Semua'
WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Berhasil Sebagian (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
ELSE 'Tunggu Eksekusi'
END AS TRX_STATUS_I
FROM CB_TASK_BULKS TB
JOIN CB_TASKS TSK ON TSK.REF_ID = TB.REF_ID
JOIN CB_COMPANIES C ON C.COMPANY_ID = TSK.COMPANY_ID
JOIN CB_BRANCHES B ON B.BRANCH_CODE = C.BRANCH_CODE,
(
SELECT REF_ID, SUM(CHARGE) AS TOTAL_CHARGE
FROM
(
SELECT XTB.REF_ID, SUM(CHARGE) AS CHARGE
FROM CB_TRANSFERS XT
JOIN CB_TASK_BULK_DETAILS XTBD ON XTBD.BULK_DETAIL_ID = XT.BULK_DETAIL_ID
JOIN CB_TASK_BULKS XTB ON XTB.REF_ID = XTBD.REF_ID
GROUP BY XTB.REF_ID
UNION
SELECT XTB2.REF_ID, SUM(CHARGE) AS CHARGE
FROM CB_TRANSFERS_DOM XTD
JOIN CB_TASK_BULK_DETAILS XTBD2 ON XTBD2.BULK_DETAIL_ID = XTD.BULK_DETAIL_ID
JOIN CB_TASK_BULKS XTB2 ON XTB2.REF_ID = XTBD.REF_ID
GROUP BY XTB.REF_ID
UNION
SELECT XTB3.REF_ID, SUM(CHARGE) AS CHARGE
FROM CB_PAYMENTS XP
JOIN CB_TASK_BULK_DETAILS XTBD3 ON XTBD3.BULK_DETAIL_ID = XP.BULK_DETAIL_ID
JOIN CB_TASK_BULKS XTB3 ON XTB3.REF_ID = XTBD.REF_ID
GROUP BY XTB.REF_ID
)
GROUP BY REF_ID
) TC
WHERE TC.REF_ID = TSK.REF_ID
AND (TSK.TRX_COUNT_SUCCESS > 0 OR TSK.TRX_COUNT_FAIL > 0);
can somebody help me to find my problem here,
i think i’ve given all tables with his own «name» like TB for CB_BULKS_DATA (example)
If you see MySQL SQL error 1248, this tutorial will help you.
Updated
Speed up your computer today with this simple download.
You must provide an alias for all your inline mood / view tables in MySQL, even if they are nested. In your code, you are using a view in the following view, but you seem to have forgotten to mention the alias for your company’s internal view.
MYSQL ERROR 1248 (42000): Each view must have its own nickname.
I could not understand why this was a mistake
SELECT * FROMCHOOSEc.city_id,p.prop_ynow_id,Object id p.,p.prop_add_value,name,Drawing,ifnull (p.address, '') as 'place',ifnull (city, '') as 'ville',ifnull (shortcut, '') as "State",ifnull (p.zip, '') as `zip`,min (if (pr.minrent implies 0.9999999, pr.minrent)) as minrent,max (pr.maxrent) as maxrent,'' during the service,Hood_id,ifnull (p.phone, '') as 'phone',Huge,Longitude,min (CAST (pu.fullBath AS UNSIGNED)) as `minbath`,max (CAST (pu.fullBath AS UNSIGNED)) as `maxbath`,min (CAST (pu.Bed AS UNSIGNED)) as `minbed`,max (CAST (pu.Bed AS UNSIGNED)) as `maxbed`,'' as a URL,'as source_id,'Source name,'' as well as the address code,'' as area code,almost like ctime,'' as paid,like Similar_url,like created_at,mainly because update_at,as the name of the city,'' as service_listing_idFROMwiki_city_list they will join Propertyinfo p at c.city_id = p.cityjoin ynow_rentwiki.Property_Unitlayout Please take away p.prop_ynow_id = pu.P_Identjoin (SELECT CAST (substring_index (if (Rent> 0, Rent, RentLow), '.', 1) AS UNSIGNED) as minrent, CAST (substring_index (if (Loyer> 0, Rent, Renthigh), '.', 1 )) AS UNSIGNED) as maxrent, PRE_Ident, P_Ident, UNL_Ident, RTY_Ident from ynow_rentwiki.Property_rents, where P_Ident in 4576, (3958, 4577) and (Rent! = '' And also (low! = '' And Renthigh! = ' ')))) as page rank on pu.UNL_Ident = pr.UNL_IdentJoin state s on (p.state matches s.stateid OR p.state = s.ShortCut)ORpu.Status = 'Active'and p.delete_date = '0000-00-00'BY GROUPc.city_id,p.prop_ynow_idUNIONCHOOSEc.city_id,p.prop_ynow_id,Object id p.,p.prop_add_value,name,Drawing,ifnull (p.address, '') as 'place',ifnull (city, '') as 'ville',ifnull (shortcut, '') as "State",ifnull (p.zip, '') as `zip`,min (if (pr.minrent matches 0.99999999, pr.minrent)) as minrent,max (pr.maxrent) as maxrent,'for the reason that the serviceHood_id,ifnull (p.phone, '') as 'phone',Huge,Longitude,min (CAST (pu.fullBath AS UNSIGNED)) as `minbath`,max (CAST (pu.fullBath AS UNSIGNED)) as `maxbath`,min (CAST (pu.Bed AS UNSIGNED)) as `minbed`,max (CAST (pu.Bed AS UNSIGNED)) as `maxbed`,'' as a URL,'as source_id,'Source name,'' although as an address code'' as area code,like ctime,'' paid,like Similar_url,'like created_at,'' the_update_at,as the name of the city,'' as service_listing_idFROMwiki_city_list c participate in Propertyinfo p at c.city_id = p.cityjoin ynow_rentwiki.Property_Unitlayout Please take away p.prop_ynow_id = pu.P_Identjoin (SELECT CAST (substring_index (if (Rent> 0, Rent, RentLow), '.', 1) AS UNSIGNED) as minrent, CAST (substring_index (if (Loyer> 0, Rent, Renthigh), '.', 1 )) AS UNSIGNED) as maxrent, PRE_Ident, P_Ident, UNL_Ident, RTY_Ident from ynow_rentwiki Where property_rents P_Ident is in (9744) and (Rent! = '' Or (Rentlow! = '' And Renthigh! = ''))) As pr to pu.UNL_Ident = pr.UNL_IdentJoin point s at (p.state = s.stateid OR p.state = s.ShortCut)ORpu.Status = 'Active'and p.delete_date = '0000-00-00'BY GROUPc.city_id, p.prop_ynow_idUNIONSELECT AS''prop_ynow_id,id as a property identifier,0 as prop_add_value,t.name as a name,'' as an image,t. address as location,T. a city as a city,see ShortCut as a state,t.zip is essentially zip,CAST (REPLACE (REPLACE (t.price, '$', ''), ',', '') as UNSIGNED) although minrent,like Maxrent,t. service as a service,'' available as Hood_id,like a phone,t latitude in latitude,t. longitude as longitude,i.e. there are bathrooms in minbad,like maxbad,t. the room is like a bed,like maxbed,t.url as a URL,t.source_id as source_id,t.source_name in the view that source_name,t.addresscode as an address code,t.citycode as city code,t.ctime is ctime,i.e. paid as paid,t.similar_url, although Similar_url,t.created_at as created_at,t.updated_at as updated_at,SUBSTRING_INDEX (c.city_name, '_', 1) mainly because city_name,t.service_listing_id is essentially service_listing_idDE LBCPrimary.third_party_properties as t, LBCPrimary.wiki_city_list as, LBCPrimary.state as sORt.stadt in ('230')andAddress <> '', etc.t.city = c.city_id andc.city_state = s.stateidorder at t.ctimedescriptionLimit 46 as limit 0.50
You
Write any type of query in MySQL and get the big error “1248: Each view must have its own alias”? If so, then this is a good, reliable, and simple error that can be corrected.
How To FixMySQL “Each View Must Have Its Own Alias” Error
So you have a specific bug in MySQL. How to solve this problem?
The short answer you want is to give your subqueries an alias in your company’s SELECT statement. The alias for adding the FROM term subquery after the closing curly brace.
This is not required in 3rd party SQL, but MySQL requires you to create an alias for your current subqueries.
Derived Table Example
A Derived Chart is a SELECT subquery in the FROM clause of your main query. It helps, like a table, because it contains articles and returns rows. For example, it might be the old location of the table in some kind of FROM clause. It is often recognized as an inline view or simply as a specific subquery.
SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM ( SELECT o.order_id, o.Kunde_Stadt, o.order_amount ORDERS INNER JOIN member c ON o.customer_id = c.customer_id)GROUP BY Customer_City;
This query finds the location, the number of orders associated with the order invoices, and the total Accounts of orders from tables of orders and customers.
The request has an outer request and a body request. An internal query is used to find data about orders and customers:
How do you fix Every derived table must have its own alias?
How do I fix them? Short answer: you really want to use aliases for your subqueries in your SELECT statement. In the subquery of the FROM clause, add a strong alias after the closing parenthesis. Various SQL vendors do not require this, but MySQL requires you to provide aliases for your subqueries along the way.
SELECT o.order_id, o.customer_city, o.order_amountORDERSINNER JOIN customer f ON o.customer_id = c.customer_id
In this case, the data is initially taken from two tables, which are combined into a common row of work. This is a “derived table”.
The outer topic selects multiple columns prior to this subquery. Marketing information does not come from an array, but from the results of this subquery.
How do you derive a table in SQL?
A derived table is a specific table expression that appears in the FROM clause of a query. You can manipulate views if using Hug you aliases is not possible because the SQL translator processes another term if known before the alias.
SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM (...)GROUP BY Customer_City;
What is error 1248 in MySQL?
MYSQL ERROR 1248 (42000): Each view must have its own nickname.
It shows customer_city, two combined skills, and city groups.
SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM ( SELECT o.order_id, o.Kunde_Stadt, o.order_amount Delivery of AB from INTERNAL customer CONNECTION c ON o.customer_id = c.customer_id)GROUP BY Customer_City;
Error 1248: Each view must have its own alias
Solution For “Every View Must Have An Alias”
The reason for this error The point is that in MySQL, every dining view (a subquery that is a SELECT query) must have an alias.
In the sample query, there was no alias for your subquery here. The alias of the absolute subquery is the same as the alias of the channel. These are the closing parentheses of the FROM clause for a general subquery. B
So, in this example, we just need to add an alias after the closing parenthesis to fix this error.
An alias can be almost anything that interests you: a single letter, a word, several letters. I often use the word “under” when it is a small query (for a subquery), and something more descriptive when it is a longer specific query.
How do you fix Every derived table must have its own alias?
How to fix it? No doubt the short answer is that you need to give your subqueries an alias in your SELECT statement. Add the alias to the subquery of the FROM clause after the closing parenthesis. Other SQL providers do not require this, but MySQL expects you to create an alias for your subqueries.
SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM ( SELECT o.order_id, o.Kunde_Stadt, o.order_amount ORDERS INNER JOIN customer c ON o.customer_id implies c.customer_id) underGROUP BY Customer_City;
What does Every derived table must have its own alias?
Each derived bed (AKA subquery) must have all aliases. This means that each request in parentheses should be given an alias (regardless of AS) that can be used to refer to the situation in the rest of the interface request.
Notice how the word “sub” is added on the penultimate line after the parentheses are closed? This is your alias for a subquery or view. This alias is required in MySQL but ignored Other suppliers.
Updated
Are you tired of your computer running slow? Annoyed by frustrating error messages? ASR Pro is the solution for you! Our recommended tool will quickly diagnose and repair Windows issues while dramatically increasing system performance. So don’t wait any longer, download ASR Pro today!
This query should work and no longer receive the “Each table retrieved must have its own alias” error message.
Optional: Add The AS Keyword
You can add the AS keyword as this is a great optional part of adding an alias, but it’s up to you. If it still works:
SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM ( SELECT o.order_id, o.Kunde_Stadt, o.order_amount ORDERS INTERNAL customer CONNECTION c ON o.customer_id = c.customer_id) AS-SubGROUP BY Customer_City;
You can fix this derived table alias error in the same way. Add an alias to your subquery.
Speed up your computer today with this simple download.
Error 1248 De Mysql Sql
MySQL SQL 오류 1248
Ошибка SQL SQL 1248
Błąd MySQL Sql 1248
Erreur SQL SQL 1248
MySQL-SQL-Fehler 1248
Mysql SQL-fel 1248
Mysql SQL-fout 1248
Errore SQL MySQL 1248
Erro SQL 1248 Do MySQL
Вопрос:
Я получаю сообщение об ошибке при использовании этого запроса в MySQL.
Логика запроса верна, и я пробовал ее в Oracle, и она работает нормально, но я получаю сообщение об ошибке при работе в MySQL.
Я просмотрел предыдущие вопросы о StackOverflow, но не нашел что-то, чтобы помочь мне.
Вот запрос:
select * from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
from RATOR_IMP.PROCESS_MONITOR as PM
JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
ON PM.ID = PMS.PROCESS_MONITOR_ID
WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
order by PMS.PROCESS_START_DATE desc)
limit 10000;
И вот ошибка:
Error Code: 1248. Every derived table must have its own alias
No soultion found for query
Лучший ответ:
Вам нужно предоставить псевдоним для подзапроса, например:
select * from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
from RATOR_IMP.PROCESS_MONITOR as PM
JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
ON PM.ID = PMS.PROCESS_MONITOR_ID
WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
order by PMS.PROCESS_START_DATE desc) as s
limit 10000;
В документации,
Подзапросы являются законными в предложении FROM SELECT. Фактический синтаксис:
SELECT… FROM (подзапрос) [AS] name…
Предложение имени [AS] является обязательным, поскольку каждая таблица в предложении FROM должна иметь имя. Любые столбцы в списке выбора подзапроса должны иметь уникальные имена.
Ответ №1
Да вам нужно указать псевдоним для полученных данных
select x.* from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
from RATOR_IMP.PROCESS_MONITOR as PM
JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
ON PM.ID = PMS.PROCESS_MONITOR_ID
WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
order by PMS.PROCESS_START_DATE desc)x <-- here
limit 10000;
Brief
I encountered this problem when learning mysql.
Check it outhttps://stackoverflow.com/questions/3363918/mysql-error-1248-42000-every-derived-table-must-have-its-own-alias This is the answer given by the stackoverflow.
In fact, it is a very simple question.
In the process of multi-level query, you will need to give the table an alias.
Code
Wrong query method:
Select * from (select s.sno from student s where s.ssex='female');
The correct way to query:
Select * from (select s.sno from student s where s.ssex='female') as mid_sno;
In fact, the difference is whether there is one behind it.as mid_sno。
This is to ensure that each derived table needs to have its own alias.
Loading