连接两个表以获取整个记录 [英] Join of two tables as to get entire records

问题描述

我创建了两个表。表1有以下模式

  user_id int not null，autoincrement 
 movie _id int not null 
 movie_name varchar 
 user_name varchar 
 rating int 
 genre varchar 
 
 user_Id movie_Id movie_name user_name rating class 
 1 1 Twister Alex 6戏剧
 2！ Twister Tim 1 Drama 
（续）.. 
  

p>

  movie _id int not null 
 movie_name varchar 
 user_name varchar 
 genre varchar 
 b $ b movie_Id movie_Name user_name genre 
 2 Harry Porter Alex Fantay 
 3 Narnia Alex幻想
 .. 
 ...（续）
  

现在，当我插入一个查询值时，它首先检查第一个表中是否存在以下用户名。如果为true，则插入第二个表，否则按照模式将值插入第一个表中。换句话说，第一个表具有唯一的用户名和唯一的user_Id，而第二个表包含许多重复的用户名和电影。

现在我想要连接表1和表2，以便它包含表的所有列和user_name
的唯一user_Id和他们已经看到的电影，并用genre

分级

例如

  user_id movie_Id movie_name user_name rating class 
 1 1 Twister Alex 6 Drama 
 1 2 Harry Porter Alex 7幻想
 1 3 Narnia Alex 6幻想
 2 1 Twister Tim 1戏剧
  

...
等等

感谢

解决方案

尝试此

  select * from table2 inner join table1 on table2.user_id = table1.user_id 
  

I am creating two tables .Table 1 has following schema

user_id       int            not null,autoincrement
movie _id     int            not null 
movie_name    varchar        
user_name     varchar
rating        int
genre         varchar

user_Id    movie_Id    movie_name  user_name   rating  genre
1           1           Twister    Alex          6      Drama
2           !           Twister    Tim           1      Drama
(contd..)

while table 2 has following schema

movie _id     int            not null 
movie_name    varchar        
user_name     varchar
genre         varchar

movie_Id   movie_Name    user_name   genre
2          Harry Porter  Alex        Fantay
3          Narnia        Alex        Fantasy
..
...(contd)

Now when I put a query to insert values it first checks whether a following username exist in first table .If true then it inserts into second table otherwise it inserts value as per schema into first table.In other words first table has unique usernames and unique user_Id while second contains many repeated usernames with movie they have seen

Now I want to join Table 1 and table 2 ,so that it contains all the colums of both table and a unique user_Id for user_name and the movie they have seen and rating with genre

like for example

user_id   movie_Id  movie_name   user_name   rating  genre
1         1         Twister       Alex        6      Drama
1         2         Harry Porter  Alex        7      Fantasy
1         3         Narnia        Alex        6      Fantasy
2         1         Twister       Tim         1      Drama

... and so on

Thanks

解决方案

try this

select * from table2 inner join table1 on table2.user_id= table1.user_id

这篇关于连接两个表以获取整个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！