Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Looking to use a JOIN instead of IN in query

Posted on 2008-10-15
9
Medium Priority
?
133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
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 2000 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 to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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