April 26, 2009 at 10:57 pm
Hi All,
Does any one have an idea regarding how can we get the no of columns which are matched in a query and accordingly depending upon the matched columns get the percentage which is matched.
This is what i have written in stored procedure:
SELECT FirstName, LastName,Religon, Martialstatus,
BirthPlace, Nationality, Caste, SubCaste, MotherTongue,
Height, Complexion, Familytype, Familyvalues,
Profession, Income, Diet, Drink, Smoke,
Individuald, ISNULL(sum(relevance),0) as relevance
FROM (
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 6 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.BIRTHPLACE, 'Bengaluru')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 7 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.NATIONALITY,'India')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 6 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.RELIGON,'Hindu')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 7 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.CASTE,'Hindu:Niyogi')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 6 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.SUBCASTE,'Hindu:Niyogi')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 7 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.MOTHERTONGUE,'Kannada')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 6 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.HEIGHT,'5')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 7 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.COMPLEXION,'Very Fair')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 6 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.FAMILYTYPE,'Joint family')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 7 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(Individualdata.FAMILYVALUES,'Traditional')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 6 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(IndivInterest.PROFESSION,'Software')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 7 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(IndivInterest.INCOME,'Below Rs. 50,000')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 6 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(IndivInterest.DIET,'Vegetarian')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 7 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(IndivInterest.DRINK,'Yes')
UNION
SELECT Individualdata.FirstName as FirstName, Individualdata.LastName as LastName,
Individualdata.Religon as Religon, Individualdata.Martialstatus as Martialstatus,
Individualdata.BirthPlace as BirthPlace, Individualdata.Nationality as Nationality,
Individualdata.Caste as Caste, Individualdata.SubCaste as SubCaste,
Individualdata.MotherTongue as MotherTongue,
Individualdata.Height as Height, Individualdata.Complexion as Complexion,
Individualdata.Familytype as Familytype, Individualdata.Familyvalues as Familyvalues,
IndivInterest.Profession as Profession, IndivInterest.Income as Income,
IndivInterest.Diet as Diet, IndivInterest.Drink as Drink, IndivInterest.Smoke as Smoke,
Individualdata.Individuald as Individuald, 6 AS relevance
FROM Individualdata,IndivInterest
WHERE Individualdata.Individuald = IndivInterest.Individuald
AND FREETEXT(IndivInterest.SMOKE,'Yes')
) results
GROUP BY FirstName, LastName,Religon, Martialstatus,
BirthPlace, Nationality, Caste, SubCaste, MotherTongue,
Height, Complexion, Familytype, Familyvalues,
Profession, Income, Diet, Drink, Smoke,
Individuald
order by relevance desc
Here relevance i am assiging some values. it will sum and give the result. But the result is not accurate and If i gave all the relevance value as for ex.6 then it will return 6 only if multiple columns match. It will not sum and get the value
Any help will be kindly appreciated.
Thanking you,
Syed Tameemuddin
April 26, 2009 at 11:35 pm
Can you pls specify what may be your inputs and what should be your desired output?
"Don't limit your challenges, challenge your limits"
April 27, 2009 at 12:36 am
Try Count instead of SUM.
OR
Get the results seperately for each Union into another table
for example,
Insert into @t1
Select ...
and Get @@ROWCOUNT after each Insert.
I think this will be more simple...
April 27, 2009 at 1:52 am
Hi,
My Inputs at present in my stored procedure is hardcoded. If one query match in a single row then some percentage is assigned, if two querys match then different percentage should be shown and so on. On a particular row i want to know how many columns are getting matched and get the results with percentage matching in that row.
Any Help.
Thanks,
Syed Tameemuddin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply