Query for Selecting Unique ROWS

Question

0.00/5 (No votes)

See more:

Respected SIR/MADAM
I have 3 Tables like t1 which contains 3 field (Id,Name,registrationdate) and second t2(Id,Complaints,complaindate) and third t3(Id,Disease,diseasedate). I am register a id like 1 and i have inserted 2 complaints and 2 diseases . now i want to retrieve the data of complaints and diseases. i have tried like this

SQL

select distinct a.id,b.id,c.id,b.complaints,c.Disease,a.registrationdate,b.complaindate,c.diseasedate from t1 a left join t2 b on a.id = b.id left join t3 c on a.id = c.id and b.id= c.id ;

but it shows 4 records
like
1, 1, 1, Complaint1, Dengue,2014-06-23
1, 1, 1, Complaint2, Dengue,2014-06-23
1, 1, 1, Complaint1, fever,2014-06-23
1, 1, 1, Complaint2, fever,2014-06-23

but i want only 2 records like this
1, 1, 1, Complaint1, Dengue,2014-06-23
1, 1, 1, Complaint2, fever,2014-06-23

how can i get it.
your help will be appriciatable for me ..
Thanks In Advance

Posted 22-Jun-14 19:53pm

Mohammad Nawaz

Add a Solution

Comments

Jörgen Andersson 23-Jun-14 2:39am

Remove the last condition "and b.id= c.id", it's redundant.
Your query contains three date columns, but your result only one.
Please update your question, using the "Improve question" button, with some example data from the tables giving the result shown.

data modeling guy 23-Jun-14 2:53am

Jorgen, with the given set of attributes in t1,t2 and t3, there is no logical way to associate a complaint with decease.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

data modeling guy · Answer 1 · 2014-06-22T20:33:00

Nawaz,

I assume you have have created table t3 separate from t2 as each complaint can have many deceases associated with it. If yes, you need a PK in Complaint table(t2) and an FK in decease table(t3) referencing t2 that models the association between decease and corresponding complaint. You may re-write you SQL once the data model is done right.

Magic Wonder · Answer 2 · 2014-06-24T23:07:00

Hi,

You have to change your table structure little. Add a column as ID_New in T2 which will maintain the unique Id of table T2. And use that T2 new id in T3 as reference. So, your query might look like below

SQL

select distinct
a.id,
a.regdate,
b.id,
b.complain,
b.compdate,
c.id,
c.disease,
c.diseasedate
from T1 as a
left join  T2 as b on a.id = b.id
left join  T3 as c on b.id_new= c.id

Hope this will help you.

Query for Selecting Unique ROWS

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0