Linking Info - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linking Info

Author

Topic

Basshole8117
Starting Member

8 Posts

Posted - 2009-03-25 : 15:20:48

I am trying to write a report linking mother to infant. does anyone have any code that they have come up with that does this. this is somthing new that had been asked of us and we have never done this before. we have one report for mothers with certain codes and infants with certain codes and now we have to try and link those mothers with infants. any help would be awesome. thanks

TRaymond
Starting Member

2 Posts

Posted - 2009-03-25 : 15:22:27

What is the table structure for the Mothers and Infants tables?
Is there a foreign key between the two?

TRaymond

Basshole8117
Starting Member

8 Posts

Posted - 2009-03-25 : 15:35:12

quote:
Originally posted by TRaymond

What is the table structure for the Mothers and Infants tables?

Not sure i understand what you mean by this...there not really tables like in databases per say...i am pulling info from tables...im sorry i am new to writing SQL code so maybe i should have put that in the that forum...sorry

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-25 : 16:15:52

If they are not DB tables, then where do you have the info about these Mothers and Infants?

Maybe I'm not understanding this right...but if they are not on any DB table...I don't see a need for using SQL.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-25 : 17:01:25

I created two tables one with the mothers, one with the infants. THen I joined them on the MotherID. The below code will illustrate how to do this.


	Declare @T_Mothers table(MotherID int,MName varchar(30))
	Declare @T_Infant table(InfantID int,MotherID Int,IName varchar(30))

Insert into @T_Mothers(MotherID,MName)
Select 1,'Joanne' Union all
Select 2,'Debbie'


Insert into @T_Infant(InfantID,MotherID,IName)
Select 1,1,'Bobbie' Union all
Select 2,2,'Craig' Union all
Select 3,1,'Johnny' Union all
Select 4,2,'Shannon' Union all
Select 5,1,'Danielle' Union all
Select 6,1,'Becky' 


Select a.MName,b.*
from
	@T_Mothers a
Inner Join
	@T_Infant b
on		a.MotherID = b.MotherID

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Basshole8117
Starting Member

8 Posts

Posted - 2009-03-26 : 06:35:18

quote:
Originally posted by vijayisonly

If they are not DB tables, then where do you have the info about these Mothers and Infants?

Maybe I'm not understanding this right...but if they are not on any DB table...I don't see a need for using SQL.

no i dont think im doing a good job of explaining. i'm using SQL 2005 to get all my info from our database tables that are already created. i have all my info on mothers and child and now i have to link the too. i work at a hospital so each mother is a patient and then when the child is born it becomes a patient with its own ID # different from the mothers and has a differnt icd9 code. let me see if i can share a little bit of code with you...

--ICD9 codes
Left Join TPM318_VISIT_DIAGNOSIS dx
on dx.vst_int_id = pat.vst_int_id
Left Join TSM910_ICD9_REF ref
on ref.icd9_int_id = dx.icd9_int_id
--Mothers
where (icd9_code in('6564', '6588', 'V271', 'V273', 'V274', 'V276', 'V277')
or (icd9_code between '65500' and '65503') or (icd9_code between '65510' and '65513'))
and (pat.adm_ts >= '1/1/2008')

--Infants
--where (icd9_code in ('3480', '7628', '7852') and ((pat.adm_ts >= '1/1/2008')
--and (psn.bth_ts >= '1/1/2008'))

see how the above infants and mothers icd9 codes are different? im going to just keep playing around with this one and see what i can come up with...like i said im new at this whole thing...thanks for all the help you have given so far.

Basshole8117
Starting Member

8 Posts

Posted - 2009-03-26 : 06:53:27

in all seriousness i dont think it is possible to do this now that i am looking at all the info again...but if anyone has any idea's i'd still love to hear them

Basshole8117
Starting Member

8 Posts

Posted - 2009-03-26 : 08:28:37

quote:
Originally posted by Vinnie881

I created two tables one with the mothers, one with the infants. THen I joined them on the MotherID. The below code will illustrate how to do this.

Declare @T_Mothers table(MotherID int,MName varchar(30))
Declare @T_Infant table(InfantID int,MotherID Int,IName varchar(30))

Insert into @T_Mothers(MotherID,MName)
Select 1,'Joanne' Union all
Select 2,'Debbie'

Insert into @T_Infant(InfantID,MotherID,IName)
Select 1,1,'Bobbie' Union all
Select 2,2,'Craig' Union all
Select 3,1,'Johnny' Union all
Select 4,2,'Shannon' Union all
Select 5,1,'Danielle' Union all
Select 6,1,'Becky'

Select a.MName,b.*
from
@T_Mothers a
Inner Join
@T_Infant b
on a.MotherID = b.MotherID

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

thanks for this. however we do not no who the mother is and that is what we are trying to find out...sorry for all the confusion guys...

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-26 : 08:43:07

What is the link between mother and baby. E.g. is there a mother's id associated with a baby or vice versa? I see you have date of admission for each but obvously this will not be accurate enough to join a baby to its mother.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-26 : 09:10:40

Try following the first link in my signature. It will explain how to ask a question, and what information we need to give you a good answer.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources