Click here to Skip to main content
15,798,592 members
Please Sign up or sign in to vote.
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

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

but it shows 4 records
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
Jörgen Andersson 23-Jun-14 2:39am    
Remove the last condition "and", 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.


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.
Share this answer

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

select distinct,
from T1 as a
left join  T2 as b on =
left join  T3 as c on b.id_new=

Hope this will help you.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900