0

I have these tables in MS Access :

Tables Schema

I need a special join query to give me result like following:

list of all words in table 'pat_Words' that have synonyms in table 'pat_Synonyms'

Keen         eager,enthusiast,avid

Shelter      refuge,sanctuary
mohammadkad
  • 105
  • 1
  • 10
  • 1
    You need VBA t make this work in MS Access, unfortunately, VBA only works in an Access environment check out http://stackoverflow.com/questions/92698/combine-rows-concatenate-rows/93863#93863 – Fionnuala May 31 '16 at 11:38
  • @Fionnuala wow,Thanks that's so useful. – mohammadkad Jun 01 '16 at 03:47

1 Answers1

0

In mysql you can do this

select word, group_concat(synonym) from pat_Words join pat_synonyms on pat_Words.word_id = pat_synonyms.word_id group by pat_Words.word_id

See http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

Chris Lear
  • 6,592
  • 1
  • 18
  • 26
  • Thanks,I like your answer but what about MS access? – mohammadkad May 31 '16 at 10:35
  • Maybe add an MS Access tag to the question - it's helpful to let people know what DBMS you are using when asking questions like this. – Chris Lear May 31 '16 at 10:38
  • Try this: http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16&SID=453fabc6-b3z9-34z6zb14-a78f832z-19z89a2c.html. I have no idea whether it works. – Chris Lear May 31 '16 at 10:40