Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join

Author

Topic

PatD1
Starting Member

3 Posts

Posted - 2009-09-17 : 12:46:44

I want to join two tables to get my report. copied below is the the sql to create tables and enter few rows of sample data. The sql that I am using to generate my usage report is also copied below. This sql is not giving me the desired result. I want to see a recore in the report for each record in the usage table. But this sql is showing records only 5 records when I have 9 records in the usage table. Please help. The records that are missed are where a.UserName and b.UserName are different i.e. DKV is looking at PD1 records.

Thanks.

CREATE TABLE [dbo].[HV_Users](
[RecID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DispName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Phone] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserType] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddDate] [datetime] NOT NULL,
[PhysicianID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PhysicianName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HealthCoachID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Confirmed] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_HV_Users_1] PRIMARY KEY CLUSTERED
(
[RecID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HV_Usage](
[UserID] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RecID] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UsageDateTime] [datetime] NOT NULL,
[UsageMin] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PageURL] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

INSERT HV_Users
VALUES('029a9296','3d85857ffe75','PatD1','PD1','pd1@gmail.com','1111111111','P','2009-09-14 16:38:50.917',NULL,'pemu',NULL,'y');

INSERT HV_Users
VALUES('902ab3d7','3b88f5c9c693','DKV','DKV','dkv@gmail.com','1111111111','P','2009-09-16 16:38:50.917',NULL,'pemu',NULL,'y');
GO

INSERT HV_Usage
VALUES ('902ab3d7', '3b88f5c9c693', 'UPPage.aspx', '2009-09-16 14:05:37.997', '00:00:19'),
('902ab3d7', '3b88f5c9c693', 'BG1.aspx', '2009-09-16 14:05:39.103', '00:00:19'),
('902ab3d7', '3b88f5c9c693', 'BP11.aspx', '2009-09-16 14:05:40.323', '00:00:19'),
('902ab3d7', '3d85857ffe75', 'UPPage.aspx', '2009-09-16 14:08:36.120', '00:00:01'),
('902ab3d7', '3d85857ffe75', 'UPPage.aspx', '2009-09-16 14:08:38.010', '00:00:01'),
('902ab3d7', '3d85857ffe75', 'BG1.aspx', '2009-09-16 14:08:41.027', '00:00:02'),
('902ab3d7', '3d85857ffe75', 'BP1.aspx', '2009-09-16 14:08:42.713', '00:00:01'),
('029a9296', '3d85857ffe75', 'UPPage.aspx', '2009-09-16 14:07:19.353', '00:00:01'),
('029a9296', '3d85857ffe75', 'BG1.aspx', '2009-09-16 14:07:20.870', '00:00:01')

Here is the SQL.

SELECT a.UserName,
b.UserName,
u.UsageDateTime,
u.PageURL,
u.UsageMin
FROM HV_Users a JOIN HV_Usage u
ON a.UID = u.UserID
JOIN HV_Users b
ON b.RecID = u.RecID;

If HV_Usage has 9 records, I expect this query to return 9 records, not 5.

Thank You for your help.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-17 : 12:52:29

You want to use a left join.

An infinite universe is the ultimate cartesian product.

PatD1
Starting Member

3 Posts

Posted - 2009-09-17 : 14:08:40

quote:
Originally posted by cat_jesus

You want to use a left join.

An infinite universe is the ultimate cartesian product.

I am new with SQL. Please show me how to.

Thank you.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-17 : 14:12:05

maybe this??

SELECT a.UserName,
u.UsageDateTime,
u.PageURL,
u.UsageMin
FROM HV_Usage u
LEFT JOIN HV_Users a
ON a.UID = u.UserID

PatD1
Starting Member

3 Posts

Posted - 2009-09-17 : 14:26:19

It does not show the second column (Username for recID).

I want to display both (Username for for UserID and Username for RecID), so that the report shows which user was looking at which records.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-17 : 16:04:22

[code]SELECT
RecUser.UserName,
ReportUser.UserName,
Usage.UsageDateTime,
Usage.PageURL,
Usage.UsageMin
FROM
HV_Users AS RecUser
INNER JOIN
HV_Usage AS Usage
ON RecUser.RecID = Usage.RecID
INNER JOIN
HV_Users AS ReportUser
ON ReportUser.UID = Usage.UserID
[/code]

If it is possible not to have someone look at the report yet, then you'd need a LEFT OUTER JOIN to make sure you get all the reports listed:[code]SELECT
RecUser.UserName,
ReportUser.UserName,
Usage.UsageDateTime,
Usage.PageURL,
Usage.UsageMin
FROM
HV_Users AS RecUser
INNER JOIN
HV_Usage AS Usage
ON RecUser.RecID = Usage.RecID
LEFT OUTER JOIN
HV_Users AS ReportUser
ON ReportUser.UID = Usage.UserID[/code]
Incase it's needed, here is the corrected dataset:[code]-- DROP TABLE HV_Users
CREATE TABLE [dbo].[HV_Users](
[UID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DispName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Phone] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserType] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddDate] [datetime] NOT NULL,
[PhysicianID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PhysicianName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HealthCoachID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Confirmed] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_HV_Users_1] PRIMARY KEY CLUSTERED
(
[RecID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

-- DROP TABLE HV_Usage
CREATE TABLE [dbo].[HV_Usage](
[UserID] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RecID] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UsageDateTime] [datetime] NOT NULL,
[UsageMin] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PageURL] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

INSERT HV_Users
VALUES('029a9296','3d85857ffe75','PatD1','PD1','pd1@gmail.com','1111111111','P','2009-09-14 16:38:50.917',NULL,'pemu',NULL,'y');

INSERT HV_Users
VALUES('902ab3d7','3b88f5c9c693','DKV','DKV','dkv@gmail.com','1111111111','P','2009-09-16 16:38:50.917',NULL,'pemu',NULL,'y');
GO

INSERT HV_Usage
VALUES ('902ab3d7', '3b88f5c9c693', '2009-09-16 14:05:37.997', '00:00:19' , 'UPPage.aspx'),
('902ab3d7', '3b88f5c9c693', '2009-09-16 14:05:39.103', '00:00:19', 'BG1.aspx'),
('902ab3d7', '3b88f5c9c693', '2009-09-16 14:05:40.323', '00:00:19', 'BP11.aspx'),
('902ab3d7', '3d85857ffe75', '2009-09-16 14:08:36.120', '00:00:01', 'UPPage.aspx'),
('902ab3d7', '3d85857ffe75', '2009-09-16 14:08:38.010', '00:00:01', 'UPPage.aspx'),
('902ab3d7', '3d85857ffe75', '2009-09-16 14:08:41.027', '00:00:02', 'BG1.aspx'),
('902ab3d7', '3d85857ffe75', '2009-09-16 14:08:42.713', '00:00:01', 'BP1.aspx'),
('029a9296', '3d85857ffe75', '2009-09-16 14:07:19.353', '00:00:01', 'UPPage.aspx'),
('029a9296', '3d85857ffe75', '2009-09-16 14:07:20.870', '00:00:01', 'BG1.aspx')[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources