query for inner join of table 4.

Question

hi.... I'm facing the problem of inner join of table 4 following is query given plz see & give me solution

select INSURED.FNAME + ' ' + INSURED.LNAME AS MNAME
,INSURED.MEMBCODE as MEMBERCODE
,INSURED.POLICYNO AS POLICYNO
,INSURED.POLICYFRMDATE AS POLICYFROMDATE
,INSURED.POLICYTODATE AS POLICYTODATE
, MEMBERSHIP.MRKEXTNAME AS MARKETINGEXECUTIVE
,MEMBERSHIP.EMPLOYEECOUNT AS EMPLOYEECOUNT
,INSURED.CLAIMID AS CLAIMID
,POLICY.POLICYTYPE
,POLICY.COVAMTHOSPITAL as SUMINSURED
,ORGANIZATION.ORGANIZATIONNAME  
from ((INSURED 
inner join MEMBERSHIP on MEMBERSHIP.MEMBERSHIPID=INSURED.MEMBERSHIPID) 
inner join POLICY on MEMBERSHIP.POLICYNAME=POLICY.POLICYNAME) 
inner join ORGANIZATION on ORGANIZATION.ORGANIZATIONID=MEMBERSHIP.ORGANIZATIONID 
WHERE INSUREDID=427 

Answer 1

Get rid of the brackets around your inner join statements.

Answer 2

After making it a bit more readable there doesn't seem to be much wrong. I removed the brackets as they aren't needed. I would also put the INNER JOINS the other way round as its normal to put them in this order.

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

So it should look something like

select INSURED.FNAME + ' ' + INSURED.LNAME AS MNAME,
INSURED.MEMBCODE as MEMBERCODE,
INSURED.POLICYNO AS POLICYNO,
INSURED.POLICYFRMDATE AS POLICYFROMDATE,
INSURED.POLICYTODATE AS POLICYTODATE,
MEMBERSHIP.MRKEXTNAME AS MARKETINGEXECUTIVE,
MEMBERSHIP.EMPLOYEECOUNT AS EMPLOYEECOUNT,
INSURED.CLAIMID AS CLAIMID,
POLICY.POLICYTYPE,
POLICY.COVAMTHOSPITAL as SUMINSURED,
ORGANIZATION.ORGANIZATIONNAME
FROM INSURED
INNER JOIN MEMBERSHIP on INSURED.MEMBERSHIPID=MEMBERSHIP.MEMBERSHIPID
INNER JOIN POLICY on POLICY.POLICYNAME=MEMBERSHIP.POLICYNAME
INNER JOIN ORGANIZATION on MEMBERSHIP.ORGANIZATIONID=ORGANIZATION.ORGANIZATIONID
WHERE INSUREDID=427

You need to tell us what the problem is. Does it return results different to what you are expecting or does it fail with an error.

You should also tell us which database you are using.

Also there appears to be something odd about your table structure. You would not normally store PolicyName in your Membership Table rather you should be linking with ID Fields of some kind rather than PolicyName Strings...Something Like

INNER JOIN POLICY on POLICY.POLICYID=MEMBERSHIP.POLICYID

You got it right for with OrganizationID and MEMBERSHIPID

In short you need to tell us more about it.

Answer 3

Replace INNER JOIN with LEFT OUTER JOIN.