Click here to Skip to main content
15,913,941 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how to join 5 tables and if one table is filled and others are empty,then we can get that row . Each tables have column with month ,year ,date and T1

EX:
MNTH  YEAR  DATE    T1    |    OMNTH  YEAR DATE T2
JAN   2017 22/1/17  CC    |    ....  .... ...  ..     etc.........


REQUIRED OUTPUT

MNTH  YEAR  DATE    T1 T2 T3 T4 T5
 JAN   2017 22/1/17  CC .. .. .. ..


these are filtered by condition of date (date tym piker)

What I have tried:

SQL
var qry = "
  SELECT month,T1,T2,T3,T4,T5 
  FROM T1 AS m 
   FULL JOIN T2 AS o ON m.month = o.omonth and m.year=o.year 
   FULL JOIN T3 AS s ON s.smonth = o.omonth and s.year=o.year 
   FULL JOIN T4 AS t ON t.tmonth = m.month and t.year=m.year 
   FULL JOIN T5 AS p ON p.spmonth=o.omonth and p.year=o.year where m.date between '" + 
   dateTimePicker6.Value.ToString("yyyy/MM/dd") + "' and '" + dateTimePicker5.Value.ToString("yyyy/MM/dd") + "'"


It joins the table and give only output when two or more tables are filled.
Posted
Updated 17-Jul-18 22:59pm
v3
Comments
Maciej Los 26-Jun-18 5:59am    
Sounds like wrong database design!
CHill60 26-Jun-18 6:01am    
Why do you have several tables with the same schema? That is poor database design.
You need to use the Improve question link to tidy up your sample data ... "etc" is not helpful. While you're there include SQL in the tags
Also, do not concatenate strings to create SQL statements - use Parameterized queries - SQL Injection Prevention Cheat Sheet - OWASP[^]
Member 13854008 26-Jun-18 6:15am    
ok...then how to join 5 tables together and how to see in datagrid when one table only filled
Kornfeld Eliyahu Peter 26-Jun-18 6:26am    
1. Why not all joins created between T1 and the joined table? Why mixing it?
2. You show the expected output, but not the real (and why it is not good)!

1 solution

If i understand you well...

Let say T1 is contains complete data and the other tables - not. So, to be able to get all data from T1 and corresponding data from T2 ... T5, you need to use the other type of join: LEFT JOIN.

For further details, please see: Visual Representation of SQL Joins[^]

Note: your code is SQL Injection[^] vulnerable. You need to use parameterized queries[^] to avoid it:
SqlCommand.Parameters Property (System.Data.SqlClient)[^]
How to: Provide a Value to a Query Parameter[^]
 
Share this answer
 
Comments
Member 13854008 30-Jun-18 1:24am    
tanku this link helps me... but i have one doubt too
i select the month from first table ,so if first table is empty the month didn't came there intead of that zero can fill there. so what to do to get the month also there
Maciej Los 1-Jul-18 16:04pm    
You're very welcome.
You have to add another table, which will contain all months.
Member 13854008 30-Jul-18 0:22am    
if we add another table month ,then how the each month enter there and link with other tables

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