INNER JOIN

[bobicles2]

Select * FROM "Patient"

INNER JOIN "GP"

ON Patient.GP_Name = GP.GP_Name

ORDER BY Patient.Name

 

 

My Tables are

 

Patient (

  "N.I.Number" character varying(100) NOT NULL,

  "Name" character varying(100) NOT NULL,

  "Address" character varying(200) NOT NULL,

  "GP_Name" character varying(100) NOT NULL,

 

GP

"ID" serial NOT NULL,

  "GP_Name" character varying(100),

  "Practice" character varying(100),

  CONSTRAINT "GP_pkey" PRIMARY KEY ("ID")

 

 

Want to create a query that produces an alphabetic list of all patients for a GP

 

 

been stuck for hours now

 

thanks

[F1Fan]

Looks like that's what you have. What errors are you getting?

[btherl]

If you want a list of patients for one specific GP:

 

Select * FROM "Patient"
WHERE GP_Name = 'Dr Btherl'
ORDER BY Name

 

If you want to do the same but selecting by the gp's id then you will need the join:

 

Select * FROM "Patient"
INNER JOIN "GP"
ON Patient.GP_Name = GP.GP_Name
WHERE GP.ID = 5
ORDER BY Patient.Name

 

I should mention it's a bit odd to be using GP_Name for joining when GP has an ID column.  Usually you would put the id column in the patient table and join on id.  The reason is that if a GP's name changes, you just need to update the GP table only.