CODEWITHSUNDEEP
sqlsql-servert-sqljoinstored-procedures
mafortis
mafortis

Reputation: 7128

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:

one

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)
);

Upvotes: 1

Views: 60

Answers (2)

persian-theme
persian-theme

Reputation: 6638

Well, in order to access users, you have to give the user table to TimeSheets LEFT JOIN so that if the user profile is empty.

SELECT t.Id, t.Enter_time, t.Exit_Time, t.Total, t.Date, t.Month, t.Year, u.Name as username
FROM Users u
LEFT JOIN TimeSheets t 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

Upvotes: 0

forpas
forpas

Reputation: 164069

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.

Upvotes: 2

Related Questions