CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

get tablename where the variable in a column matches

How to get the tablenames where nname is "new5" in all tables?.I have tried the query below but I dont get the solution(I dont think the query is right either).I really appreciate any help.Thanks in Advance.

http://sqlfiddle.com/#!2/aa1b8/7

CREATE TABLE if not exists tblC1
(
id int(11) NOT NULL auto_increment ,
nname varchar(255),
 PRIMARY KEY (id)
);

INSERT INTO tblC1 (id, nname) VALUES
('1', 'new1'),
('2', 'new2'),
('3', 'new3'),
('4', 'new4'),
('5', 'new5');


CREATE TABLE if not exists tblC2
(
id int(11) NOT NULL auto_increment ,
nname varchar(255),
 PRIMARY KEY (id)
);

INSERT INTO tblC2 (id, nname) VALUES
('1', 'new1'),
('2', 'new21'),
('3', 'new31'),
('4', 'new41'),
('5', 'new51');

CREATE TABLE if not exists tblC3
(
id int(11) NOT NULL auto_increment ,
nname varchar(255),
 PRIMARY KEY (id)
);

INSERT INTO tblC3 (id, nname) VALUES
('1', 'new1'),
('2', 'new21'),
('3', 'new31'),
('4', 'new41'),
('5', 'new5');

Query:

SELECT  * 
FROM (SELECT  * FROM   tblC1 
      UNION SELECT * FROM tblC2  
      UNION SELECT * FROM tblC3 ) as t2  
where nname = "new5"

Upvotes: 0

Views: 24

Answers (1)

VMai
VMai

Reputation: 10336

Add the table name to your single selects:

SELECT * FROM (
    SELECT 
        'tblC1' as tbl, tblC1.*
     FROM   tblC1 
      UNION SELECT 'tblC2', tblC2.* FROM tblC2  
      UNION SELECT 'tblC3', tblC3.* FROM tblC3 
) t
where t.nname = "new5"

should do. You need either the outer select or a where clause for every single select of your unions. And you've got to specify the tablename,

Fiddle

Upvotes: 1

Related Questions