| Author |
Topic |
|
rodnew75
Starting Member
8 Posts |
 Posted - 2010-12-15 : 18:08:42
|
| I have a table with the names of GP and what clinic they work in. It's layout is:IDFirst NameLast NameClinicAddressI also have a table with the clinic detailsClinic IDClinic NamePhone Number Fax NumberThe number of GP's at a particular clinic varies from 1 to 30.I am wanting to generate a query that will provide a clinic name and the GP's who work at that clinic. Have tried a number of different statements but find it difficult.Hopefully the outcome will generate something like:Clinic Name, GP1, GP2, GP3 etc. on the same row.thanks for any suggestions or help you can provide. I am working on Openoffice Base.Rod |
|
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2010-12-15 : 18:52:23
|
| Databases don't normally work that way, because of normalization. It it will only returnClinicA Doctor 1ClinicA Doctor 2ClinicB Doctor 1ClinicB Doctor 3If what you are looking for isClinicA Doctor 1 Doctor 2ClinicB Doctor 1 Doctor 3That's not going to happen unless you write some really fancy code involving row_number and a bunch of sub-queries and aggregate functions.If you give some sample data I may be able to help you. But the short answer is: it doesn't work that way. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rodnew75
Starting Member
8 Posts |
Posted - 2010-12-15 : 19:58:10
|
| I have developed a query with the outcome you have mentioned Conrad. The output I get is ok just trying to improve it. I will have a look at Pivot tables and see what I come up with. Thanks for your help |
|
|
|
rodnew75
Starting Member
8 Posts |
Posted - 2010-12-15 : 20:28:58
|
| Actually, is there a better way to set up the tables to get the desired outcome. One outcome is for a list of doctors surgeries with contact details, the other outcome is a mailing list for the doctors - ie. doctors name, clinic, address |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2010-12-15 : 23:55:06
|
| do you need table design? |
|
|
|
rodnew75
Starting Member
8 Posts |
Posted - 2010-12-16 : 00:01:13
|
| Yes, I suppose I do. Since it is too difficult to develop a query I am thinking that I may be able to fix my actual design |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2010-12-16 : 00:40:33
|
| mainatain seperate tables for:Doctors,Clinics,Address,Surgeries.because in your scenario it is one to many relation.so normalizing the tables would solve your problem.Still if you did'nt get me you can ask. |
|
|
|
jessiefun
Starting Member
35 Posts |
Posted - 2010-12-16 : 01:51:53
|
| Acually this design is pretty good. And what you need is a inner join and group by. :) |
|
|
|
rodnew75
Starting Member
8 Posts |
Posted - 2010-12-16 : 02:10:47
|
| Sorry Ahmed, you have lost me. Do you mean have a doctors name only table with a key, clinic name (with phone numbers) and a key, address details with a key and then associate them? And Jessie, do you mean my design is good or the one ahmed described? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-12-16 : 02:22:21
|
Your design is ok so far.Your problem is a "display-problem" only and should be handled in the front end.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jessiefun
Starting Member
35 Posts |
Posted - 2010-12-16 : 04:11:54
|
Yeah, i prefer to your design  |
|
|
|
rodnew75
Starting Member
8 Posts |
Posted - 2010-12-16 : 20:35:47
|
| The question I have now is regarding how to use the group by clause when dealing with letters. The examples I find always seem to include integers and a sum function |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rodnew75
Starting Member
8 Posts |
Posted - 2010-12-17 : 00:09:10
|
| ClinicName GPFullnameAmberley Park PadmaAmberley Park XiaoAmberley Park AshokBerwick Family LesBerwick Family CaroliThis is a table I produce using a query. I am wanting to then group each GP into one cell per clinic. There are about 30 clinics, and at least 190 doctors. So the output I am hoping to get is:ClinicName GPAmberley Park Padma, Xiao, AshokBerwick Family Les, Janet, Caroline |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rodnew75
Starting Member
8 Posts |
Posted - 2010-12-18 : 01:55:58
|
| I have looked at PIVOT but don't understand how this works with text. Is someone able to provide a sample or example code on how to configure SQL for a pivot table of similar format to what I have described. |
|
|
|
|
Query to collate list of doctors