Using Multiple Tables.

[bobicles2]

Thes are my Tables

 

Patient - ID, Name, Address, GP

GP - ID, Name, Hospital Specialisation

 

Looking to produce a query that selects all the Patients for a particular GP, im not sure how to Query Multiple tables

 

Thanks Rob

[btherl]

Typically you'll use a join for that, though you can use a subquery.  Most SQL tutorials will cover that.  Are you definitely using postgres and not mysql?

[F1Fan]

SELECT a.ID, a.Name AS PatientName, a.Address, a.GP, b.Name AS GPName FROM Patient a, GP b WHERE a.GP = b.ID

[bobicles2]

Typically you'll use a join for that, though you can use a subquery.  Most SQL tutorials will cover that.  Are you definitely using postgres and not mysql?

 

Eerm...the report its for says

 

"The implementation should include appropriate use of the advanced features of PostgreSQL"

 

But ive just implemented the tables using phpmyadmin? so im not 100% of the differences? perhaps this could be something i may need to learn more about?

 

[btherl]

Aha .. if they're asking you to use postgresql, then you can't be using phpmyadmin.  That's for the more common database package called Mysql.

 

Judging by the wording, this is a report you are doing for a class?  In which case your teacher should be able to tell you how you're expected to get access to Postgresql to complete it.

 

It's difficult for me to comment more as I'm not sure how you're supposed to be accessing postgresql, and I'm not sure what features are considered "advanced"   Possibly a join is considered advanced if it's a basic database class.  Join basically means "Match table 1 and table 2 up wherever column a from table 1 matches column b from table 2".  It can get more complex but that's the basic meaning.  In your case you want to match the GP column from Patient table with the ID column from GP table, probably.

 

You can write it as F1Fan wrote it above, or here's an alternative:

 

SELECT *
FROM Patient
JOIN GP ON (Patient.GP = GP.ID)

 

Here I'm using "*" instead of listing columns, and I'm using another syntax for joins, which I find easier to understand.  And I'm not aliasing the tables.