Mysql 'distinct' (unique)

Soldato
Joined
24 Nov 2002
Posts
16,378
Location
38.744281°N 104.846806°W
I have a table with three fields: "id", "desc" and "db".

I want "id" and "db" to be the composite primary key, however the table as it stands at the moment is not unique.

I want to create a new table with only unique values.

The difficulties I'm having is id on its own doesn't have to be unique, neither does db on its own... it is the id/db combination that is unique.

Any pointers?
 
matja said:
Or am i getting the wrong idea...
Nope, you've not got the wrong idea. But,

matja said:
how about fixing the values in your current table so that they are unique composite primary keys
Is what I'm stuck on. Ofcourse I could do it in excel, but I wanna do it directly in SQL.

In pseudocode, what I want is "select all rows where id+db (combined) = unique". As id isn't unique, and db definately is, but the combination should be.
 
matja said:
you could use concat() and seperate the column values with a value which doesn't appear in the data - eg : select distinct concat(id,'|',db) from t;

I can use the above to build a new table and I can include the missing column (desc) :)

Cheers


EDIT -Can anyone make the below syntaxically correct?
db_ref has "id, db and def", db_reg_bck has "id, db" but no def.

Code:
SELECT db_refs.`id`, db_refs_bck.`desc`, db_refs.`db` FROM db_refs LEFT JOIN db_refs_bck ON concat(db_refs.id,'|',db_refs.db) = concat(db_refs_bck.id,'|',db_refs_bck.db)
 
Last edited:
Back
Top Bottom