CODEWITHSUNDEEP
jquerymysqlajaxautocompletejquery-autocomplete
Azylak
Azylak

Reputation: 157

MySQL - 3 different tables with the same column join in 1 column

So I'm trying to join 3 tables into 1 column when I query them.

The three tables are

investigators.name, ancientones.name,monsters.name.

The query I used to call back all the names is

SELECT investigators.name ,ancientones.name ,monsters.name FROM investigators, ancientones, monsters WHERE ((investigators.name LIKE'a%') OR (ancientones.name LIKE'a%')OR (monsters.name LIKE'a%'))

But what this returns is 3 table rows with the "name" with the values from table 1, 2 and 3. I want all the values in a single column, but I don't have that much SQL experience..

Upvotes: 0

Views: 187

Answers (2)

Bobby
Bobby

Reputation: 2938

try using '+' between column names

SELECT investigators.name + ' ' + ancientones.name + ' ' + monsters.name FROM investigators, ancientones, monsters WHERE ((investigators.name LIKE'a%') OR (ancientones.name LIKE'a%')OR (monsters.name LIKE'a%'))

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

you need to use UNION (or UNION ALL to keep duplicates)

SELECT name
FROM investigators
WHERE name like 'a%'
UNION
SELECT name 
FROM anientones
WHERE name like 'a%'
UNION
SELECT name
FROM monsters
WHERE name like 'a%'

Upvotes: 1

Related Questions