4 Replies - 2308 Views - Last Post: 18 September 2012 - 05:27 AM

#1 saizburg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 22-November 10

joining table in sql sever 2005

Posted 17 September 2012 - 03:50 AM

select 
ltrim(rtrim(lastname) +','+
rtrim(firstname) +' '+
rtrim(middlename)) as patient_name,
rtrim(roomnumber) as room_number,
(roomtype) as room_type,
(date_admit) as date_of_admission,
(bednumber) as number_of_bed


from
tblconfine e
inner join tblpatient p on p.rid= e.patient_id
inner join tblroom r on r.rid= e.room_id



declare
@room_id as bigint,@patient_id as bigint

set @room_id= (select rid from tblroom
where roomnumber ='301');

set @patient_id= (select rid from tblpatient
where lastname ='cornista' and firstname='johnritz');

insert into tblconfine (patient_id,room_id)
values (@patient_id,@room_id)





i have 3 tables the tblpatient,tblroom and tblconfine. i can't combine the information in tblpatient and tblroom.
tblpatient fields are rid,firstname,middlename,lastname,contactnumber,e-mail,address,date admitted, while tblroom fields are rid,room number,room type,number of beds,firstname,middlename,lastname,

the tblconfine fields are patient_id,room_id,rid,date_admitted,number_of_bed,address,contact_number,name_of_patient.

if i run this code in sql sever 2005.. there are some columns with null value..

the output must be the name of patient,dateadmitted,room number,number of bed, room type,adress and contact number

This post has been edited by saizburg: 17 September 2012 - 04:02 AM


Is This A Good Question/Topic? 0
  • +

Replies To: joining table in sql sever 2005

#2 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,006
  • Joined: 12-January 10

Re: joining table in sql sever 2005

Posted 17 September 2012 - 06:20 AM

what do you mean you cant combine the tables?
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: joining table in sql sever 2005

Posted 17 September 2012 - 07:29 AM

patient
	rid -- what is rid??  The patient id?
	firstname
	middlename
	lastname
	contactnumber
	e-mail
	address
	date_admitted

room
	rid
	room number
	room type
	number_of_beds
	firstname -- why
	middlename -- why
	lastname -- why

confine -- interesting name.  is this a metal hospital
	patient_id
	room_id
	rid -- what is this?
	date_admitted -- same as patient.date_admitted?
	number_of_bed -- this could make sense
	address -- same as patient.address?
	contact_number -- same as patient.contactnumber?
	name_of_patient -- from patient



Simply, your tblconfine should have only two of three columns, There rest are pointless and bad form. The two required would be patient_id and room_id. The field number_of_bed also makes sense.

When you do your select, always name the tables providing the fields:
select 
		ltrim(rtrim(p.lastname) +','+
			rtrim(p.firstname) +' '+
			rtrim(p.middlename)) as patient_name,
		r.roomnumber as room_number,
		r.roomtype as room_type,
		p.date_admit as date_of_admission,
		e.bednumber as number_of_bed
	from tblconfine e
		inner join tblpatient p 
			on p.rid= e.patient_id
		inner join tblroom r
			on r.rid= e.room_id



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#4 saizburg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 22-November 10

Re: joining table in sql sever 2005

Posted 17 September 2012 - 09:18 PM

thank you but how can i show the output for all the patient icluding the fields of name,date of admission,number of beds ,room number and room type in one execute only?
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: joining table in sql sever 2005

Posted 18 September 2012 - 05:27 AM

View Postsaizburg, on 18 September 2012 - 12:18 AM, said:

how can i show the output for all the patient


select * from tblpatient


View Postsaizburg, on 18 September 2012 - 12:18 AM, said:

icluding the fields of name,date of admission,number of beds ,room number and room type in one execute only?


If you want all the records from one table, even if records don't exist in another table, you want an outer join.

select * 
	from tblpatient p -- all patients
		left outer join tblconfine e -- join on confine if there is a match
			on p.rid = e.patient_id
		left outer join tblroom r -- bring in room if there is a confine record
			on r.rid = e.room_id


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1