CODEWITHSUNDEEP
mysql
vincy
vincy

Reputation: 605

I am getting an error as "Unknown table" error?

I have declared a a table as "m" still the query is giving me an arror. Here is the query:

select m.id AS id,
       m.product_id AS product_id,
       m.status AS `status`,
       m.pmatch AS pmatch,
       m.p_offset AS p_offset,
       m.smatch AS smatch,
       m.s_offset AS s_offset,
       m.tmatch AS tmatch,
       m.t_offset AS t_offset,
       m.txt1hex AS txt1hex,
       m.txt2hex AS txt2hex,
       m.txt3hex AS txt3hex,
       m.http_host AS http_host,
       m.http_uri AS http_uri,
       m.http_user_agent AS http_user_agent,
       m.http_cookie AS http_cookie,
       m.direction AS direction,
       m.trans AS trans,
       m.conn AS conn,
       m.min_pl_len AS min_pl_len,
       m.max_pl_len AS max_pl_len,
       m.stream_off AS stream_off,
       m.packet_num AS packet_num,
       m.sip AS sip,
       m.sp AS sp,
       m.dip AS dip,
       m.dp AS dp,
       m.stage AS stage,
       m.func_index AS func_index,
       m.usage AS usage,
       m.comments AS comments,
       m.created AS created,
       m.createdby AS createdby,
       m.updated AS updated,
       m.updatedby AS updatedby
from   sp_mip_rule m
where  exists(select 1 AS 1
              from vw_extr_active_socnet_product p
              where ((p.id = sp_mip_rule.product_id) and (sp_mip_rule.status = 1) ))

Upvotes: 1

Views: 1099

Answers (3)

Álvaro González
Álvaro González

Reputation: 146410

<1> You have an unmatched quote:

m.stream_off` AS stream_off

<2> USAGE is a reserved word. You need to quote it:

m.usage AS `usage`

<3> If you want to use 1 as column alias, you need to quote it:

select 1 AS '1'

... or:

select 1 AS `1`

... though of course it's not really necessary to alias a column with its own name.

I suggest you write your SQL code in several lines and use an editor with syntax highlighting. That makes it easier to spot this kind of errors.

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135789

The parens are off in your WHERE clause. You've got the m.status=1 buried inside the exists. I think you want that check outside of the exists.

...
where m.status = 1 
    and exists(select 1 from vw_extr_active_socnet_product p where p.id = m.product_id)

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270609

Since you have aliased the table as m in the FROM clause, I think you must also refer to it as m in the WHERE clause:

select m.id AS id,abunchofotherstuff
from sp_mip_rule m
where exists(select 1 AS 1 from vw_extr_active_socnet_product p where ((p.id = m.product_id) and (m.status = 1) ))

Upvotes: 2

Related Questions