Sql return one row from right table every left table result

0
votes

My question is how do I get 1 result from each result from Table1 for Table2? And not al results also from table2? I already searches stack overflow I found how to solve this with a id but I want to know how to do this with a Varchar column.

Table1

+--------------+-------------+
| Ip (Varchar) | Count (Int) |
+--------------+-------------+
| 1.1.1.1      | 9           |
| 1.1.1.2      | 6           |
| 1.1.1.3      | 1           |
+--------------+-------------+

Table2

+-------------+--------------+
| Name (Text) | Ip (Varchar) |
+-------------+--------------+
| User01      | 1.1.1.1      |
| User034     | 1.1.1.1      |
| User012     | 1.1.1.1      |
| User21      | 1.1.1.2      |
| User24      | 1.1.1.2      |
| User3       | 1.1.1.3      |
+-------------+--------------+

Wanted result

+--------+---------+-------+
| Name   | Ip      | Count |
+--------+---------+-------+
| User01 | 1.1.1.1 | 9     |
| User21 | 1.1.1.2 | 6     |
| User3  | 1.1.1.3 | 1     |
+--------+---------+-------+

It does not matter what name will be returned.

2
mysqlsqljoin
How do you decide which result(s) to return? - Strawberry
It looks like you want to join tables 1 and 2 on the IP, which is easy enough for us to help you with, but how do you choose which name to keep? - AdamMc331
It does not matter what name . - user3279848
So do you need any name, or can it be omitted? - AdamMc331
I only need 1 random name each ip. - user3279848

2 Answers

1
votes

Because you just need a random name, you can use group by since you're using mysql:

select t1.ip, t1.count, t2.name
from table1 t1 
    join table2 t2 on t1.ip = t2.ip
group by t1.ip

Depending on your data, you may need an outer join as well.

1
votes

You should start by joining the two tables together on the condition that the ip address matches:

SELECT t2.name, t2.ip, t1.count
FROM firstTable t1
JOIN secondTable t2 ON t1.ip = t2.ip;

However, this will return all rows in the second table with a matching ip, so results will appear more than once. If you only want to see specific pairings, you should group by the ip column:

SELECT t2.name, t2.ip, t1.count
FROM firstTable t1
JOIN secondTable t2 ON t2.ip = t1.ip
GROUP BY ip;

As far as the name goes, if you're not picky you can leave it as above and it will be picked arbitrarily, or you could chose something consistent like the minimum username:

SELECT MIN(t2.name), t2.ip, t1.count
FROM firstTable t1
JOIN secondTable t2 ON t2.ip = t1.ip
GROUP BY ip;

Here is an SQL Fiddle example. It shows all three queries so you can visualize the steps I took.