CODEWITHSUNDEEP
mysqlsql
zoro724
zoro724

Reputation: 87

How to merge two tables in a mysql query

First of sorry I know mysql is not recommended any more but in this case I have no control and have to use it.

Now onto the question.

I have two tables Games and videos

Inside games I have

| id | gameID | GameTitle | 
| 1  | 1      | Halo ODST |
| 2  | 2      | Disgaea 4 |

Inside videos I have

| id | game | videoTitle | image |
| 1  | 1    | Title 1    | PATH  |
| 2  | 1    | Title 2    | PATH  |
| 3  | 2    | Title 3    | PATH  |
| 4  | 1    | Title 4    | PATH  |

I need to basically do the following 

Select x,y,z from video where videos.game = games.gameID

which will basically read

select id, videoTitle, image from videos where video.game = 1

(or some other numeric value)

I’m aware I have to use a join however nothing I have tried appears to be working and yeah I’m getting nowhere with this.

The closest I am is the below query which says it works but is returning an empty result set so clearly its wrong somewhere.

SELECT * FROM `games` INNER JOIN `videos` on `game` WHERE `game` = 1

If its any help I'm using phpmyadmins sql query tool rather than actual code at this stage as i just want to get it working before coding it.

Any help is greatly appreciated.

Thanks.

Upvotes: 0

Views: 55

Answers (2)

mohammad haris
mohammad haris

Reputation: 145

you can use view to merge table and can perform operations on that..

create view view_name as
select Games.id,gameID, GameTitle,videos.id, game, videoTitle, image 
from Games,videos
where videos.game = games.gameID

where section will contain ids or anything you want to match

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425863

SELECT  *
FROM    `games` g
INNER JOIN
        `videos` v
ON      v.game = g.gameId
WHERE   g.gameId = 1

Upvotes: 1

Related Questions