Using Where in Select statement while Union in Mysql

Question

i am facing problems while specifying where condition in select statement for uninon of my three tables. Here is my code

create table master_data(
select id,number,product_id
from tablename_A
where id REGEXP '^[0-9]*$' and id != ' ' and product_id != 0 and product_id is not null
union
select id,number,product_id
from tablename_B
where id REGEXP '^[0-9]*$' and id != ' ' and product_id != 0 and product_id is not null
union
select id,number,product_id
from tablename_C
where id REGEXP '^[0-9]*$' and id != ' ' and product_id != 0 and product_id is not null
);

i am getting this error:

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 'id != ' ' and product_id != 0 and product_id is not nullunionselect id' at line 8

@Drew it's not a dupe sir. the least you could do is help me if you know the answer. Thankyou. — Shubham R
– Shubham R, Commented Oct 25, 2016 at 7:07
What, the dupe looks pretty unrelated as the OP asks for the error in the select statement — Philipp
– Philipp, Commented Oct 25, 2016 at 7:07
@SRingne " product_id is not nullunionselect v" You are missing whitespaces in your query. Check the error message. Though I can't find that line in your code. Are you showing the correct snippet? — Philipp
– Philipp, Commented Oct 25, 2016 at 7:08
reopened my bad. Will be back in a few to see where you are at with it — Drew
– Drew, Commented Oct 25, 2016 at 7:12
@Philipp i editer the question, please take a look at the error part "product_id is not nullunionselect id" — Shubham R
– Shubham R, Commented Oct 25, 2016 at 7:12

Philipp · Accepted Answer · 2016-10-25 07:28:54Z

1

first of all, what is 'REGEXP' ?

I prefer this:

WITH master_data AS 
(
   select
      id,
      number,
      product_id 
   from
      tablename_A 
   union
   select
      id,
      number,
      product_id 
   from
      tablename_B 
   union
   select
      id,
      number,
      product_id 
   from
      tablename_C 
)
SELECT
   * 
FROM
   master_data 
WHERE
   id NOT IN '^[0-9]*$' 
   and id != ' ' 
   and product_id != 0 
   and product_id is not null

edited Oct 25, 2016 at 7:28

Philipp

2,7962 gold badges27 silver badges28 bronze badges

answered Oct 25, 2016 at 7:24

Pouya Kamyar

1331 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shubham R Over a year ago

REGEXP' is used for removing alphanumeric values in id column and selecting only numbers. my id column is of categogy varchar(100)

Drew · Accepted Answer · 2016-10-25 07:32:34Z

The following works fine on mysql 5.6.31 (Microsoft Server) and mysql 5.7.14 (Redhat Linux)

create table tablename_A
(   id int not null,
    product_id int not null,
    number int not null
);
create table tablename_B
(   id int not null,
    product_id int not null,
    number int not null
);
create table tablename_C
(   id int not null,
    product_id int not null,
    number int not null
);


create table master_data as
select id,number,product_id
from tablename_A
where (id REGEXP '^[0-9]*$') and id != ' ' and product_id != 0 and product_id is not null
union
select id,number,product_id
from tablename_B
where (id REGEXP '^[0-9]*$') and id != ' ' and product_id != 0 and product_id is not null
union
select id,number,product_id
from tablename_C
where id REGEXP '^[0-9]*$' and id != ' ' and product_id != 0 and product_id is not null;

Collectives™ on Stack Overflow

Using Where in Select statement while Union in Mysql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related