July 18, 2012 at 5:03 am
Hi,
I have to tables Table Candidates, Students and Fields are as follow:
Structure of Candidate table is
ID, Name , F'Name, Address, Village, City,District.
and Structure of Sudent Table is
ID, Name , F'Name, Address, Village, City,District, RollNo, RegID
now I have to write a in which RollNo and RegID should be inserted in Candidate table
and matching criteria is only Name, F'Name ,Address, Village,City,District.
I want result where all candidates from Candidate table should be displayed with the roll number and regisration number for the students who are in student table.
can any one help me.
July 18, 2012 at 5:24 am
Please follow the second link in my signature on posting code and data and I am sure you will get a more precise answer to your problem.
But I am guessing you want something like this
DECLARE @Candidate TABLE (ID INT, Name NVARCHAR(50), FName NVARCHAR(50), Address NVARCHAR(100), Village NVARCHAR(50), City NVARCHAR(50),District NVARCHAR(50))
DECLARE @Student TABLE (ID INT, Name NVARCHAR(50), FName NVARCHAR(50), Address NVARCHAR(100), Village NVARCHAR(50), City NVARCHAR(50),District NVARCHAR(50), RollNo INT, RegID INT)
INSERT INTO @Candidate VALUES (1,'smith','bob','123 Somestreet','Somevillage','Somecity','Somedistrict'),
(2,'smithalina','bobalina','123 Somestreet2','Somevillage2','Somecity2','Somedistrict2'),
(3,'bloggs','joe','123 JoesStreet','JoesVillage','JoesCity','JoesDistrict')
INSERT INTO @Student VALUES (2,'smith','bob','123 Somestreet','Somevillage','Somecity','Somedistrict',123,123),
(3,'smithalina','bobalina','123 Somestreet2','Somevillage2','Somecity2','Somedistrict2',1234,1234),
(1,'bloggs','joe','123 JoesStreet','JoesVillage','JoesCity','JoesDistrict',12345,12345)
SELECT * FROM @Candidate
SELECT * FROM @Student
SELECT
C.*,
S.RollNo,
S.RegID
FROM
@Candidate C
INNER JOIN
@Student S
ON
C.Name = S.Name
AND
C.FName = S.FName
AND
C.Address = S.Address
AND
C.Village = S.Village
AND
C.City = S.City
AND
C.District = S.District
July 18, 2012 at 10:51 pm
Hi,
Thank you for your help.. and I am littele bit Close to result.
I needed all records from left table
so my query is like:
SELECT
BI.*,
S.uid,
S.eid,
S.name_local,S.addr_careof_local
FROM
bilaspur_ssp..bilaspurssp BI
left JOIN
EIDMAP S
ON
BI.ApplicantName like N'%'+S.name_local+'%'
AND
S.addr_careof_local like N'%'+rtrim(ltrim(isnull(BI.HusbandsName,'')+ISNULL(BI.FathersName,'')))+'%'
and S.addr_locality_local like N'%'+BI.SubPostOffice+'%'
but I got just 50 matches.. which is not acceptable.. all recordes from bilaspur table comes form Right table.
Can help me to get more efective results
July 19, 2012 at 1:50 am
That is a totally different schema and set of data to what you have provided all ready.
Please post the correct DDL and the correct data along with your expected outcomes as based on the second link in my signature.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply