File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Mysql query

 
Sapan Shah
Greenhorn
Posts: 25
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33684
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't really follow what you are trying to do here. Can you explain in words what you want to return?
 
Sapan Shah
Greenhorn
Posts: 25
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33684
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 25
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33684
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic