Joining tables

[arunpatal]

Hi, i have 4 tables and three of them have same fields

 

T1 = CSS

T2 = PHP

T3 = HTML

 

T4 = subcat_table

 

now i am sending one value via get link

 

page.php?sub_id=14

 

now i want to display all data from T1, T2, T3 which have sub_id value = 14.

 

ofcourse T1, T2, T3 have sub_id field.

[Barand]

If T1, T2 and T3 all have the same fields then why on earth are they three separate tables?

 

Use a UNION.

[vinny42]

 

now i want to display all data from T1, T2, T3 which have sub_id value = 14.

 

If the tables have different columns then run four queries.

You are fetching three different types of data and I'm guessing you also want to know and show which data is CSS, which is PHP and which is HTML and that's just easier with separate queries.

 

A UNION is possible, if the tables are identical, but the I'd advise to doe something like:

SELECT col1, col2, col3, 'CSS' AS data_type FROM css_table

UNION ALL

SELECT col1, col2, col3, 'PHP' FROM php_table

UNION ALL

SELECT col1, col2, col3, 'HTML' FROM html_table

 

so you can use the new virtual field 'data_type' in your resultset to see which is which. Otherwise you'l use CSS asif it is PHP and that just weird.

 

Oh, and in that case I'd also like to know why there are sperate tables, that would only serve a purpose if the tables contain millions of records.

[arunpatal]

Hi,

Am not storing data in one table because there is lot of information.....

I will try UNION ALL tomorrow and will come back with the result

But thanks for looking into the matter 

Edited September 17, 2013 by arunpatal

[Barand]

 

Am not storing data in one table because there is lot of information.....

 

So you're talking about tens of millions then?

[vinny42]

 

So you're talking about tens of millions then?

 

Or thousands of large records, or heavily indexed records, or a server with slow disks, or the database system has no per-record security.

 

Granted, it is usually only the really large tables that need partitioning, but sheer volume is not the only reason to do it.