SQL query join tables data

I have 2 tables Users and TimeSheets, Timesheets table has column UserId which is users id form users table obviously 🙂

Logic

And I’m trying to get all data of Timesheets table plus just name of user from users table but somehow it seems like my query is other way around!

Now I know this might be a simple query but I personally have no idea about SQL (my bad!)

Here is my query:

CREATE PROCEDURE [dbo].[GetTimeSheets]
AS
BEGIN
    SELECT 
        t.Id, t.Enter_time, t.Exit_Time, t.Total, t.Date, 
        t.Month, t.Year, u.Name AS username
    FROM 
        TimeSheets t
    FULL OUTER JOIN 
        Users u ON u.Id = t.UserId
    GROUP BY 
        t.Id, t.Enter_time, t.Exit_Time, t.Total, t.Date, 
        t.Month, t.Year, u.Name
END;

And here is screenshot of returned data:

As you can see I get my users names without any data being existed in timesheets table!

This view supposed to get timesheets table and then username of each row from users.

Update

Here is my timesheets table schema

CREATE TABLE [dbo].[TimeSheets]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [UserId] INT NOT NULL, 
    [Enter_time] TIME NOT NULL, 
    [Exit_Time] TIME NULL, 
    [Total] TIME NULL, 
    [Date] DATE NULL, 
    [Month] NVARCHAR(50) NOT NULL , 
    [Year] NVARCHAR(50) NOT NULL , 

    CONSTRAINT [FK_TimeSheets_Users] 
        FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users]([Id])
);

And here is users table

CREATE TABLE [dbo].[Users] 
(
    [Id]       INT            NOT NULL,
    [UserType] NVARCHAR (50)  NOT NULL,
    [Name]     NVARCHAR (100) NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Answer

It seems that you want an INNER join and since you are not doing any aggregation there is no need for GROUP BY:

SELECT t.*, 
       u.Name AS username
FROM TimeSheets t
INNER JOIN Users u 
ON u.Id = t.UserId;

By doing an INNER join you get results only when there is a match between 2 rows in the 2 tables.

Advertisement

Answer