Help with a weird query

Question

Post reply

Help with a weird query

chileu17

Hall of Fame

Points: 3093
More actions
June 9, 2010 at 7:35 pm

#225469

Hi everyone,
Today I came across with a kinda tricky query. I was asked to retrieve codes that are related among them. I had to find them in a way which that it could be proved that they were related in triples, not couples but triples :doze: I just copied the basic example.
IdXIdYScVaP1P2
-----------------------------------------
7700019900014411e-12510065
9900017700014411e-12510042
7700017700034241e-1208912
7700037700014241e-12039100
9900017700034411e-1257341
7700039900014411e-1259085
And this would have to give me a result of this type:
77000199001770003
I came up with the following query but I don't think it is the best way to do it
select
h."indexQuery" as edge1A, h."indexSubj" as edge1B,
h2."indexQuery" as edge2A, h2."indexSubj" as edge2B
,
h3."indexQuery" as edge3A, h3."indexSubj" as edge3B
from hit h , hit h2 , hit h3
where
(
((h."indexSubj" = h2."indexQuery" or h2."indexQuery" = h."indexSubj")
or (h2."indexSubj" = h."indexQuery" or h."indexQuery" = h2."indexSubj"))
AND
((h."indexSubj" = h3."indexQuery" or h3."indexQuery" = h."indexSubj")
or (h3."indexSubj" = h."indexQuery" or h."indexQuery" = h3."indexSubj"))
AND
((h2."indexSubj" = h3."indexQuery" or h3."indexQuery" = h2."indexSubj")
or (h3."indexSubj" = h2."indexQuery" or h2."indexQuery" = h3."indexSubj"))
)
OR
(
((h."indexSubj" = h3."indexQuery" or h3."indexQuery" = h."indexSubj")
or (h3."indexSubj" = h."indexQuery" or h."indexQuery" = h3."indexSubj"))
AND
((h."indexSubj" = h2."indexQuery" or h2."indexQuery" = h."indexSubj")
or (h2."indexSubj" = h."indexQuery" or h."indexQuery" = h2."indexSubj"))
AND
((h2."indexSubj" = h3."indexQuery" or h3."indexQuery" = h2."indexSubj")
or (h3."indexSubj" = h2."indexQuery" or h2."indexQuery" = h3."indexSubj"))
)
OR
(
((h."indexSubj" = h3."indexQuery" or h3."indexQuery" = h."indexSubj")
or (h3."indexSubj" = h."indexQuery" or h."indexQuery" = h3."indexSubj"))
AND
((h2."indexSubj" = h3."indexQuery" or h3."indexQuery" = h2."indexSubj")
or (h3."indexSubj" = h2."indexQuery" or h2."indexQuery" = h3."indexSubj"))
AND
((h."indexSubj" = h2."indexQuery" or h2."indexQuery" = h."indexSubj")
or (h2."indexSubj" = h."indexQuery" or h."indexQuery" = h2."indexSubj"))
)
OR
(
((h2."indexSubj" = h3."indexQuery" or h3."indexQuery" = h2."indexSubj")
or (h3."indexSubj" = h2."indexQuery" or h2."indexQuery" = h3."indexSubj"))
AND
((h."indexSubj" = h3."indexQuery" or h3."indexQuery" = h."indexSubj")
or (h3."indexSubj" = h."indexQuery" or h."indexQuery" = h3."indexSubj"))
AND
((h."indexSubj" = h2."indexQuery" or h2."indexQuery" = h."indexSubj")
or (h2."indexSubj" = h."indexQuery" or h."indexQuery" = h2."indexSubj"))
)
OR
(
((h2."indexSubj" = h3."indexQuery" or h3."indexQuery" = h2."indexSubj")
or (h3."indexSubj" = h2."indexQuery" or h2."indexQuery" = h3."indexSubj"))
AND
((h."indexSubj" = h2."indexQuery" or h2."indexQuery" = h."indexSubj")
or (h2."indexSubj" = h."indexQuery" or h."indexQuery" = h2."indexSubj"))
AND
((h."indexSubj" = h3."indexQuery" or h3."indexQuery" = h."indexSubj")
or (h3."indexSubj" = h."indexQuery" or h."indexQuery" = h3."indexSubj"))
)
OR
(
((h."indexSubj" = h2."indexQuery" or h2."indexQuery" = h."indexSubj")
or (h2."indexSubj" = h."indexQuery" or h."indexQuery" = h2."indexSubj"))
AND
((h2."indexSubj" = h3."indexQuery" or h3."indexQuery" = h2."indexSubj")
or (h3."indexSubj" = h2."indexQuery" or h2."indexQuery" = h3."indexSubj"))
AND
((h."indexSubj" = h3."indexQuery" or h3."indexQuery" = h."indexSubj")
or (h3."indexSubj" = h."indexQuery" or h."indexQuery" = h3."indexSubj"))
)
Any kind of suggestions are more than welcome! Thanks in advanced.

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

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

