Solved

Looking to use a JOIN instead of IN in query

Posted on 2008-10-15
9
121 Views
Last Modified: 2010-04-21
Dear Experts,

I was hoping somebody could help me get my head around optimising a query?

My original query is below (code snippet)

I'm hoping to look up another table (Phrases2_Suggest_Votes) which holds:

Phrases2_Suggest.ID
Username

How can I omit any records from the query below where a record exists which matches the fields above without using IN?

Any help would be appreciated.

Thanks

Nick


SELECT     Phrases2.ID

FROM  Phrases2 

LEFT OUTER JOIN Phrases2_Suggest ON Phrases2.ID = Phrases2_Suggest.Original_ID

WHERE  (Phrases2.Translation IS NULL) AND (Phrases2.Phrase IS NOT NULL) AND (Phrases2.ID NOT IN
 

(SELECT     Phrases2_1.ID

FROM Phrases2 AS Phrases2_1 

LEFT OUTER JOIN Phrases2_Suggest AS Phrases2_Suggest_2 ON Phrases2_1.ID = Phrases2_Suggest_2.Original_ID 

INNER JOIN Phrases2_Suggest AS Phrases2_Suggest_1 ON Phrases2_Suggest_2.Username = @Username

WHERE (Phrases2_1.Translation IS NULL) AND (Phrases2_1.Phrase IS NOT NULL))) 

AND (Phrases2.Phrase NOT IN (SELECT     Profanity FROM          Profanities))

Open in new window

0
Comment
Question by:nkewney
  • 5
  • 4
9 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22719566
SELECT     Phrases2.ID
FROM  Phrases2
LEFT OUTER JOIN Phrases2_Suggest ON Phrases2.ID = Phrases2_Suggest.Original_ID
left outer join
(SELECT     Phrases2_1.ID
FROM Phrases2 AS Phrases2_1
LEFT OUTER JOIN Phrases2_Suggest AS Phrases2_Suggest_2 ON Phrases2_1.ID = Phrases2_Suggest_2.Original_ID
INNER JOIN Phrases2_Suggest AS Phrases2_Suggest_1 ON Phrases2_Suggest_2.Username = @Username
WHERE (Phrases2_1.Translation IS NULL) AND (Phrases2_1.Phrase IS NOT NULL)))
AND (Phrases2.Phrase NOT IN (SELECT     Profanity FROM          Profanities)) as t2 on Phrases2.id = t2.id

WHERE  (Phrases2.Translation IS NULL) AND (Phrases2.Phrase IS NOT NULL)
and t2.id is null
0
 
LVL 1

Author Comment

by:nkewney
ID: 22719620
Hi momi_sabag,

Thanks for the response but I don't see any mention of Phrases2_Suggest_Votes ...

Thanks

Nick
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22719689
ok
my bad
anyway, i'll give you the general guideline instead of the query
if you have 2 tables - a and b, and both of them has column ID,
and you want to select all the records from a which does not exists in b and not use a subquery, you should do

select *
from a left outer join b on a.id = b.id
where b.id is null

since a left outer join will put nulls for the columns of b when the record from a does not match a record from b
0
 
LVL 1

Author Comment

by:nkewney
ID: 22719903
Thanks for the tip. That's very useful.

Could I ask you to provide an example in this instance, since it's not two standard tables but a sub-table.

Many thanks

Nick
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22719944
post it with a subquery and i will modify it since i don't understand exactly what you need
0
 
LVL 1

Author Comment

by:nkewney
ID: 22720044
Thanks for this.

I'm getting a bit confused though.

I pasted create statements for the three tables below.

Basically, I'm trying to return all Phrases2.ID where @Username isn't either

1. In Phrases_Suggest
2. In Phrases_Suggest_Votes

Is there an easier way to achieve this?

Nick


USE [ST]

GO

/****** Object:  Table [dbo].[Phrases2]    Script Date: 10/15/2008 13:16:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Phrases2](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[Phrase] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

	[Translation] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

	[SystemVoted] [bit] NULL,

	[SystemVotedDate] [datetime] NULL,

	[BadCount] [int] NULL,

 CONSTRAINT [PK_Phrases_2] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]
 
 

CREATE TABLE [dbo].[Phrases2_Suggest](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[Original_ID] [int] NULL,

	[Username] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,

	[Translation] [nvarchar](500) COLLATE Latin1_General_CI_AS NULL,

	[Timestamp] [datetime] NULL,

	[Votes] [int] NULL,

 CONSTRAINT [PK_Phrases2_Suggest] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]
 

USE [ST]

GO

/****** Object:  Table [dbo].[Phrases2_Suggest_Votes]    Script Date: 10/15/2008 13:16:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Phrases2_Suggest_Votes](

	[ID] [int] NULL,

	[Username] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,

	[Timestamp] [datetime] NULL

) ON [PRIMARY]

Open in new window

0
 
LVL 1

Author Comment

by:nkewney
ID: 22720054
I should point out that Original_ID in Phrases2_Suggest is a FK for Phrases.ID

Nick
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22720071
Basically, I'm trying to return all Phrases2.ID where @Username isn't either

1. In Phrases_Suggest
2. In Phrases_Suggest_Votes

select *
from Phrases2 t1
left outer join Phrases_Suggest t2 on t1.id = t2.id
left outer join Phrases_Suggest_Votes t3 on t1.id = t3.id
where t2.id is null and t3.id is null
0
 
LVL 1

Author Closing Comment

by:nkewney
ID: 31506250
Great stuff. Thanks.

Here's my final solution

SELECT     ID, U1, U2
FROM         (SELECT     t1.ID, ISNULL(t2.Username, '') AS U1, ISNULL(t3.Username, '') AS U2
                       FROM          Phrases2 AS t1 LEFT OUTER JOIN
                                              Phrases2_Suggest AS t2 ON t1.ID = t2.Original_ID LEFT OUTER JOIN
                                              Phrases2_Suggest_Votes AS t3 ON t1.ID = t3.ID
                       WHERE      (t1.Translation IS NULL) AND (t1.Phrase IS NOT NULL)) AS tbl
WHERE     (NOT (U1 = @username)) AND (NOT (U2 = @username))
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now