MySql single query, two tables same field name

Question

I have two database tables one called clients, the other one users. I'm trying to select single field "email" from both tables. Note, that email field is in both tables.

Table clients contains 10 records, while users has 5 records.

The below query works, but for some reason records are repetitive, so instead of getting 15 records in total, I end up with 50. Seems like records from one table are multiplied by records from other table.

SELECT 
  clients.email,
  users.email 
FROM
  clients,
  users;

Any help would be appreciated.

You need a join or a where clause. Check out http://en.wikipedia.org/wiki/Join_%28SQL%29 — j08691, Dec 19 '14 at 03:33
Since you are not applying any condition or joins, the query is bound to return all the email fields from both tables. — Aditya, Dec 19 '14 at 03:33
I want to return email fields from both tables. That's the whole point, but instead of 15 results, I'm getting 50. each record is repeated x10 — user3176519, Dec 19 '14 at 03:49

score 0 · Answer 1 · answered Dec 19 '14 at 03:35

0

Your query returns cartesian product

You can simply use union all

select email from clients
union all
select email from users

answered Dec 19 '14 at 03:35

Abhik Chakraborty

44,654
6
52
63

I'm getting "General error: 1271 Illegal mix of collations for operation 'UNION'" – user3176519 Dec 19 '14 at 03:46
Looks like you have different collations on both tables and columns.Change the collations of them and make them same across. http://stackoverflow.com/questions/1294117/how-to-change-collation-of-database-table-column http://stackoverflow.com/questions/742205/mysql-alter-table-collation – Abhik Chakraborty Dec 19 '14 at 03:57

score 0 · Answer 2 · answered Dec 19 '14 at 04:04

You are getting 50 rows because cross join is happening there, go for inner join or left join or right join as per your choice, most preferably inner join... Take some common attributes in both like id or something...

select clients.email, users.email from clients,users where clients.id=users.id...

score -1 · Answer 3 · answered Dec 19 '14 at 03:35

-1

try renaming the fields

For Clients table

clients_email

For Users table

users_email

then query as

SELECT clients.clients_email, users.users_email FROM clients, users WHERE clients.clients_email = users.users_email;

Hope it helps

answered Dec 19 '14 at 03:35

Kostas Drak

3,222
6
28
60

Why renaming required? – Indra Kumar S Dec 19 '14 at 03:37
i never said it is required.. i said TRY renaming...it means that if he wants he can do it...it is not a must – Kostas Drak Dec 19 '14 at 03:39

MySql single query, two tables same field name

3 Answers3