CODEWITHSUNDEEP
mysqljoin
user3299199
user3299199

Reputation: 63

MySQL query eleminating duplicates from second table

I have two mysql tables: table_1 and table_2.

table_1:

| c_id | name |  email  |
|  1   | tom  | [email protected] |

table_2:

| a_id | c_id | address | street |
|  1   |  1   |   67    |  home  |
|  2   |  1   |   68    |  main  |

How to create mysql query that will select table_1.name, table_1.email, table_2.address and table_2.street returning only one record like:

| name | email   | address | street |
| tom  | [email protected] | 67      | home   |

Thanks for any suggestion. Regards, T

Upvotes: 0

Views: 54

Answers (3)

Mr. Radical
Mr. Radical

Reputation: 1855

Try this: 

SELECT table_1.name, table_1.email, table_2.address, table_2.street
FROM table_1
JOIN table_2
ON table_1.c_id = table_2.c_id
GROUP BY table_1.c_id

SQLfiddle demo

The GROUP BY will determine which column you want to use to group the results by. B.T.W. if you like the change the column head you can add AS followed by the column head name (table_1.name AS "First name").

Table structure and sample data:

CREATE TABLE table_1
    (`c_id` int, `name` varchar(3), `email` varchar(7))
;

INSERT INTO table_1
    (`c_id`, `name`, `email`)
VALUES
    (1, 'tom', '[email protected]')
;

CREATE TABLE table_2
    (`a_id` int, `c_id` int, `address` int, `street` varchar(4))
;

INSERT INTO table_2
    (`a_id`, `c_id`, `address`, `street`)
VALUES
    (1, 1, 67, 'home'),
    (2, 1, 68, 'main')
;

If you like to limit the sql query to a certain person say tom WHERE table_1.name LIKE 'Tom'. Like so:

SELECT table_1.name, table_1.email, table_2.address, table_2.street
FROM table_1
JOIN table_2
ON table_1.c_id = table_2.c_id
WHERE table_1.name LIKE 'Tom'
GROUP BY table_1.c_id;

You can also use = but with LIKE you can use wildcards like T%

Upvotes: 1

AllInOne
AllInOne

Reputation: 1450

select table_1.name as name, table_1.email as email, table_2.address as address, table_2.street as street
from table_1 
left join table_2 on table_1.c_id = table_2.c_id

Upvotes: 0

Cerveser
Cerveser

Reputation: 860

you should use the SQL statement LEFT JOIN, for example 

SELECT name, email, address, street
FROM table1
LEFT JOIN table2
ON table1.c_id=table2.c_id;

with the possible option WHERE name = 'tom'

Upvotes: 0

Related Questions