Search code examples
mysqlproceduresqlyog

how to get specific columns with the help of primary key from entire database

last time i had asked a question which helped me a lot, thanks to all who supported..

my question is that in our office we are using mysql server 5.5 and the GUI we are using is SQLYOG..

i have a database which has approx 10 tables and each table has more than 20000 rows .. one column is common in every table, and that is a primary key on first table and it is foreign key on other 9 tables.

now i want to make such query or a procedure which let me find any column from any table (i mean that 10 tables) with the help of that ID column that is similar in all the tables ..

for example i want to find table1.column , table2.column, table3.column , table4.colum, table5.column where Id = 4578143 ;

please help ..

Thanks a million

Solution

  • What you want is to JOIN those tables based ON their common column: 

    SELECT t1.column, t2.column, t3.column, t4.column FROM t1 
INNER JOIN (t2, t3, t4) ON (t2.id=t1.id AND t3.id=t1.id AND t4.id=t1.id)
WHERE [...]

    Check this http://dev.mysql.com/doc/refman/5.0/en/join.html and this http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ for more information on JOIN Syntax.