Query Help with 2 tables

Question

Query Help with 2 tables

Viewing 4 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