platipino SSC Enthusiast Points: 152 More actions · Answer 1

This might work better

This is an easy table to show how to solve the issue :

Create TableCustomer

(

IdintNot NullIdentity,

TitlenVarChar(80)Not Null,

ConstraintIX_Customer_TitleUniqueNonClustered(Title),

ConstraintPK_CustomerPrimary Key(Id)

)

And here is the my solution :

SelectCustomer2.[Name],

Customer2.Family,

Result.Kount

From(

SelectCustomer.[Name]As [Name],

Customer.FamilyAs Family,

Count(*)As Kount

FromCustomer

Group ByCustomer.[Name],

Customer.Family

)As Result

Inner Join

CustomerAs Customer2

OnResult.[Name]= Customer2.[Name]And

Result.Family= customer2.Family

Group ByCustomer2.[Name],

Customer2.Family,

Result.kOunt

HavingkOunt >= 3

shield_21 Ten Centuries Points: 1033 More actions · Answer 2

Hi there,

Does it mean there is at least 2 occurrences of same number in IdY? Ex: IdX=770001.. 770001 appears twice in IdY.. Sample code below:

CREATE TABLE test

(

IdX INT,

IdY INT,

Sc INT,

Va VARCHAR(50),

P1 INT,

P2 INT

)

INSERT INTO test

SELECT 770001, 990001, 441, '1e-125', 100, 65 UNION ALL

SELECT 990001, 770001, 441, '1e-125', 100, 42 UNION ALL

SELECT 770001, 770003, 424, '1e-120', 89, 12 UNION ALL

SELECT 770003, 770001, 424, '1e-120', 39, 100 UNION ALL

SELECT 990001, 770003, 441, '1e-125', 73, 41 UNION ALL

SELECT 770003, 990001, 441, '1e-125', 90, 85

;WITH cte AS

(

SELECT *, COUNT(IdY) OVER (PARTITION BY IdX) AS IdCount

FROM test

)

SELECT DISTINCT IdX FROM cte WHERE IdCount >= 2

DROP TABLE test

Cheers,:-)

shield_21

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

DROP TABLE #test

CREATE TABLE #test

(

IdX INT,

IdY INT,

Sc INT,

Va VARCHAR(50),

P1 INT,

P2 INT

)

INSERT INTO #test

SELECT 770001, 990001, 441, '1e-125', 100, 65 UNION ALL

SELECT 990001, 770001, 441, '1e-125', 100, 42 UNION ALL

SELECT 770001, 770003, 424, '1e-120', 89, 12 UNION ALL

SELECT 770003, 770001, 424, '1e-120', 39, 100 UNION ALL

SELECT 990001, 770003, 441, '1e-125', 73, 41 UNION ALL

SELECT 770003, 990001, 441, '1e-125', 90, 85

SELECT BothColumns, Instances = COUNT(*)

FROM (

SELECT BothColumns = IdX

FROM #test

UNION ALL

SELECT IdY

FROM #test

) d

GROUP BY BothColumns

HAVING COUNT(*) > 2

ORDER BY BothColumns

^{“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

Matt Mitchell SSC Enthusiast Points: 152 More actions · Answer 4

Your example query complicates things a lot more than is needed!

First of all, you need to work out what characterises the output that you want:

3 nodes, where:

- node1 is parent of node2

- node1 is parent of node 3

- and either:

- - node2 is parent of node3

- or

- - node3 is parent of node2

as all the nodes are taken from the same set, the various permutations of these that you show are all equivalent (try substituing "apple" for node1, node2 and node3 above, and see if you can spot the difference!)

Given that, you can do it all with two self-joins:

CREATE TABLE edgelist (

node INTEGER NOT NULL PRIMARY KEY,

child INTEGER NOT NULL

-- add your node data bits here...

-- somefield1 NVARCHAR(6),

-- etc...

)

SELECT

n1.node,

n2.node,

n3.node

FROM

edgelist n1 JOIN edgelist n2

ON n1.child=n2.node

JOIN edgelist N3 ON

n2.child=n3.node

GO

Is it me, or does this look like a homework question?

(edited to correct "nodelist" to "edgelist")

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

lart.expert (6/11/2010)
Is it me, or does this look like a homework question?

Unlikely as OP has been a forum member for over two years.

^{“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

Matt Mitchell SSC Enthusiast Points: 152 More actions · Answer 6

Chris Morris-439714 (6/11/2010)
lart.expert (6/11/2010)
Is it me, or does this look like a homework question?
Unlikely as OP has been a forum member for over two years.

In that case, I apologise. It's just that the problem statement was rather vague - it sounds like an exercise in spotting triangle nets in a graph.

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 7

Chris Morris-439714 (6/11/2010)
lart.expert (6/11/2010)
Is it me, or does this look like a homework question?
Unlikely as OP has been a forum member for over two years.

Heh... maybe a 4 year school? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Mitchell SSC Enthusiast Points: 152 More actions · Answer 8

Gets my vote!

The question does highlight thebenegits of stating the problem in English though.

bappzamd786 Valued Member Points: 52 More actions · Answer 9

Now this is a weird query, but I really need some help. I am a first year undergrad doing BSc Biomedical Science at pompey. Now I want to do medicine. I have done International Baccalaureate before but I did not study chemistry. Is it possible to do a chemistry AS or full A level now along with my biomedical sci. course and in my second year apply for a 5 yrs. MBBS?

Can this be done? If I also do BMAT and UKCAT alongside, would that help? Please help if anyone has done this before or know if this works. Also great if you could add any other tips.

Thanks all!

BD Hosting

Matt Mitchell SSC Enthusiast Points: 152 More actions · Answer 10

Matt Mitchell (6/12/2010)
Gets my vote!
The question does highlight thebenegits of stating the problem in English though.

and also the benefits of reading what you've put before posting!

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

Matt Mitchell (6/13/2010)
Matt Mitchell (6/12/2010)
Gets my vote!
The question does highlight thebenegits of stating the problem in English though.
and also the benefits of reading what you've put before posting!

Lol!

Shame the OP's taken off, this one had the potential to get interesting.

^{“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

chileu17 Hall of Fame Points: 3093 More actions · Answer 12

Hi everyone, I am sorry I couldn't keep up with the forum lately, but work is just killing me.

Thank you all for the replies, I really appreciate your time to answer. 🙂

And Matt, what if I were a student? what is the problem with checking with the community? or people must have certain qualifications to participate in here? I thought forums were for people to discuss ideas and help each other, but I guess just some people are born knowing everything 😉

So about the query, what really wanted was the transitive closure of the relation. That is obviously not possible with sql-like languanges due to their first order logic expressive power. So I wanted to get as close as possible by making combinations and aggregations. In a way, it is kinda what Matt said, I thought if I can't get transitive closure, maybe I can find closed graphs out of my data. The problem is that I will get as many resulting records as the number of combinations of the connecting nodes. For example in the example I posted I would get:

770001 99001 770003 abc

99001 770003 770001 bca

770003 770001 99001 cab

770001 770003 99001 acb

99001 770001 770003 bac

770003 99001 770001 cba

And what I want is 99001 770003 770001 I am sorry I can describe my data, but I am sure you guys will understand. I thought there was a way like to sort the records using the data they have inside of them? Oh well, any ideas and comments are appreciated.

Thanks in advanced and sorry again I couldn't keep up with the forum, but I will try to stay more active.