There is a DISTINCT keyword, which eliminates duplicates from the rows being fetched. This keyword is placed right after the SELECT. Then there is the GROUP BY clause, which is useful to obtain only one distinct value from a column or several columns, and some kind of aggregate values for the rest of columns. It comes right after the WHERE clause.
If you describe what exactly you need, we can try to point you in the right direction. Additionally, you might want to read some SQL tutorial.
Joined: Jan 31, 2010
in a Mysql VIEW I used "join" to join two tables
...table1 join table2...
table2 has 2 entries(rows) and table1 has also 2 entries join by email(for a particular email), expected the result to return for this email(primary key) 2 rows(as many as table2 has for this email) return double 4 rows, in other words two rows have appeared twice,... the other data(customers) that had 1 in each table appeared normally as one row... well?
That's how joins work. They take everything from the left table (table1), everything from the right table (table2), and match them. Depending on the join type it may omit results that appear on only one side. For example, an inner join removes results from either table that has no match in the other table, whereas a left outer join only removes results from the right table.
If a record in table1 has two matching records in table2, then the join will indeed have two rows. That's simply how joins work.
Could you post your whole query? The description of your problem looks like you didn't specify which field to join the table on. I don't know whether that would be syntax error in MySQL or not, though.
Martin Vajsar wrote:Could you post your whole query? The description of your problem looks like you didn't specify which field to join the table on. I don't know whether that would be syntax error in MySQL or not, though.
I think it would just return every row in table 1 duplicated for every row in table 2. (is this called a cartisian product?)
subject: MYSQL: a row with only one value the same in that field, extracted from table