Query Help with 2 tables

Question

Query Help with 2 tables

pandayk 85384

Grasshopper

Points: 21
More actions
October 9, 2017 at 4:50 am

#328963

Hello guys
I am new to SQL and need help with a query with 2 tables.
tblASSESSMENT-AILMENT
ASSESSMENT_ID AILMENT_ID STATUS
100 201 MAJOR
100 202 MAJOR
100 295 MAJOR
100 299 MINOR
100 55 MINOR
100 99 MINOR
100 77 MAJOR
100 87 MAJOR
100 33 MINOR
101 55 MAJOR
101 99 MAJOR
101 77 MAJOR
101 87 MINOR
101 201 MAJOR
101 299 MAJOR
101 202 MINOR
101 295 MINOR
102 55 MAJOR
102 99 MAJOR
102 295 MINOR
102 87 MAJOR
102 201 MINOR
102 202 MINOR
102 299 MAJOR
tblAILMENT
AILMENT_ID AILMENT_NAME
33 Diabetes
55 Hypertension
77 Cancer
87 Thyroid
99 cardiomyopathy
201 Migrane
202 Sinus
295 Psoriasis
299 Arthrithis
I am trying to have a table JOIN itself this is where I am confused. I need to know the following answers, I am not sure if it can be queried with SQL.
1. When an ailment was a MAJOR , what other ailments where also selected as major.
2. When an ailment was a MAJOR , what other ailments where selected as minor.
3. What is the relationship between 2 ailments, eg, how many times did they appear together as either major or minor ?
Any tips to get me started will be highly appreciated guys.
Thanks
KIm+

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

What is your question here? You haven't asked one. Questions end with a Question Mark (?), where as you have simply made a statement that you want to use a self referencing table, and you have 2 tables.

The paste you've provided seems to imply you have 3 columns (Assessment ID, Ailment ID, and Status), however, you've pasted data with a lot more than 3. Also, where is your second table?

Have a look at the link in my signature to an Article written by Jeff, which explains how to post T-SQL questions. Post again using that article to guide you and ask a question. 🙂

Edit: Ok, you've amended your post since I starting typing, good, however, it's still not consumable and you've just stated you need help with 2 tables. Great! What about them???

Edit 2: Good, more edits! We have requirements!An expected output here is key. Rather than explaining what you want from your data, tell us what the expected output should be. Again, I can't help but refer to Jeff's article. It gives you all the information you need on how to post a question including DDL, DLM. Expected output can be a formatted paste of table data, or even a SELECT statement that returns what you want; we just need to know what we need to aim for. 😎

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99143 More actions · Answer 2

Thanks for the edits. I've editted my post a couple of times, but you won't get a subscription for those so I'm posting again. Have a look at my comments and reply when you've provided the requested information.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

pandayk 85384 Grasshopper Points: 21 More actions · Answer 3

Thanks for the reply, I am really only asking how to create a SELECT statement that calls the table twice or maybe a JOIN
In my case I have a bunch of ailments that were selected in an assessment and all i want to know is, for any given ailment eg, Diabetes. what other ailment was selected with it with a status MAJOR, then again what other ailment was selected with it with a status MINOR. I am not sure how to call the table twice or should I have a view ?

So a sample output with be for an ailment diabetes the 5 most common ailments selected in the assessment as MAJOR was : hypertension, cancer, thyroid, migrane, sinus, cardiomyopathy.

Another query could be for any given assessment , how many times did diabetes and hypertension appear as MAJOR ailments.

Hope this makes sense

Thanks for replying

Thom A SSC Guru Points: 99143 More actions · Answer 4

Without consumable sample data or DDL, this is a guess and untested, however, maybe:
SELECT s.AILMENT_NAME, AAs.[Status] FROM tblAILMENT A JOIN tblASSESSMENT-AILMENT AA ON A.AILMENT_ID = AA.AILMENT_ID JOIN tblASSESSMENT-AILMENT AAs ON AA.[STATUS] = AAs.[Status] JOIN tblAILMENT s ON AAs.AILMENT_ID = s.AILMENT_ID WHERE A.AILMENT_NAME = 'Diabetes';
If you want a tested answer, and probably more responses from the community, then please supply proper DDL, DLM and Expected outputs.

Thanks,

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk