Menu
I have 2 tables in my database. One named data, another named changes.
The table's columns are
Data
------------
|id | name |
|-----------
|1 | Test |
|2 | Hello|
------------
changes
------------------------------------
|id | name | related_id | Comments |
|-----------------------------------
|1 | Test | 1 | Example |
|2 | Hello| 2 | Example2 |
|3 | Hello| 2 | Example3 |
------------------------------------
As you can see, changes.related_id is a foreign key to data.id.
changes can have multiple rows of same name and related_id and different comments.
After running this query, I realized that multiple rows are returned where for example, Hello can appear 2 times.
SELECT DISTINCT data.name, changes.comments FROM data LEFT JOIN changes ON data.id = changes.related_id
Result
--------------------
|name | comments |
|-------------------
|Hello | Example2 |
|Hello | Example3 |
--------------------
How do I go about making sure that only 1 row is returned? I went about SO to look for answers and many stated using DISTINCT, yet it's not working in my case.
Thanks in advance.
499
Again, if we perform a left outer join where date = date, each row from Table 5 will join on to every matching row from Table 4. However, in this case, the join will result in 4 rows of duplicate dates in the joined DataSet (see Table 6).
Using an Incomplete ON Condition. Unwanted rows in the result set may come from incomplete ON conditions. In some cases, you need to join tables by multiple columns. In these situations, if you use only one pair of columns, it results in duplicate rows.
When I use the left join with distinct, it applies in the fact table and not in the category inline load. It turns out it perfoms the join operation first and then the distinct.
mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl -> ORDER BY last_name; An alternative to the DISTINCT command is to add a GROUP BY clause that names the columns you are selecting. This has the effect of removing duplicates and selecting only the unique combinations of values in the specified columns.
If you want only one record for every name in your data table and it can be any record, then you can group by the unique column (name) and use an aggregate function on the other column like max()
SELECT data.name, max(changes.comments)
FROM data
LEFT JOIN changes ON data.id = changes.related_id
GROUP BY data.name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
