intersect

Question

intersect

karthik babu

Hall of Fame

Points: 3335
More actions
March 18, 2014 at 6:43 am

#286824

i have the below table.. i need the intersection of j with group by of i column, which is 4. pls let me know the query to arrive at that.. thanks.
ijk
142
162
242
262
342
352
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

karthik babu (3/18/2014)
i have the below table.. i need the intersection of j with group by of i column, which is 4. pls let me know the query to arrive at that.. thanks.
ijk
142
162
242
262
342
352

How about if YOU put in some of the effort here? Start by posting this in a consumable format. You have been around here long enough to know what that means.

You might also check BOL for the title of your thread.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 2

Typically you would use INTERSECT to compare the results of 2 queries/tables (data sets) - can you share what you have come up with so far, and what your expected result should be?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

karthik babu (3/18/2014)
i have the below table.. i need the intersection of j with group by of i column, which is 4. pls let me know the query to arrive at that.. thanks.
ijk
142
162
242
262
342
352

Writing queries is easy. Interpreting requirements is almost always the hardest part. Sometimes, as in this case, it's impossible due to insufficient information. Please provide a sample data script and a proper explanation of what you are trying to do. A result set would help too.

"group by of i column, which is 4" - how is this derived?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik babu Hall of Fame Points: 3335 More actions · Answer 4

I have come up till this.. here I have hard coded the values of i. if this result is arrived dynamically or looping using any other logic it will be perfect!!

create table num (i int, j int, k int)

insert into num values (1,4,2),

(1,6,2),

(2,4,2),

(2,6,2),

(3,4,2),

(3,5,2)

select distinct j from num where i = 1

intersect

select distinct j from num where i = 2

intersect

select distinct j from num where i = 3

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 5

karthik babu (3/18/2014)
if this result is arrived dynamically or looping using any other logic it will be perfect!!

When is the assignment due? 😉

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

karthik babu Hall of Fame Points: 3335 More actions · Answer 6

as early as possible.. since its EOD today we have 12+ hrs 🙂

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

The values of i (1, 2 and 3) have a value of j which is common to all of them (4). Another way to view this: a value of j is associated with all values of i.

Is this what you are looking for?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

karthik babu (3/18/2014)
as early as possible.. since its EOD today we have 12+ hrs 🙂

How about pasting the assignment here so we don't have to guess what you're trying to do?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 9

I think that this one does the trick:

With DistinctPer as (

select distinct i, j from num),

NumOfJs as (

select count(*) as CountOfJs, j

from DistinctPer

group by j)

select j

from NumOfJs

where CountOfJs = (select count(distinct i) from num)

[/Code]

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

KS Hall of Fame Points: 3323 More actions · Answer 10

Will this help

;WITH Nums (i,j,k) AS

(

SELECT 1,4,2 UNION ALL

SELECT 1,6,2 UNION ALL

SELECT 2,4,2 UNION ALL

SELECT 2,6,2 UNION ALL

SELECT 3,4,2 UNION ALL

SELECT 3,5,2

)

SELECT B.j

FROM (SELECT COUNT(DISTINCT i) Cnt FROM Nums) AS A

JOIN (SELECT j,COUNT(DISTINCT i) Cnt FROM Nums

GROUP BY j) AS B ON A.Cnt = B.Cnt

karthik babu Hall of Fame Points: 3335 More actions · Answer 11

Yes Adi!! it was perfect!! thank you for your worthy contribution..

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