CODEWITHSUNDEEP
sqlasp.net
Swati
Swati

Reputation: 61

Does i use sql function here instead of adding inner join to main query

Here are my tables

Table 1: Patient

id | pname  | plname |  cid
---+--------+--------+--------
1  | Savita |  rani  |  1
2  | Lavina |  kale  |  1
3  | fatima |  khole |  2

Table 2: DMRelation

id | pid | did |  mid
---+-----+-----+-------
1  |  1  |  1  |   1
2  |  1  |  1  |   2
3  |  1  |  2  |   0
4  |  2  |  2  |   2

Table 3: Doctor

id  |  dname
----+------------
1   |  Dr suhas
2   |  Dr Malini

Table 4: Medicine

id | drugname
---+----------
1  |   Drug1
2  |   Drug2

I want show patientname along with doctorname and medicine name whose cid= 1.Whether doctor give or not give medicine to patient record should come.

QUERY

select Patient.Pname,Doctor.dname,Medicine.Drugname
from DMRelation
inner join Patient
on Patient.id = DMRelation.pid
inner join Doctor
on Doctor.id = DMRelation.did
left join Medicine
on Medicine.id = DMRelation.mid
order by Patient.Pname,Doctor.dname,Medicine.Drugname

Working properly.Now I want add extra column to DMrelation.That is Inid.Added 3 more table

Table 2: DMRelation

id | pid | did |  mid   Inid     
---+-----+-----+-------
1  |  1  |  1  |   1    2
2  |  1  |  1  |   2    3
3  |  1  |  2  |   0    1
4  |  2  |  2  |   2    3

Table iR 
id   ino 
1    3
2    1
3    2

Table GR
id  gno
1   2
2   1
3   3

Table injection
id injName
1  inj1
2  inj2
3  inj3

I want to show name of inid that is nothing but name of injection.

Value of DMrelation.Inid = IR.Ino
fetch ir.id
ir.id= gr.gno 
fetch gr.id
gr.id = injection.id
from injection.id  fetch injection name

how to do that?

I tried here but failed

select Patient.Pname,Doctor.dname,Medicine.Drugname,injection.injectionname
from DMRelation
inner join Patient
on Patient.id = DMRelation.pid
inner join Doctor
on Doctor.id = DMRelation.did
inner join ir
on DMRelation.inid  =ir.ino
inner join gr
on ir.id=gr.gno
inner join injection
on gr.id = injection.id
left join Medicine
on Medicine.id = DMRelation.mid
where cid = 1
order by Patient.Pname,Doctor.dname,Medicine.Drugname

Output is :

Lavina  Dr Malini   Drug2   Inj3
Savita  Dr Malini   NULL    Inj1
Savita  Dr suhas    Drug1   Inj3
Savita  Dr suhas    Drug2   Inj2

Created function and modified query.

CREATE FUNCTION whichContinent2 
(@Inid int)
RETURNS varchar(30)
AS 
BEGIN 
declare @Injection varchar(30) 
select @Injection = injectionname from DMRelation
 inner join ir
on DMRelation.inid  =ir.ino
inner join gr
on ir.id=gr.gno
inner join injection
on gr.id = injection.id
where Inid= @Inid
return @Injection 
end

Query :

select Patient.Pname,Doctor.dname,Medicine.Drugname,dbo.WhichContinent2(DMRelation.inid)
from DMRelation
inner join Patient
on Patient.id = DMRelation.pid
inner join Doctor
on Doctor.id = DMRelation.did
left join Medicine
on Medicine.id = DMRelation.mid
where cid = 1
order by Patient.Pname,Doctor.dname,Medicine.Drugname

Upvotes: 0

Views: 73

Answers (1)

Dgan
Dgan

Reputation: 10285

Try Like this :

select distinct Patient.Pname,Doctor.dname,Medicine.Drugname,
injection.injName
from DMRelation
LEFT join Patient
on Patient.id = DMRelation.pid
LEFT join Doctor
on Doctor.id = DMRelation.did
LEFT join ir
on DMRelation.inid  =ir.ino
LEFT join gr
on ir.id=gr.gno
LEFT join injection
on gr.id = injection.id
left join Medicine
on Medicine.id = DMRelation.mid
where cid = 1
order by Patient.Pname,Doctor.dname,Medicine.Drugname

OUTPUT :

PNAME   DNAME   DRUGNAME    INJNAME

Lavina  Dr Malini   Drug2   inj2

Savita  Dr Malini   (null)  (null)

Savita  Dr suhas    Drug1   inj3

Savita  Dr suhas    Drug2   inj2

SQL FIDDLE

Upvotes: 1

Related Questions