Data from Multiple Tables

[deepmann97]

Hello Friends,
I have three table and i want to select data from them
Table 1
Roll No. --- Name---Age
1------------a--------12
2------------b--------13

Table 2
Roll No.---Name---Age
7------------d------12
8------------b------13
9------------c------12

Table 10
Roll No.---Name---Age
41---------d---------12
43---------b---------13
44---------c---------12

Table 3
Roll No.---Sex
1------------f
7------------f
41----------f

i want to select date from Table 1 and Table 2 and Table 3 only for the Rolle No. 1 and 7. i tried inner loop. total table are 10. I want to display age and sex.

[tesuji]

hi deepman,
try this

select t.rollno, t.age, s.sex from table1 t 
       join table3 s on t.rollno = s.rollno
         where s.rollno = 1 or s.rollno = 7
union
select t.rollno, t.age, s.sex from table2 t 
       join table3 s on t.rollno = s.rollno
         where s.rollno = 1 or s.rollno = 7
union
select t.rollno, t.age, s.sex from table10 t 
       join table3 s on t.rollno = s.rollno
         where s.rollno = 1 or s.rollno = 7

So far, i didn't test it.
what is the reason, that you have split the data over various tables?

krs,
tesu

[deepmann97]

tesuji

actualy have 10 tables for different classes.
Table A for Batch 1
___________________
Roll No. | Name | Age
A1 aa 12
A2 ab 13
A3 bb 12

Table B for Batch 2
___________________
Roll No. | Name | Age
B1 ac 12
B2 ad 13
B3 ba 12

Table c for Batch 3
___________________
Roll No. | Name | Age
C1 ae 12
C2 ad 13
C3 bd 12

Table S for Batch 2
___________________
Roll No. | Sex
A1 f
B3 f
C2 f

I want to select details from table A,B,C for the Roll No. in table S

[tesuji]

So the advised select should function correctly. Solely just out of curiosity, did you true check the advised select on your database? Possibly you might have done some modification of it.

[mwasif]

Why are you creating separate tables for each batch? You can save all your data only by adding a new column batch in a table and saving corresponding batch in the field.