I am having a problem using distinct with *. I have a table that as a join and I am trying to do a statement like:
SELECT DISTINCT Name, * FROM table_a JOIN table_a.id=table_b.id WHERE status=1
But it is not allowing me to do so. Is there a way of using Distinct with * option?
I don't have access to test this right now, but I suspect that the problem is not with distinct but rather that you have columns in each table with the same names (like id) and it doesn't know which of the two conflicting columns to choose. Does it change if you do select distinct table_a.*, table_b.* ...?
*.DISTINCT * probably will return all rows, because they are distinct:-)
To get all different names use DISTINCT name or to retrieve some statistics about names (e.g. count) use SELECT name, COUNT(*) FROM ... GROUP BY name.
It is not clear what you want to do.
Maybe you want to return only one column with a given name (not two id columns).
In this case the standard SQL query is like this:
SELECT * FROM table_a JOIN table_b USING (id) WHERE status=1
The keyword DISTINCT means that after performing the select, duplicate rows are eliminated (not duplicate column names).
Basically the solution is more like this:
SELECT *
FROM table_a, table_b
JOIN table_a.id = table_b.id
WHERE status = 1
GROUP BY table_a.name
This is useful, if you want to avoid the enumeration of attributes. When you do, and the relation changes (one attribute is removed or added), your query will not work.
Read this: MySQL 8.3.1.13. DISTINCT Optimization
FROM clause include a table named table_b?) I suspect the answer was SELECT * FROM table_a NATURAL JOIN table_b WHERE status = 1;
DISTINCT when you use *.NATURAL JOIN is a good way to achieve that.
Name, *part?mysqlandpostgresql?DISTINCT *does not make any sense. That will include the primary key column(s) and so by defintion all rows will be returned