February 6, 2012 at 10:49 pm
Hi there. First off, I have the following tables:
hospital:
hid
hname
city
patient:
pid
pname
byear
bplace
visit:
pid
hid
date
diagnosisid
disease:
did
dname
check_period_months
risk
What I need is to trace the disease infections. I consider that a disease X is transmitted when a patient is diagnosed with X in a hospital and in the same hospital there is another patient who is diagnosed with X later. I am using this sql statement below, but I am getting the wrong results. Could you please help me out? Thanks in advance.
SELECT DISTINCT(p1.pname), p2.pname, h1.hname, v1.date, h3.hname, v3.date FROM patient p1, patient p2, visit v1, visit v2, visit v3, hospital h1, hospital h2, hospital h3, disease d1, disease d2, disease d3 WHERE p1.pid=v1.pid AND v1.hid=h1.hid AND p1.pid!=p2.pid AND p2.pid=v2.pid AND v2.hid=h1.hid AND v1.date=v2.date AND v1.diagnosisid=d1.did AND v2.diagnosisid=d2.did AND d1.did=d2.did AND d1.dname="some_disease" AND p2.pid=v3.pid AND v3.hid=h3.hid AND v3.date>v2.date AND v3.diagnosisid=d3.did AND d3.did=d1.did
February 6, 2012 at 11:55 pm
Could you include some sample data and the desired results?
(read the link in my sig about posting questions)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2012 at 7:05 am
All right. For instance, I am supposed to take these results for diabetes:
pname from pname to infection hname infection date diagnosis hname diagnosis date
Christina Winslet Sotiris Chen Maimonides 1916/02/01 Interfaith 1977/03/01
Christina Winslet Sotiris Chen Maimonides 1916/02/01 Triada 1950/11/02
Christina Winslet Sotiris Chen Maimonides 1916/02/01 Agios Panteleimonas 1983/05/01
Panos Poulain Chitose Peters PAGNI 1956/10/02 Montefiore 1989/06/02
but I take this, which is obviously totally wrong:
pname from pname to infection hname infection date diagnosis hname diagnosis date
Archimedes PicassoRyan PicassoInterfaith1/7/60 New York 3/2/48
February 7, 2012 at 7:35 am
niktaxi (2/7/2012)
All right. For instance, I am supposed to take these results for diabetes:pname from pname to infection hname infection date diagnosis hname diagnosis date
Christina Winslet Sotiris Chen Maimonides 1916/02/01 Interfaith 1977/03/01
Christina Winslet Sotiris Chen Maimonides 1916/02/01 Triada 1950/11/02
Christina Winslet Sotiris Chen Maimonides 1916/02/01 Agios Panteleimonas 1983/05/01
Panos Poulain Chitose Peters PAGNI 1956/10/02 Montefiore 1989/06/02
but I take this, which is obviously totally wrong:
pname from pname to infection hname infection date diagnosis hname diagnosis date
Archimedes PicassoRyan PicassoInterfaith1/7/60 New York 3/2/48
Read this article and try posting the readily usable sample data and DDL script again please.[/url]
February 7, 2012 at 7:36 am
As was pointed out previously you need to help us help you. That means it makes it a LOT easier for us if you post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. Looking at your data make sure it is sample data and NOT real data as the information you are dealing with is sensitive.
As for your query I would start by using joins instead of the older syntax. It is so hard to read a comma separated list of tables like that, and really easy to miss a criteria.
select [fields]
from table1
join table2 on table1.field = table2.field
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 7:17 am
this will almost certainly be best solved with an EXISTS type of query, but as others have said you will need to provide better inputs for us to help you with.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply