CODEWITHSUNDEEP
mysql
user1810868
user1810868

Reputation: 1605

how to select from two not joined tables in mysql

I have two tables that have no common column. But there is relation. I have table1, table2. table 1 is as follows:

t1.ID | t1.Name | t1.Number

Where Name is unique value.

table2 is as follows:

t2.ID | t2.Number1 | t2.Number2 | t2.Country

My query is as follows:

select t1.Name, t1.Number, t2.country
from db.t1, db.t2
where t1.Number between t2.Number1 AND t2.Number2

What is happening as a result from the query is that I get each record twice. But, when I add:

group by t1.Name

I get the correct result (each record once). I do not want to use group by. How to make correct query and do I get the same record twice without group by ?

Upvotes: 0

Views: 97

Answers (3)

xlecoustillier
xlecoustillier

Reputation: 16351

Try using DISTINCT :

SELECT DISTINCT 1.Name, t1.Number, t2.country
FROM db.t1, db.t2
WHERE t1.Number BETWEEN t2.Number1 AND t2.Number2

Upvotes: 1

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16076

select t1.Name ,t1.Number from t1
union
select t2.Number1 ,t2.Number2 from t2

You can use union in this case.

Upvotes: 0

Arunkumar Srisailapathi
Arunkumar Srisailapathi

Reputation: 1169

There should be at least one column that is common to both of the tables. Else you 'll get duplicate values only.

At least to my knowledge that is the case.

Upvotes: 0

Related Questions