intersect

Question

Post reply

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.

i j k

1 4 2

1 6 2

2 4 2

2 6 2

3 4 2

3 5 2

______________________________________________________________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: 286562 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.

i j k

1 4 2

1 6 2

2 4 2

2 6 2

3 4 2

3 5 2

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: 44461 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.

i j k

1 4 2

1 6 2

2 4 2

2 6 2

3 4 2

3 5 2

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

karthik babu

Hall of Fame

Points: 3335

March 18, 2014 at 8:00 am

#1698021

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

karthik babu

Hall of Fame

Points: 3335

March 19, 2014 at 4:54 am

#1698307

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

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