Looking to use a JOIN instead of IN in query

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

LVL 1
nkewneyAsked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
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
 
momi_sabagCommented:
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
 
nkewneyAuthor Commented:
Hi momi_sabag,

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

Thanks

Nick
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
momi_sabagCommented:
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
 
nkewneyAuthor Commented:
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
 
momi_sabagCommented:
post it with a subquery and i will modify it since i don't understand exactly what you need
0
 
nkewneyAuthor Commented:
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
 
nkewneyAuthor Commented:
I should point out that Original_ID in Phrases2_Suggest is a FK for Phrases.ID

Nick
0
 
nkewneyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.