Inner join not returning any results

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 2000 Forums

Transact-SQL (2000)

Inner join not returning any results

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-03 : 11:52:34

Any help on this would be apreciated as my SQL skills are still limited. I have the following query which should return results but currently does not for some reason. The aim of the query is to return all records that are users belonging to both applications. I know that there are contacts that fit this criteria.


SELECT     Contacts.FirstName, Contacts.LastName, Contacts.Country, Contacts.Email, Applications.Name AS Application
FROM         Contacts INNER JOIN
                      Contact_Application ON Contacts.ContactId = Contact_Application.ContactId RIGHT OUTER JOIN
                      Applications ON Contact_Application.ApplicationId = Applications.ApplicationId
WHERE     (Applications.ApplicationId = '0c1eeb41-e4ce-450d-a925-685901442005') AND (Applications.ApplicationId = 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')

www.creativenrg.co.uk

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 11:56:28

Change this line:

WHERE     (Applications.ApplicationId = '0c1eeb41-e4ce-450d-a925-685901442005') AND (Applications.ApplicationId = 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')

WHERE Applications.ApplicationId in ('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-03 : 12:20:23

Thanks for the quick response, however I am still not sure this provides the results I need and seems to act like an OR query? I need all contacts that have a entries in the Contact_Application table for both of the specified ApplicationId's.

www.creativenrg.co.uk

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-03 : 12:22:51

see example #2 here:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/12/60230.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 12:26:31

SELECT Contacts.FirstName, Contacts.LastName, Contacts.Country, Contacts.Email, Applications.Name AS Application
FROM Contacts
INNER JOIN Contact_Application ON Contacts.ContactId = Contact_Application.ContactId
RIGHT JOIN Applications ON Contact_Application.ApplicationId = Applications.ApplicationId
AND Applications.ApplicationId IN('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')

Peter Larsson
Helsingborg, Sweden

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-03 : 12:47:25

[quote]Originally posted by Peso

SELECT Contacts.FirstName, Contacts.LastName, Contacts.Country, Contacts.Email, Applications.Name AS Application
FROM Contacts
INNER JOIN Contact_Application ON Contacts.ContactId = Contact_Application.ContactId
RIGHT JOIN Applications ON Contact_Application.ApplicationId = Applications.ApplicationId
AND Applications.ApplicationId IN('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')

This is close except it still include records that have either/or and not both.

www.creativenrg.co.uk

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-03 : 13:39:48

[code]SELECT *
FROM Contacts C
JOIN (
SELECT CA1.ContactId
FROM Contact_Application CA1
WHERE CA1.ApplicationId IN('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')
GROUP BY CA1.ContactID
HAVING COUNT(*) = 2
) D
ON C.ContactID = D.ContactID
JOIN Contact_Application CA
ON C.ContactID = CA.ContactID
RIGHT JOIN Applications A
ON CA.ApplicationId = A.ApplicationId[/code]

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-03 : 16:24:07

quote:

Originally posted by Ifor

SELECT *
FROM Contacts C
    JOIN (
            SELECT CA1.ContactId
            FROM Contact_Application CA1
            WHERE CA1.ApplicationId IN('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')
            GROUP BY CA1.ContactID
            HAVING COUNT(*) = 2
        ) D
        ON C.ContactID = D.ContactID
    JOIN Contact_Application CA
        ON C.ContactID = CA.ContactID
    RIGHT JOIN Applications A
        ON CA.ApplicationId = A.ApplicationId

That seems to be the one that does it, thanks very much for the help. This is a great forum and I look forward to further developing my SQL skills with help of this community.

www.creativenrg.co.uk

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-03 : 17:11:25

quote:

Originally posted by CreativeNRG

quote:

Originally posted by Ifor

SELECT *
FROM Contacts C
    JOIN (
            SELECT CA1.ContactId
            FROM Contact_Application CA1
            WHERE CA1.ApplicationId IN('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')
            GROUP BY CA1.ContactID
            HAVING COUNT(*) = 2
        ) D
        ON C.ContactID = D.ContactID
    JOIN Contact_Application CA
        ON C.ContactID = CA.ContactID
    RIGHT JOIN Applications A
        ON CA.ApplicationId = A.ApplicationId

That seems to be the one that does it, thanks very much for the help. This is a great forum and I look forward to further developing my SQL skills with help of this community.

I spoke too soon! Although I am making progress I am still getting inacurate data returned and I think it may be because some contacts belong to 2 or more applications regardless of whether it is the 2 specified in the query. I have altered the query to select distinct rows as follows but to no avail - any further input would be much appreciated:


SELECT DISTINCT TOP 100 PERCENT COUNT(*) AS Expr1, C.Email, C.FirstName, C.LastName, C.Country
FROM         dbo.Contacts C INNER JOIN
                          (SELECT DISTINCT CA1.ContactId
                            FROM          Contact_Application CA1
                            WHERE      CA1.ApplicationId IN ('0c1eeb41-e4ce-450d-a925-685901442005', '57646833-c746-4ad7-a0c4-602e3badd8a9')
                            GROUP BY CA1.ContactID
                            HAVING      COUNT(*) = 2) D ON C.ContactId = D.ContactId INNER JOIN
                      dbo.Contact_Application CA ON C.ContactId = CA.ContactId RIGHT OUTER JOIN
                      dbo.Applications A ON CA.ApplicationId = A.ApplicationId
GROUP BY C.Email, C.FirstName, C.LastName, C.Country

www.creativenrg.co.uk

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-04 : 05:13:03

It is difficult to tell what you want without some sort of test harness and expected results. The outer join means your query returns all the applications. If you only want to see the contacts for the two applications, even if the contact has more applications, try the following. If this is not what you want post DDL, test data and expected results.

SELECT *
FROM Contacts C
    JOIN (
            SELECT CA1.ContactId
            FROM Contact_Application CA1
            WHERE CA1.ApplicationId IN ('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')
            GROUP BY CA1.ContactID
            HAVING COUNT(*) = 2
        ) D
        ON C.ContactID = D.ContactID
    JOIN Contact_Application CA
        ON C.ContactID = CA.ContactID
            AND CA.ApplicationId IN ('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')
    RIGHT JOIN Applications A
        ON CA.ApplicationId = A.ApplicationId

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-04 : 09:35:48

I appreciate the help so far but am still not getting the desired results so here is what I believe you need and what I should have posted from the start:


/****** Object:  Table [dbo].[Applications]    Script Date: 04/07/2007 13:06:24 ******/
CREATE TABLE [dbo].[Applications] (
	[ApplicationId]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[Description] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[Type] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
	[CreatedBy] [uniqueidentifier] NOT NULL ,
	[CreatedDate] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Contact_Application]    Script Date: 04/07/2007 13:06:24 ******/
CREATE TABLE [dbo].[Contact_Application] (
	[ContactId] [uniqueidentifier] NOT NULL ,
	[ApplicationId] [uniqueidentifier] NOT NULL ,
	[UserId] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[Username] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[RegistrationDate] [smalldatetime] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Contacts]    Script Date: 04/07/2007 13:06:24 ******/
CREATE TABLE [dbo].[Contacts] (
	[ContactId]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[FirstName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[LastName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Address1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[Address2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[Address3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[City] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[County] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Postcode] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[Country] [uniqueidentifier] NULL ,
	[Telephone] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
	[Email] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Gender] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[Subscribed] [bit] NOT NULL ,
	[CreatedDate] [smalldatetime] NOT NULL ,
	[CreatedBy] [uniqueidentifier] NOT NULL ,
	[UpdatedDate] [smalldatetime] NULL ,
	[UpdatedBy] [uniqueidentifier] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Applications] WITH NOCHECK ADD 
	CONSTRAINT [PK_Applications] PRIMARY KEY  CLUSTERED 
	(
		[ApplicationId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Contacts] WITH NOCHECK ADD 
	CONSTRAINT [PK_Contacts] PRIMARY KEY  CLUSTERED 
	(
		[ContactId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Applications] ADD 
	CONSTRAINT [DF_Applications_ApplicationId] DEFAULT (newid()) FOR [ApplicationId],
	CONSTRAINT [DF_Applications_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[Contacts] ADD 
	CONSTRAINT [DF_Contacts_ContactId] DEFAULT (newid()) FOR [ContactId],
	CONSTRAINT [DF_Contacts_Subscribed] DEFAULT (1) FOR [Subscribed],
	CONSTRAINT [DF_Contacts_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate],
	CONSTRAINT [DF_Contacts_UpdatedDate] DEFAULT (getdate()) FOR [UpdatedDate],
	CONSTRAINT [IX_Contacts] UNIQUE  NONCLUSTERED 
	(
		[Email]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Applications] ADD 
	CONSTRAINT [FK_Applications_aspnet_Users] FOREIGN KEY 
	(
		[CreatedBy]
	) REFERENCES [dbo].[aspnet_Users] (
		[UserId]
	)
GO

ALTER TABLE [dbo].[Contact_Application] ADD 
	CONSTRAINT [FK_Contact_Application_Applications] FOREIGN KEY 
	(
		[ApplicationId]
	) REFERENCES [dbo].[Applications] (
		[ApplicationId]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_Contact_Application_Contacts] FOREIGN KEY 
	(
		[ContactId]
	) REFERENCES [dbo].[Contacts] (
		[ContactId]
	) ON DELETE CASCADE 
GO

ALTER TABLE [dbo].[Contacts] ADD 
	CONSTRAINT [FK_Contacts_aspnet_Users] FOREIGN KEY 
	(
		[CreatedBy]
	) REFERENCES [dbo].[aspnet_Users] (
		[UserId]
	),
	CONSTRAINT [FK_Contacts_aspnet_Users1] FOREIGN KEY 
	(
		[UpdatedBy]
	) REFERENCES [dbo].[aspnet_Users] (
		[UserId]
	),
	CONSTRAINT [FK_Contacts_Countries] FOREIGN KEY 
	(
		[Country]
	) REFERENCES [dbo].[Countries] (
		[CountryId]
	)
GO

What I need to achieve is the ability to pull out Contacts that belong for example to 2 specific applications, not 1 or the other but both and only the contacts that belong to both. It could be more than 2 but 2 is sufficient for my example.

Thanks

www.creativenrg.co.uk

thenearfuture
Starting Member

35 Posts

Posted - 2007-07-04 : 11:06:55

How about something like this?

 

SELECT 	Contacts.FirstName, 
		Contacts.LastName, 
		Contacts.Country,
		Contacts.Email, 
		Applications.Name as Application1, 
		ContactsApp2.ApplicationName as Application2

FROM         	Contacts INNER JOIN
                      	Contact_Application ON Contacts.ContactId = Contact_Application.ContactId RIGHT OUTER JOIN
                      	Applications ON Contact_Application.ApplicationId = Applications.ApplicationId INNER JOIN
		
			(SELECT     	Contacts.ContactId as ContactId, Applications.Name AS ApplicationName
			FROM         	Contacts INNER JOIN
                      				Contact_Application ON Contacts.ContactId = Contact_Application.ContactId RIGHT OUTER JOIN
                      				Applications ON Contact_Application.ApplicationId = Applications.ApplicationId
			WHERE     	(Applications.ApplicationId = 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')) ContactsApp2

		on ContactsApp2.ContactId = Contacts.ContactId 

WHERE 	Applications.ApplicationId = '0c1eeb41-e4ce-450d-a925-685901442005'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-04 : 11:39:37

[code]SELECT c.FirstName,
c.LastName,
c.Country,
c.Email,
p.App1,
p.App2
FROM Contacts AS c
INNER JOIN (
SELECT ca.ContactID,
MIN(a.Name) AS App1,
MAX(a.Name) AS App2
FROM Contact_Application AS ca
INNER JOIN Applications AS a ON a.ApplicationID = ca.ApplicationID
WHERE a.ApplicationID IN ('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')
GROUP BY ca.ContactID
HAVING MIN(CASE WHEN a.ApplicationID = '0c1eeb41-e4ce-450d-a925-685901442005' THEN 0 ELSE 1 END) = 0
AND MIN(CASE WHEN a.ApplicationID = 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4' THEN 0 ELSE 1 END) = 0
) AS p ON p.ContactID = c.ContactID[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-04 : 11:40:30

[code]SELECT c.FirstName,
c.LastName,
c.Country,
c.Email,
p.App1,
p.App2
FROM Contacts AS c
INNER JOIN (
SELECT ca.ContactID,
MIN(a.Name) AS App1,
MAX(a.Name) AS App2
FROM Contact_Application AS ca
INNER JOIN Applications AS a ON a.ApplicationID = ca.ApplicationID
WHERE a.ApplicationID IN ('0c1eeb41-e4ce-450d-a925-685901442005', 'caa27a9a-eeaa-4e94-9ae5-6a98e1c5a6d4')
GROUP BY ca.ContactID
HAVING COUNT(DISTINCT a.ApplicationID) = 2
) AS p ON p.ContactID = c.ContactID[/code]

Peter Larsson
Helsingborg, Sweden

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-11 : 12:04:37

Thanks for all of the help on this guys, my final query which seems to give me the expected results is as follows:


SELECT     TOP 100 PERCENT ca.ContactID, c.FirstName, c.LastName, c.Email, c.Country
FROM         dbo.Contact_Application ca LEFT OUTER JOIN
                      dbo.Contacts c ON ca.ContactID = c.ContactId
WHERE     (ca.ApplicationId = '1') OR
                      (ca.ApplicationId = '3')
GROUP BY ca.ContactID, c.FirstName, c.LastName, c.Email, c.Country
HAVING      (COUNT(ca.ContactID) > 1)
ORDER BY c.LastName

Please note that after some advice I was given I changed the data type for ApplicationId to int rather than unqiqueidentifier.

www.creativenrg.co.uk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-11 : 13:18:29

This goes against everything you said earlier that a user could have several entries for same application...
See the last three previous replies...

07/04/2007 : 11:06:55
07/04/2007 : 11:39:37
07/04/2007 : 11:40:30

Peter Larsson
Helsingborg, Sweden

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-11 : 16:14:27

quote:
Originally posted by Peso

This goes against everything you said earlier that a user could have several entries for same application...
See the last three previous replies...

07/04/2007 : 11:06:55
07/04/2007 : 11:39:37
07/04/2007 : 11:40:30

Peter Larsson
Helsingborg, Sweden

Hi, I have just looked back over my previous posts to see if that was the impression I gave. A user can have several entries in the table but not for the same application however even if they did my understanding of the query I posted would still work unless I am not understanding it correctly.

My understanding of the query is that it looks for contacts that belong to application a or b. If the count is higher than 1 then the user must belong to both and so is therefore returned in the results?

www.creativenrg.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources