Thursday 12 November 2015

sql multiple inner joins to same table



CREATE TABLE Table1
([weddingtable] int, [tableseat] int, [tableseatid] int, [name] varchar(4),
    [Created] int,[Modified] int)
;

INSERT INTO Table1
([weddingtable], [tableseat], [tableseatid], [name],[Created],[Modified[)
VALUES
(001, 001, 001001, 'bob',1,2),
(001, 002, 001002, 'joe',2,3),
(001, 003, 001003, 'dan',2,2),
(002, 001, 002001, 'mark',3,1)
;

CREATE TABLE Table2
([weddingtable] int, [tableseat] int, [meal] varchar(7))
;

INSERT INTO Table2
([weddingtable], [tableseat], [meal])
VALUES
(001, 001, 'chicken'),
(001, 002, 'steak'),
(001, 003, 'salmon'),
(002, 001, 'steak')
;
 
 CREATE TABLE usert
([userID] int,  [userName] varchar(7))
;
                                                                         
INSERT INTO usert
([userID], [userName])
VALUES
(1, 'A'),
(2, 'B'),
(3, 'C')
;
       
-----------------------
--Query

select * from Table1 t1
inner join usertable u
on t1.cre=u.userID
inner join usertable u1
on t1.Mod=u1.userID