Oracle SQL: How to get distinct values of a particular column from 5 different tables? -

i have 5 different tables , 1 column common in 5 tables. need 50 distinct values of column in 1st table, in second table need 50 distinct values of same column not appear in list of 50 values received in result 1st table. in way, need 250 unique values 5 tables. cannot use temporary table since don't have write access in database , can use select.

can suggest solution problem? or pointers highly appreciated!

example: suppose have 5 tables employees, greenhouse_emp, redhouse_emp, bluehouse_emp, yellowhouse_emp , greyhouse_emp;

the column common in 5 tables emp_id.

i want 50 distinct emp_ids greenhouse_emp table, 50 distinct redhouse_emp not including 50 selected greenhouse_emp table , on..

the end result should 250 employees having 50 distinct of each catagories.

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

first @ link see how joins work. visual representation helpful. asking want 5 tables joined off of 1 common column.. values want distinct each table.. need outer join (see examples) , select distinct values from tables including where table.id null

a sample select this

select     a.whatever,     b.whatever,     c.whatever,     d.whatever,     e.whatever from(     select         a.id a_id,         a.whatever     a.table     b.id null , c.id null , d.id null , e.id null     limit 50     ) temp full outer join(     select         b.id b_id,         b.whatever     b.table     a.id null , c.id null , d.id null , e.id null     limit 50     ) temp1 on temp1.b_id = temp.a_id 

.... rest other 3 tables.

i believe work, don't have way test since didn't provide much. @ least on right track doing select's

let me know if helpful , if still have questions. luck!

---

try first see if works have.

    select         e.emp_id e_id     employees e     left outer join greenhouse_emp g_e on g_e.emp_id = e.emp_id     left outer join redhouse_emp r_e on r_e.emp_id = e.emp_id     left outer join bluehouse_emp b_e on b_e.emp_id = e.emp_id     left outer join yellowhouse_emp y_e on y_e.emp_id = e.emp_id     g_e.emp_id null , r_e.emp_id null , b_e.emp_id null , y_e.emp_id null     limit 50 

---

well don't know if query works, kinda still waiting on response you. went ahead , built whole thing. pretty gross sql select , not how recommend doing parameters can think of @ moment.

select     * from(         select         e.emp_id e_id     employees e     left outer join greenhouse_emp g_e on g_e.emp_id = e.emp_id     left outer join redhouse_emp r_e on r_e.emp_id = e.emp_id     left outer join bluehouse_emp b_e on b_e.emp_id = e.emp_id     left outer join yellowhouse_emp y_e on y_e.emp_id = e.emp_id     g_e.emp_id null , r_e.emp_id null , b_e.emp_id null , y_e.emp_id null     limit 50 ) t union(     select         g_e.emp_id g_id     greenhouse_emp g_e     left outer join employees e on g_e.emp_id = e.emp_id     left outer join redhouse_emp r_e on r_e.emp_id = g_e.emp_id     left outer join bluehouse_emp b_e on b_e.emp_id = g_e.emp_id     left outer join yellowhouse_emp y_e on y_e.emp_id = g_e.emp_id     e.emp_id null , r_e.emp_id null , b_e.emp_id null , y_e.emp_id null     limit 50 )  union(     select         r_e.emp_id r_id     redhouse_emp r_e     left outer join greenhouse_emp g_e on g_e.emp_id = r_e.emp_id     left outer join employees e on r_e.emp_id = e.emp_id     left outer join bluehouse_emp b_e on b_e.emp_id = r_e.emp_id     left outer join yellowhouse_emp y_e on y_e.emp_id = r_e.emp_id     g_e.emp_id null , e.emp_id null , b_e.emp_id null , y_e.emp_id null     limit 50 )  union(     select         b_e.emp_id b_id     bluehouse_emp b_e     left outer join greenhouse_emp g_e on g_e.emp_id = b_e.emp_id     left outer join redhouse_emp r_e on r_e.emp_id = b_e.emp_id     left outer join employees e on b_e.emp_id = e.emp_id     left outer join yellowhouse_emp y_e on y_e.emp_id = b_e.emp_id     g_e.emp_id null , r_e.emp_id null , e.emp_id null , y_e.emp_id null     limit 50 ) union(     select         y_e.emp_id y_id     yellowhouse_emp y_e     left outer join greenhouse_emp g_e on g_e.emp_id = y_e.emp_id     left outer join redhouse_emp r_e on r_e.emp_id = y_e.emp_id     left outer join bluehouse_emp b_e on b_e.emp_id = y_e.emp_id     left outer join employees e on y_e.emp_id = e.emp_id     g_e.emp_id null , r_e.emp_id null , b_e.emp_id null , e.emp_id null     limit 50 ); 

this query should return 1 columns 250 id's. used union add 1 column... if id same in more 1 of subquery results less 250 rows returned (a way test). if test it simpler lol. let me know if closer want.