?
Solved

sql query

Posted on 2005-05-12
9
Medium Priority
?
235 Views
Last Modified: 2010-03-19
I have a table name tblMHCIntakeDetail in this table is a field named lngdataid and a field named strtype.

There is a field name lngdataid and a field named strtype.   In the strtype column there is are value Issue or Pop and in the lngDataid there are values 225 and 172.  What I am looking for is to get everyone who has the value of 172 which means a child and is also code as a 225.  Thanks

What I really need is  a count of all

SELECT     strtype, lngDataid
FROM         tblMHCIntakeDetail  where  lngdataid = ?
0
Comment
Question by:running32
9 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13990060
Will this work for you?

SELECT     count(*)
FROM         tblMHCIntakeDetail  
where  lngdataid IN (225, 172)
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13990069
or maybe this:

SELECT     strtype, lngDataid, count(*)
FROM         tblMHCIntakeDetail  
where  lngdataid IN (225, 172)
GROUP BY strtype, lngDataid
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13990194
Do you mean that you want all records that appear with both a 225 and 172 lngDataid value?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:running32
ID: 13990232
I don't think I can use these records I'm going to have to find the record with the individual which meets both.  Ie 225 = under 18 and 172 = depression.

thanks
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13990267
How about this:

SELECT     count(*)
FROM         tblMHCIntakeDetail  A INNER JOIN tblMHCIntakeDetail  B
ON A.ID = B.ID AND A.lngdataid = 225 AND B.lgndataid = 172
0
 

Author Comment

by:running32
ID: 13990552
Here is what I have so far.  

I can see the matches but what what I need is all dbo.tblPatient.lngPatientId that have both 172 and 225.  Thanks

SELECT dbo.tblPatient.lngPatientId, dbo.tblPatient.strPatientId, dbo.tblPatient.strLName, dbo.tblPatient.strFName,
                      dbo.tblMHCIntake.dtmIntake, dbo.tblMHCIntakeDetail.strType, dbo.tblMHCIntakeDetail.lngDataId
FROM      dbo.tblPatient INNER JOIN
                      dbo.tblMHCIntake ON dbo.tblPatient.lngPatientId = dbo.tblMHCIntake.lngPatientId INNER JOIN
                      dbo.tblMHCIntakeDetail ON dbo.tblMHCIntake.lngIntakeId = dbo.tblMHCIntakeDetail.lngIntakeId
WHERE  (dbo.tblMHCIntakeDetail.lngDataId = 172) or  (dbo.tblMHCIntakeDetail.lngDataId = 225)
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13990591

SELECT dbo.tblPatient.lngPatientId, dbo.tblPatient.strPatientId, dbo.tblPatient.strLName, dbo.tblPatient.strFName,
                      dbo.tblMHCIntake.dtmIntake, dbo.tblMHCIntakeDetail.strType, dbo.tblMHCIntakeDetail.lngDataId
FROM      dbo.tblPatient INNER JOIN
                      dbo.tblMHCIntake ON dbo.tblPatient.lngPatientId = dbo.tblMHCIntake.lngPatientId INNER JOIN
                      dbo.tblMHCIntakeDetail ON dbo.tblMHCIntake.lngIntakeId = dbo.tblMHCIntakeDetail.lngIntakeId AND dbo.tblMHCIntakeDetail.lngDataId = 172
INNER JOIN
                      dbo.tblMHCIntakeDetail B ON dbo.tblMHCIntake.lngIntakeId = B.lngIntakeId AND B.lngDataId = 225
0
 
LVL 6

Expert Comment

by:PePi
ID: 13990610
SELECT dbo.tblPatient.lngPatientId, dbo.tblPatient.strPatientId, dbo.tblPatient.strLName, dbo.tblPatient.strFName,
                      dbo.tblMHCIntake.dtmIntake, dbo.tblMHCIntakeDetail.strType, dbo.tblMHCIntakeDetail.lngDataId
FROM      dbo.tblPatient INNER JOIN
                      dbo.tblMHCIntake ON dbo.tblPatient.lngPatientId = dbo.tblMHCIntake.lngPatientId INNER JOIN
                      dbo.tblMHCIntakeDetail ON dbo.tblMHCIntake.lngIntakeId = dbo.tblMHCIntakeDetail.lngIntakeId
WHERE  (dbo.tblPatient.lngDataId = 172) or  (dbo.tblPatient.lngDataId = 225)
0
 

Author Comment

by:running32
ID: 13990690
rafrancisco thanks for your help!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question