Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

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

0
nkewney
Asked:
nkewney
  • 5
  • 4
1 Solution
 
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
momi_sabagCommented:
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now