Member Avatar for 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.

  • 3 Contributors
  • 4 Replies
  • 105 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mwasif

Dani AI

Generated

Brief recap: has student data split across per‑batch tables and a small table that lists the roll numbers to filter by. suggested querying each batch table and combining results; pointed out that a single, normalized table would be simpler. Both routes work; the notes below show a safe migration path (best long term), a maintainable on‑the‑fly option if you cannot change schema now, and practical indexing/validation tips.

A safe migration workflow (apply on a copy first): create one canonical students table that records batch, rollno, name, age and sex, then move rows from each batch table into it and set up constraints and indexes. Example DDL and simple per‑table migration pattern:

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  batch VARCHAR(32) NOT NULL,
  rollno VARCHAR(32) NOT NULL,
  name VARCHAR(120),
  age TINYINT UNSIGNED,
  sex CHAR(1),
  UNIQUE KEY (batch, rollno),
  INDEX (rollno)
);

INSERT INTO students (batch, rollno, name, age)
SELECT 'A', rollno, name, age FROM batch_A;

Populate sex by joining the small rollno list and then validate duplicates, nulls, and data formats. Keep a unique constraint (or surrogate id) so later joins are simple and fast.

If you cannot change schema immediately, create a single virtual source using a generated UNION (built at runtime from information_schema) and query that. For many tables, generate the SELECT string programmatically, prepare it and execute it so you avoid hand‑maintenance of many UNIONs:

SELECT GROUP_CONCAT(CONCAT('SELECT ''', table_name, ''' AS batch, rollno, name, age FROM ', table_name) SEPARATOR ' UNION ALL ')
INTO @q
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name LIKE 'batch_%';

PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Practical tips: index the rollno column used in joins; decide whether rollno is globally unique or only unique per batch and enforce that in schema; test all steps on a copy and keep backups; for heavy read traffic consider a materialized combined table or view. These steps make queries simpler, faster, and easier to maintain than ad‑hoc per‑table code.

Recommended Answers

All 4 Replies

Member Avatar for 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

Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.