CODEWITHSUNDEEP
phpmysql
VMeijer
VMeijer

Reputation: 433

Problems with MYSQL use 3 tables in one query

I'm struggling with a mysql query, I would like to create a query with multiple table's and be able to search or specific columns within all three tables at once, Database and tables are already created and filled. (sorry if this is easy but i'm fairly new at mysql)

Example:

table 1

`ID`,`number`,`name`,`genre`,`location`,`type` 
1, 1001, Linkin Park, Rock, L001, cd
2, 1002, 3 doors down, Alternative, L002, cd
3, 1003, golden earring, Rock, L003, cd

table 2

`ID`,`number`,`name`,`genre`,`location`,`type` 
1, 1001, Kill bill, Action, L001, dvd
2, 1002, Hulk, Action, L002, dvd
3, 1003, Driven, Action, L003, dvd

table 2

`ID`,`number`,`name`,`genre`,`location`,`type` 
1, 1001, Call of Duty, Action, L001, Game
2, 1002, Battlefield, Action, L002, Game
3, 1003, Red Alert, strategy, L003, Game

something like: 

SELECT 'number', 'name', 'genre', 'location', 'type' FROM tb1, tb2, tb3 WHERE type = 'Game'

Upvotes: 0

Views: 74

Answers (1)

CompuChip
CompuChip

Reputation: 9232

One way you can achieve what you want is making a UNION of all the tables, as follows:

 SELECT `number`, `name`, `genre`, `location`, `type` FROM 
( SELECT `number`, `name`, `genre`, `location`, `type` FROM tb1
  UNION ALL
   SELECT `number`, `name`, `genre`, `location`, `type` FROM tb2
   UNION ALL 
  SELECT `number`, `name`, `genre`, `location`, `type` FROM tb3 ) tb123
WHERE tb123.`type` = 'Game'

But I would actually recommend you look into setting up your database differently . For example, put everything into one table.

Upvotes: 1

Related Questions