SQL - select from table - Knowledgebase

Portal Home > Knowledgebase > Articles Database > SQL - select from table

SQL - select from table

Posted by scal, 12-27-2011, 06:41 PM
Hi , Please find attachment " ER " now if i want to select all person live in state i will use : SELECT * FROM Person WHERE idState = 1 but if i want to select all person live in a countery ? what i should be do ? Attached Thumbnails
Posted by topgun, 12-27-2011, 08:16 PM
SELECT * FROM Person left join state on person.idstate = state.idstate left join country on state.idcountry = country.idcountry WHERE idCountry = 1
Posted by scal, 12-27-2011, 08:21 PM
thank you , i will try this now .. and i want to ask , can we do this using inner join ?
Posted by foobic, 12-27-2011, 09:59 PM
For what you're doing (selecting all persons from a certain country), an inner join should work equally well. Where you'd really want a left (outer) join is if you were searching for a Person by name and also wanting to collect other information on him / her. Then if idState was missing or not matched to entries in the state / country tables, your search would still find the person.
Posted by Grumps, 12-27-2011, 10:58 PM
SELECT * FROM Person left join state on person.idstate = state.idstate left join country on state.idcountry = country.idcountry WHERE idCountry = 1 is same as SELECT * FROM Person left join state on person.idstate = state.idstate WHERE state.idcountry = 1
Posted by scal, 12-28-2011, 05:20 AM
thank you , and some one tell me if i add ( idcountry as PK & FK on state table ) i can using : select * from person where idcountry = 1 is this true ?and if that .. how can i do this ? thank's alot ..
Posted by scal, 12-28-2011, 03:32 PM
up .......
Posted by topgun, 12-28-2011, 04:07 PM
Foreign Keys are just for managing how updates/deletes cascade down child objects so you don't end up with orphan rows. You will always have to do some kind of join or subquery to select persons by country.
Posted by scal, 12-28-2011, 04:44 PM
hi , can you tell me please how to get it using a subquery ?
Posted by topgun, 12-28-2011, 04:51 PM
SELECT * FROM Person where idState in (Select idCountry from state where idCountry = .. ) You might need to alias the countries (select idCountry as countries from ..) I can't remember.
Posted by VectorVPS, 01-06-2012, 02:23 AM
Should be fine without aliasing, but you have a slight error in the subquery. In order to select all Persons that live in the country whose idCountry is 1: