Solved

Looking to use a JOIN instead of IN in query

Posted on 2008-10-15
9
123 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
Question about DB Schema 27 56
Return 0 on SQL count 24 30
sql server service accounts 4 27
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

810 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