Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Determine the No of columns matched in a query

Author  Topic 

syedtameem11
Starting Member

6 Posts
Posted - 2009-04-21 : 08:29:55
Hi All,

Can anybody help me in solving this problem. I have to determine the no of columns matched in a query and accordingly get the percentage depending upon the no of columns matched in that row. There will be nearly 10 inputs to the stored procedure.

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-21 : 08:30:43
We will have to see your code and store procedure definition.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

syedtameem11
Starting Member

6 Posts
Posted - 2009-04-25 : 06:39:11
Hi,

The stored procedure is as follows,

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,
Thanks
Syed Tameemuddin
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site