php - Mysql sorting concated values -


i have 3 mysql tables (animal, category , animal_2_category). every animal can have 0-n categories (for example fish, pet, insect, carnivore, "can fly", etc.).

table "animal" (id, name)

1, dog 2, cat 3, bee ...

table "categories" (id, name)

1, pet 2, insect 3, mammal 4, fish 5, can fly ...

table "animal_2_category" (animal_id, category_id)

1, 1 1, 3 2, 1 2, 3 3, 2 3, 5 ...

what need now, list of category combinations. following query works:

select concat_ws("-", x.name, c.name) animal_2_category a2c1      join animal_2_category a2c2 on a2c1.animal_id = a2c2.animal_id     join category c on c.id = a2c2.category_id´     join categories x group a2c2.category_id

this query wil return following:

  • pet-mammal
  • mammal-pet
  • insect-can fly

the problem is, dublicate entries "pet-mammal" , "mammal-pet". how can modify query, 1 of them, example:

  • pet-mammal
  • insect-can fly

you can rewrite query as

select  distinct greatest(concat_ws("-", x.name, c.name),concat_ws("-", c.name, x.name)) col animal_2_category a2c1      join animal_2_category a2c2 on a2c1.animal_id = a2c2.animal_id     join categories c on c.id = a2c2.`category_id`     join categories `x` on a2c1.category_id = x.id     x.name <> c.name

demo


Comments

Post a Comment

Popular posts from this blog

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more

ruby - Net::HTTP extremely slow responses for HTTPS requests -

for reason, on development machine i'm getting very, slow responses https requests performed via net::http. i've tried restclient , httparty , both have same issue. seems have sprung nowhere. i've made these requests hundreds of times without issue, today unbearably slow. pry(main)> puts time.now; httparty.get('https://api.easypost.com/v2/addresses'); puts time.now; 2015-04-29 08:07:08 -0500 2015-04-29 08:09:39 -0500 as can see, response took 2.5 minutes. , it's not easypost api url. i've tried numerous ssl requests servers know can connect ( https://google.com , https://weather.com , etc.) , result in same behavior. also, noticed same thing happens requests redirected http https. now, check out non-ssl request: pry(main)> puts time.now; httparty.get('http://lookitsatravis.com'); puts time.now; 2015-04-29 08:12:22 -0500 2015-04-29 08:12:22 -0500 instantaneous. gives? guess configuration problem somewhere between ruby , openssl. i...
Read more

c++ - Boost Spirit Parser with a vector of three strings compiling into a struct, adapt not working -

i´m student , need write parser in c++ boost-library. therefore write grammer in qi because need parse struct. far, good. i give example-code. think easier writing whole program down. description: first take txt-file , read it, parser goes on it, says "parsing ok!" , parse struct. our output struct in console. that works fine, code examples. here can see grammar in boost spirit qi: subject %= lexeme[lit("fach: ") >> +(char_("a-za-z")) >> lit("\n")]; //works! dozent %= lexeme[lit("dozent: ") >> +(char_("a-za-z")) >> lit("\n")]; date %= lexeme[lit("datum: ") >> digit >> digit >> lit("-") >> digit >> digit >> lit("-") >> digit >> digit >> digit >> digit >> lit("\n")]; count %= lexeme[lit("anzahl: ") >> +digit >> lit("\n...
Read more