Mysql query

[Sapan Shah]

I have created 3 master table which are as follows along with the output i want to display.
I am not able to make mysql query to create the output.


Address_mst (Table1)

Column Name Id-|---addr_typ_idn--|--Name_id-|--Address_name|
---------------------------------------------------------- -
Data 1--|---102-------------|-- 21-------|-Paras Nagar |
2--|---102-------------|-- 21-------|--G.K.Nagar |

Addr_typ_mst(Table2)

Column Name Id | Addr_typ |
------ |-------------------
Data 101-|---Residence |
102--|--Office |

Name_mst (Table3)

Column Name Id | Name |
---------|----------- |
Data 21---|--Sapan |
22---|--Umans |

Output should be

Column Name Sr.No ++ |++ Name ++ |+++ Office1 +++++ |+ Office2 |
-----------|--------------- |--------------------------|------------------- |
Output Data 1 ++++|++ Sapan ++|+++ Paras Nagar ++|+++ G.K.Nagar |

[Jeanne Boyarsky]

I don't really follow what you are trying to do here. Can you explain in words what you want to return?

[Sapan Shah]

Sorry for the weird post.

I have create 3 master table which are Address_mst,Addr_typ_mst,Name_mst.
a) In addr_typ_mst there are two column name (1. Id and 2. Addr_typ ).

The first id for addr_typ_mst is (101) and addr_typ is (Residence).
The second id for addr_typ_mst is (102) and addr_typ is (Office).

b) In Name_mst there are two column name (1. Id and 2. Name ).

The first id for name_mst is (21) and name is (Sapan).
The second id for name_mst is (22) and name is (Umans).

c) In address_mst there are four column name (1. Id , 2. Addr_typ_idn , 3. Name_id and 4. Address_name ).

The first id for address_mst is (1) , addr_typ_idn for address_mst is (102) ,name_id for address_mst is (21) and address_name for address_mst is (Paras Nagar).

The second id for address_mst is (2) , addr_typ_idn for address_mst is (102) ,name_id for address_mst is (21) and address_name for address_mst is (G.K.Nagar).


Now i want to get the data in the format of.

Column Name should be (Sr.No,Name,Office1,Office2) and the below
row-data should be (1,Sapan,Paras Nagar,G.K.Nagar)

[Jeanne Boyarsky]

What I don't understand is the join pattern. I see your specific example, but that doesn't necessarily say what you want to do.

For example, you want the result to contain the columns:
Sr.No - what is this?
Name - The first id in name_mst?
Office1 - all matching entries in address_mst?
Office2

[Sapan Shah]

Sorry for not writing proper.
I have 10,000 records in our database tables

Table 1 number_mst

|idn|name_id |atr_id|charactr|

|026|9736312|101 |10101010|
|028|9736312|101 |12121212|
|027|9736312|101 |13131313|
|035|8600514|102 |20202020|
|036|8600514|101 |14141414|
|037|8600514|102 |21212121|
|050|7188413|102 |22222222|
|051|7188413|102 |23232323|
|052|7188413|102 |24242424|
|053|7188413|101 |15151515|


Table 2 name_mst
|<-idn-->|name1|
|9736312|Sapan|
|7188413|Darsh|
|8600514|Abhai|
|9573121|Prash|
|2232125|Rishit|


Table 3 number_atr_mst

|idn|dscr|
|101|Rtel|
|102|Fax |
|103|Mob|
|104|Web|
|105|Otel|

I want to write mysql query so that the output would look like as follows

Output (Select query)

|name_id|name1|<-Rtel-->|<-Rtel1->|<-Rtel2->|<--Otel-->|<-Otel1-|<-Otel2->|
|9736312|Sapan|10101010|12121212|13131313|<--null-->|<--null-->|<--null->|
|7188413|Darsh|15151515|<--null-->|<--null-->|22222222|23232323|24242424|
|8600514|Abhai|14141414|<--null-->|<--null-->|20202020|21212121|<--null-->|

[Jeanne Boyarsky]

Ah. Now I understand; you want to pivot the results. See this Ask Tom entry for how to do so in Oracle. It uses Oracle specific functions, but maybe mySql has similar ones.