CODEWITHSUNDEEP
mysqljdbc
anonymous
anonymous

Reputation: 244

add an extra column while select from two tables

i have two tables with me, web and mail. currently i am using this query to fetch data from both tables. 

PreparedStatement ps = con.prepareStatement("(select * from web where name='abc') union (select * from mail where name='abc')");
ResultSet rs = ps.executeQuery();
while(rs.next()){      
bw.write(rs3.getString("name")+"~"+rs3.getString("age")+"~"+rs3.getString("profession");
bw.newLine();
}

The output is something like this.

+------+------+------------+
| name | age  | profession |
+------+------+------------+
| abc  |   20 | doctor     |
| abc  |   20 | engineer   |
+------+------+------------+

and in the file its like this

abc~20~doctor
abc~20~engineer

but how can i add an extra column in the result set which will give me data in this format

abc~20~doctor~web
abc~20~engineer~mail

Upvotes: 0

Views: 113

Answers (3)

A. Zalonis
A. Zalonis

Reputation: 1609

Try this 

PreparedStatement ps = con.prepareStatement("(select name,age,profession ,'web' AS ExtraColumnfrom from web where name='abc') union (select name,age,profession ,'mail' AS ExtraColumnfrom mail where name='abc')");
ResultSet rs = ps.executeQuery();
while(rs.next()){      
bw.write(rs3.getString("name")+"~"+rs3.getString("age")+"~"+rs3.getString("profession")+"~"+rs3.getString("ExtraColumnfrom ");
bw.newLine();
}

Upvotes: 1

Abhineet Prasad
Abhineet Prasad

Reputation: 1271

select * , 'web' as source from web where name='abc' union
select *, 'mail' as source from mail where name ='abc'

Upvotes: 0

Amitkumar Arora
Amitkumar Arora

Reputation: 149

try this 

select * , 'web' as tablename from web where name='abc' union
select * ,'mail' as table namefrom mail where name='abc'

Upvotes: 3

Related Questions