0

On my webserver there is a database with following two tables:

tbl_Friend                                    tbl_Colleague

| id | Name | First name | Place |            | id | Name | First name | Place | 
----------------------------------            ----------------------------------
|  1 | XXXX | XXXXXXXXXX |   1   |            |  1 | AAAA | AAAAAAAAAA |   1   |
|  2 | YYYY | YYYYYYYYYY |   2   |            |  2 | BBBB | BBBBBBBBBB |   3   |
|  3 | ZZZZ | ZZZZZZZZZZ |   1   |            |  3 | CCCC | CCCCCCCCCC |   4   |

Now I want to fetch all Persons from tbl_Friend and tbl_Colleague who live in place 1. For that I have to the data from both tables and here is my problem: How can I fetch data from two different tables in only one query? My result should look like this:

| id | Name | First name | Place |
----------------------------------
|  1 | XXXX | XXXXXXXXXX |   1   |
|  1 | AAAA | AAAAAAAAAA |   1   |
|  3 | ZZZZ | ZZZZZZZZZZ |   1   |

Can I use something like FROM tbl_Friend | tbl_Colleague or something else? Or do I have to use a Join for this?

Ronan Boiteau
  • 9,608
  • 6
  • 34
  • 56
Cilenco
  • 6,951
  • 17
  • 72
  • 152

2 Answers2

3

Try this:

SELECT id, Name, First name, Place FROM tbl_Friend
  WHERE Place= 1
UNION ALL
SELECT id, Name, First name, Place FROM tbl_Colleague
  WHERE Place= 1
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
  • +1. The `UNION ALL` set operator will return *all* matching rows, and doesn't require the extra step of identifying and removing duplicates. This will return resultset specified by OP, although we notice the rows will be in a different order. OP can add an `ORDER BY` clause if there's a particular sequence he wants the rows. (OP didn't ask this, but in queries like this, I will typically include an additional short discriminator column: a literal in the SELECT list of each query that I can use to identify which query returned the row. `SELECT 'f' AS src, ... UNION ALL SELECT 'c', ...` – spencer7593 Apr 24 '14 at 23:19
  • @Cilenco, notice, that your schema doesn't normalized. – Hamlet Hakobyan Apr 24 '14 at 23:23
  • Yes I know. This is just a cutout from my whole table to make it easier here. – Cilenco Apr 24 '14 at 23:26
-1

try this:

select * from tbl_friend a, tbl_colleague b where a.place = b.place and place like '1';

arvind
  • 1
  • 1
  • correction:select * from tbl_friend a, tbl_colleague b where a.place = b.place and a.place like '1'; – arvind May 04 '17 at 08:22