CODEWITHSUNDEEP
mysqljoin
Java Questions
Java Questions

Reputation: 7953

need help to construct query

I have the following two tables and would like to construct a select query that will match the fields between the tables:

 tablename       columnname           size  order
    employee        name               25    1
    employee        sex                25    2
    employee        contactNumber      50    3
    employee        salary             25    4
    address         street             25    5
    address         country            25    6

from this i would like to construct query like 

select 
        T1.name,
        T1.sex,
        T1.contactNumber,
        T1.salary,
        T2.street,
        T2.contry 
from tablename1[employee] T1,
     tablename2[address] T2

how to construct the above query, here table name can be N also the columname can be also N.

Upvotes: 0

Views: 86

Answers (2)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You should have a foreighn key column in table 2 so that you can join it to table 1 on id.Also the query you are using will fetch a cartisian product.

select 
    T1.name,
    T1.sex,
    T1.contactNumber,
    T1.salary,
    T2.street,
    T2.contry 
from tablename1[employee] T1
left join tablename2[address] T2 on T2.employee_id = T1.id

Upvotes: 2

Nir Alfasi
Nir Alfasi

Reputation: 53535

@Learner: in the current situation there is no way to cross-reference between the tables (Google: foreign-key).

What you want to do is to add a new-column to both tables which will be called employee_id, then you'll be able to get the data by:

select T1.*, T2.*
from employee T1, address T2
where T1.employee_id = T2.employee_id

Upvotes: 0

Related Questions