File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Mysql query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Mysql query" Watch "Mysql query" New topic
Author

Mysql query

Sapan Shah
Greenhorn

Joined: Jan 01, 2010
Posts: 25
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30382
    
150

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sapan Shah
Greenhorn

Joined: Jan 01, 2010
Posts: 25
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30382
    
150

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

Joined: Jan 01, 2010
Posts: 25
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30382
    
150

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.
 
jQuery in Action, 2nd edition
 
subject: Mysql query