CODEWITHSUNDEEP
sqlsql-servert-sql
Rajan
Rajan

Reputation: 1

Trickly transact SQL needed please: going round in circles

I have data like the following

Ftitle               Fvalue                         Freference
------               ----------                     ---------
filename             file1.java                     123
version              2                              123
cvstree              branch1                        123

filename             file2.java                     345
version              4                              345
cvstree              branch2                        345

filename             file1.java                     4556
version              3                              4556
cvstree              branch1                        4556


filename             file3.java                     4312
version              77                             4312
cvstree              branch2                        4312

filename             file1.java                     5616
version              1                              5616
cvstree              branch3                        5616

I have given a blank line above between some rows for easy readability. For various reasons, the table structure cannot be changed and the table has tons and tons of data.

Now, what i have is just the Fvalue for example file1.java . i would like to know if i can use a TSQL statement so that, in one single query, i get, for example all the distinct Freference values where the branch matches what i specify.

So, for example, if i give a query where i want matches for file1.java for branch1, then i just want the SQL to return me "Freference" 4556 and 123 .

Is this doable without looping through all "Freference" values for file.java and then further filtering it where Fvalue is branch1? This kind of loop becomes very slow.

Upvotes: 0

Views: 135

Answers (5)

gbn
gbn

Reputation: 432431

SELECT --DISTINCT intersect removes duplicates
   Freference
FROM
   MyTable M1
WHERE
   M1.Fvalue = 'file1.java'
INTERSECT
SELECT --DISTINCT intersect removes duplicates
   Freference
FROM
   MyTable M2
WHERE
   M2.Fvalue = 'branch1'

OR

SELECT DISTINCT
   M.Freference
FROM
   MyTable M
WHERE
   M.Fvalue = 'file1.java'
   AND
   EXISTS (SELECT * FROM 
            MyTable M2
         WHERE
           M2.Fvalue = 'branch1'
           AND
           M.Freference = M2.Freference)

Upvotes: 3

Rajan
Rajan

Reputation: 1

is Freference unique to each triplet? in other words, could any other set of rows have 4312 as Freference?

Yes, Freference is unique to each triplet. Sorry if i didn't mention that before. I am trying out the various ideas here in the meantime

Upvotes: 0

Shawn
Shawn

Reputation: 19813

You just want to join the table on itself, it's pretty easy.

Edit: How about this?

select a.Freference 

from table a inner join table b
        on a.Freference = b.Freference
        and a.Ftitle = 'filename'
        and b.Ftitle = 'cvstree'
where 
    a.Fvalue ='file1.java'
    and b.Fvalue = 'branch1'

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

INTERSECT is an option
But a direct join has better support across DBMS.

select distinct a.freference
from tbl a
inner join tbl b on a.freference = b.freference and b.fvalue = 'branch1'
where a.fvalue = 'file1.java'

Upvotes: 3

John K.
John K.

Reputation: 5474

select * 
from theTable 
where Ftitle = 'file1.java' 
and Fvalue = 'branch1'

Upvotes: 1

Related Questions