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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-21 : 09:36:35
|
| Amit writes "I have two tables tblProfile and tblMatch. tblProfile stores the profiles of the users and have fields like proID, proScreenName, prosex, Proage, proHeight etc. tblMatch stores their match settings and have fields like mchID, mchScreenname,MchSex, mchAge, Mchheight etc.Now i want ot write a Stored Procedure which takes all the values from tblProfile and all the values from tblMatch and then match each record one by one e.g. for user01, it will take its match settings from tblMatch and all records from tblProfile and match say MchSex, mchAge,MchHeight with all proSex,proAge, Proheight from tblProfile and return records which have one or more values similar.Can u suggest me how to write a stored procedure for such a situation." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-21 : 09:49:29
|
| Simply JOIN them together column-for-column, you can affect the entire set with one operation instead of going row-by-row:SELECT * FROM tblMatch M INNER JOIN tblProfile PON (M.mchID=P.proID AND M.mchScreenName=P.proScreenName AND M.mchSex=P.proSex)You simply include as many of the matching columns in the JOIN clause as you wish to match.Two recommendations I'd like to make. One is to GET RID OF THE PREFIXES ON YOUR COLUMN NAMES! They are the same attributes, they have the same information, they should have the same names. All the prefixes do is make code more difficult to read and confuse what is otherwise a very simple query. Get rid of the tbl_ prefixes on your table names as well.Second, why not keep this data in one table? If you need to match columns between them, you essentially have 2 tables of the same data. If you need to maintain some kind of difference between them, keep them in one table and add a column for an attribute like "match" or "profile" for each row. |
 |
|
|
|
|
|
|
|
stored procedures fetching records from multiple tables