CODEWITHSUNDEEP
sqlmysqlconcatenation
databasedude
databasedude

Reputation: 103

MYSQL - Concatenate two tables

I have two tables as follows:

    TABLE A                    TABLE B
 StuID | actid              FacID | actid
  3       12                  98      17
  5       17                  54      21

I want to list the name of everyone, both students and faculty, who participate in activity 17. Is there anyway I can get a result as below:

 id  | actid
 98     17
 5      17

WITHOUT creating a new table (by just using nesting of expressions or derived relations) ?

A JOIN on the actid would give something like:

StuID  | FacID  | actid
 5        98        17

I guess I need a form of concatenation??

Upvotes: 10

Views: 58874

Answers (4)

Harshal Bisht
Harshal Bisht

Reputation: 1

its too easy select tableA.stuId,tableA.actId, tableB.facId,tableB.actId from tableA,tableB where tableA.actid=tableB.actid;`

Upvotes: -2

programmer master
programmer master

Reputation: 1

You can concatenate these two tables by using select, from and where.

Upvotes: -6

Thilo
Thilo

Reputation: 262474

select * from table_a where actid = 17
union all
select * from table_b where actid = 17

You may (or may not) need to do something about the ids not being unique, such as

select 'Student', table_a.* from table_a where actid = 17
union all
select 'Faculty', table_b.* from table_b where actid = 17

Upvotes: 25

Joshua Martell
Joshua Martell

Reputation: 7212

You want UNION ALL:

(SELECT * FROM tablea) UNION ALL (SELECT * FROM tableb)

I think those parenthese are correct. I remember MySQL being fussy about this.

Upvotes: 2

Related Questions