In order to expand my skills in SQL for a database I am working, I developed
a conceptual query with which I am having some difficulty. The problem can
be represented as three tables: tblApartments, tblResidents, tblPhone.
tblResidents links to tblApartments through a field called AptNum. Current,
past, and future residents are stored in tblResidents and catagorized by a
field called Status. Phone numbers (e.g. home, emergency, work, etc) are
linked to the appropriate resident via a field called ResID. tblApartment
contains details about the apartment like building number and floor.
Now let's say I want to get a list of names and work phone numbers for
people that lived in building #3. The query I came up with to get the names
looks like:
SELECT tblApartments.Building, tblApartments.AptNum,
tblResidents.Name, tblResidents.AptNum, tblResidents.Status
FROM tblApartments INNER JOIN tblResidents ON
tblApartments.AptNum = tblResidents.AptNum
WHERE tblApartments.Building = 3 AND
tblResidents.Status = "Moved"
What I can't get a handle on is how to add work phone numbers. I am
assuming a subquery is the correct construct and would be of the form
SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
FROM tblResidents INNER JOIN tblPhone ON
tblResidents.ResID = tblPhone.ResID
WHERE tblPhone.Type = "Work"
However, I don't have a clue how to integrate this into the overall query!
Am I barking up the wrong tree with this structure? If not, any help or
references to online FAQs or tutorials would be greatly appreciated. (I did
look at some on-line references, but it did not help!)
Any help will be greatly appreciated!!
Thanks!
DonDon wrote:
> In order to expand my skills in SQL for a database I am working, I develop
ed
> a conceptual query with which I am having some difficulty. The problem ca
n
> be represented as three tables: tblApartments, tblResidents, tblPhone.
> tblResidents links to tblApartments through a field called AptNum. Curren
t,
> past, and future residents are stored in tblResidents and catagorized by a
> field called Status. Phone numbers (e.g. home, emergency, work, etc) are
> linked to the appropriate resident via a field called ResID. tblApartment
> contains details about the apartment like building number and floor.
> Now let's say I want to get a list of names and work phone numbers for
> people that lived in building #3. The query I came up with to get the nam
es
> looks like:
> SELECT tblApartments.Building, tblApartments.AptNum,
> tblResidents.Name, tblResidents.AptNum, tblResidents.Stat
us
> FROM tblApartments INNER JOIN tblResidents ON
> tblApartments.AptNum = tblResidents.AptNum
> WHERE tblApartments.Building = 3 AND
> tblResidents.Status = "Moved"
> What I can't get a handle on is how to add work phone numbers. I am
> assuming a subquery is the correct construct and would be of the form
> SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
> FROM tblResidents INNER JOIN tblPhone ON
> tblResidents.ResID = tblPhone.ResID
> WHERE tblPhone.Type = "Work"
> However, I don't have a clue how to integrate this into the overall query!
> Am I barking up the wrong tree with this structure? If not, any help or
> references to online FAQs or tutorials would be greatly appreciated. (I d
id
> look at some on-line references, but it did not help!)
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Just include a join of the tblPhone to it's related table:
SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum)
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID
WHERE A.Building = 3
AND R.Status = "Moved"
AND P.Type = "Work"
I used a LEFT JOIN on the tblPhone in case a resident did not have a
Work phone number.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQh+Xr4echKqOuFEgEQJT3QCfRSFLOB36+5+e
0gJlQbDM46GtRGcAn05A
CBHjNQ63P/0GQ76Yy82VTWT/
=B/s0
--END PGP SIGNATURE--|||"Don" <someone@.somewhere.net> wrote in message
news:OdOgs73GFHA.3440@.TK2MSFTNGP10.phx.gbl...
> In order to expand my skills in SQL for a database I am working, I
developed
> a conceptual query with which I am having some difficulty. The problem
can
> be represented as three tables: tblApartments, tblResidents, tblPhone.
> tblResidents links to tblApartments through a field called AptNum.
Current,
> past, and future residents are stored in tblResidents and catagorized by a
> field called Status. Phone numbers (e.g. home, emergency, work, etc) are
> linked to the appropriate resident via a field called ResID. tblApartment
> contains details about the apartment like building number and floor.
> Now let's say I want to get a list of names and work phone numbers for
> people that lived in building #3. The query I came up with to get the
names
> looks like:
> SELECT tblApartments.Building, tblApartments.AptNum,
> tblResidents.Name, tblResidents.AptNum,
tblResidents.Status
> FROM tblApartments INNER JOIN tblResidents ON
> tblApartments.AptNum = tblResidents.AptNum
> WHERE tblApartments.Building = 3 AND
> tblResidents.Status = "Moved"
> What I can't get a handle on is how to add work phone numbers. I am
> assuming a subquery is the correct construct and would be of the form
> SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
> FROM tblResidents INNER JOIN tblPhone ON
> tblResidents.ResID = tblPhone.ResID
> WHERE tblPhone.Type = "Work"
> However, I don't have a clue how to integrate this into the overall query!
> Am I barking up the wrong tree with this structure? If not, any help or
> references to online FAQs or tutorials would be greatly appreciated. (I
did
> look at some on-line references, but it did not help!)
> Any help will be greatly appreciated!!
> Thanks!
> Don
>
I don't think a subquery is required for this, another INNER JOIN should do
it:
SELECT tblApartments.Building, tblApartments.AptNum, tblResidents.Name,
tblResidents.AptNum, tblResidents.Status, tblPhone.Number, tblPhone.Type
FROM tblApartments
INNER JOIN tblResidents ON tblApartments.AptNum = tblResidents.AptNum
INNER JOIN tblPhone ON tblResidents.ResID = tblPhone.ResID
WHERE tblApartments.Building = 3 AND tblResidents.Status = "Moved" AND
tblPhone.Type = "Work"
It's a matter of taste, but I use joins in preference to subqueries where
possible because I think they are easier to read. Also, I've read that the
Query Optimiser often handles joins better than subqueries.
Regards,
Simon|||On Fri, 25 Feb 2005 21:24:59 GMT, MGFoster wrote:
(snip)
>Just include a join of the tblPhone to it's related table:
>SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
>FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID
>WHERE A.Building = 3
> AND R.Status = "Moved"
> AND P.Type = "Work"
>I used a LEFT JOIN on the tblPhone in case a resident did not have a
>Work phone number.
Hi MGFoster,
In order for that to work, the P.Type = 'Work' shoould be in the ON
clause, not in the WHERE clause. (And you shouldn't use double quotes to
delimit string constants!)
SELECT A.Building, A.AptNum,
R.Name, R.AptNum, R.Status,
P.Number, P.Type
FROM Apartments AS A
INNER JOIN Residents AS R
ON R.AptNum = A.AptNum
LEFT JOIN Phones AS P
ON P.ResID = R.ResID
AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"MGFoster" <me@.privacy.com> wrote in message
news:LIMTd.5610$MY6.682@.newsread1.news.pas.earthlink.net...
> SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID
> WHERE A.Building = 3
> AND R.Status = "Moved"
> AND P.Type = "Work"
> I used a LEFT JOIN on the tblPhone in case a resident did not have a
> Work phone number.
I believe that if you want to see all residents even if they have no work
phone that you need to add the P.type condition to the Join clause as:
SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum)
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID and P.Type = "Work"
WHERE A.Building = 3
AND R.Status = "Moved"
Good Luck,
Jim|||James Goodwin wrote:
> "MGFoster" <me@.privacy.com> wrote in message
> news:LIMTd.5610$MY6.682@.newsread1.news.pas.earthlink.net...
>
>
> I believe that if you want to see all residents even if they have no work
> phone that you need to add the P.type condition to the Join clause as:
> SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID and P.Type = "Work"
> WHERE A.Building = 3
> AND R.Status = "Moved"
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hugo & Jim,
Double quotes: Yeah, I know. I move between Access (double quotes OK)
& SQL a lot & probably just got discombobulated.
P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
"hard coded" data in the JOIN's ON clause before, but I've always put,
what could be a parameter, in the WHERE clause. Is there any increased
efficiency in putting it in the ON clause rather than the WHERE clause?
If there is an efficiency increase, wouldn't that indicate that all
WHERE clause evaluations could be put into the join's ON clause?
I'm not advocating this just curious. Also, I'm too lazy to create some
test tables & data to look at the query's execution plan. ;-)
Thanks,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQh/ s1IechKqOuFEgEQJK3QCg+2KBLrUaTtXxbQ98L95
US9slC6IAn3rb
5PXNZ5Plgzrt65L864qdd7rX
=xIwu
--END PGP SIGNATURE--|||"MGFoster" <me@.privacy.com> wrote in message
news:j1STd.5822$MY6.1200@.newsread1.news.pas.earthlink.net...
> James Goodwin wrote:
work
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Hugo & Jim,
> Double quotes: Yeah, I know. I move between Access (double quotes OK)
> & SQL a lot & probably just got discombobulated.
> P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
> "hard coded" data in the JOIN's ON clause before, but I've always put,
> what could be a parameter, in the WHERE clause. Is there any increased
> efficiency in putting it in the ON clause rather than the WHERE clause?
> If there is an efficiency increase, wouldn't that indicate that all
> WHERE clause evaluations could be put into the join's ON clause?
> I'm not advocating this just curious. Also, I'm too lazy to create some
> test tables & data to look at the query's execution plan. ;-)
> Thanks,
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/AwUBQh/ s1IechKqOuFEgEQJK3QCg+2KBLrUaTtXxbQ98L95
US9slC6IAn3rb
> 5PXNZ5Plgzrt65L864qdd7rX
> =xIwu
> --END PGP SIGNATURE--
Hello -
In this case, LEFT OUTER JOIN tblPhone ON (R.ResID = P.ResID and P.Type =
'Work') is necessary to ensure that people who don't have work phones are
returned. If you put the P.Type = 'Work' condition in the overall WHERE
clause, then people who don't have work phones will not be returned, because
their P.Type value is null.
Alternatively, you could use the simpler JOIN condition make the condition
in the WHERE clause (P.Type = 'Work' OR P.Type IS NULL).
Regards,
Simon|||> "MGFoster" <me@.privacy.com> wrote in message
< snip >
< snip >
Simon Shearn wrote:
> Hello -
> In this case, LEFT OUTER JOIN tblPhone ON (R.ResID = P.ResID and P.Type =
> 'Work') is necessary to ensure that people who don't have work phones are
> returned. If you put the P.Type = 'Work' condition in the overall WHERE
> clause, then people who don't have work phones will not be returned, becau
se
> their P.Type value is null.
> Alternatively, you could use the simpler JOIN condition make the condition
> in the WHERE clause (P.Type = 'Work' OR P.Type IS NULL).
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Simon, Thanks for the info. It made me want to see how this works. So
I went ahead & created some test tables/data:
use tempdb
go
set nocount on
create table tblApartments (
building tinyint,
aptnum tinyint primary key
)
insert into tblApartments values (1,1)
insert into tblApartments values (1,2)
insert into tblApartments values (1,3)
insert into tblApartments values (2,4)
insert into tblApartments values (2,5)
insert into tblApartments values (3,6)
insert into tblApartments values (3,7)
insert into tblApartments values (3,8)
create table tblResidents(
ResID tinyint primary key,
[name] varchar(20),
aptnum tinyint ,
status varchar(10),
constraint fk_res foreign key (aptnum) references tblApartments
)
insert into tblResidents values (1,'dalton',1,'moved')
insert into tblResidents values (2,'liloo',2,'current')
insert into tblResidents values (3,'lucy',3,'moved')
insert into tblResidents values (4,'harry',4,'moved')
insert into tblResidents values (5,'sondine',5,'current')
insert into tblResidents values (6,'jean-baptiste',6,'moved')
insert into tblResidents values (7,'betty',7,'moved')
insert into tblResidents values (8,'cornelius',8,'current')
create table tblPhone (
ResID tinyint ,
Type varchar(5),
[Number] varchar(10),
constraint fk_phone foreign key (resid) references tblResidents
)
insert into tblPhone values (1,'work','055-1234')
insert into tblPhone values(2,'home','155-1234')
--insert into tblPhone values(3,'work','255-1234')--
insert into tblPhone values(3,'home','355-1234')
insert into tblPhone values(5,'work','455-1234')
insert into tblPhone values(6,'work','555-1234')
insert into tblPhone values(7,'home','655-1234')
--insert into tblPhone values(7,'work','755-1234')-- uncomment to get
betty's PH#
SELECT A.Building, A.AptNum, R.[Name], P.[Number] as Phone, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum ) -- and r.status = 'moved')
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
-- AND (P.Type = 'Work') -- OR P.Type IS NULL)
drop table tblPhone
drop table tblResidents
drop table tblApartments
set nocount off
Result set:
Building AptNum Name Phone Status
-- -- -- -- --
3 6 jean-baptiste 555-1234 moved
3 7 betty NULL moved
Which is correct, 'cuz betty & jean-baptiste are the only residents of
building 3 apts who have moved.
============
If you change the FROM & WHERE clause to this:
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum )
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID --AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
AND (P.Type = 'Work' OR P.Type IS NULL)
The result set is:
Building AptNum Name Phone Status
-- -- -- -- --
3 6 jean-baptiste 555-1234 moved
which means the "OR P.Type IS NULL" criteria doesn't pull betty's record
as you suggested it would.
This solves some problems I've had w/ LEFT JOINS not working as I had
anticipated. Thanks for the info.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQiEODIechKqOuFEgEQLr0ACgylrysm6ilcay
4mfQ1n/B5GcLw3oAoJ+9
DDimxetXGCEb6UEz2vEmaA4m
=oICZ
--END PGP SIGNATURE--|||"MGFoster" <me@.privacy.com> wrote in message
news:K68Ud.6547$873.1771@.newsread3.news.pas.earthlink.net...
> < snip >
> < snip >
> Simon Shearn wrote:
=
are
because
condition
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Simon, Thanks for the info. It made me want to see how this works. So
> I went ahead & created some test tables/data:
> use tempdb
> go
> set nocount on
> create table tblApartments (
> building tinyint,
> aptnum tinyint primary key
> )
> insert into tblApartments values (1,1)
> insert into tblApartments values (1,2)
> insert into tblApartments values (1,3)
> insert into tblApartments values (2,4)
> insert into tblApartments values (2,5)
> insert into tblApartments values (3,6)
> insert into tblApartments values (3,7)
> insert into tblApartments values (3,8)
> create table tblResidents(
> ResID tinyint primary key,
> [name] varchar(20),
> aptnum tinyint ,
> status varchar(10),
> constraint fk_res foreign key (aptnum) references tblApartments
> )
> insert into tblResidents values (1,'dalton',1,'moved')
> insert into tblResidents values (2,'liloo',2,'current')
> insert into tblResidents values (3,'lucy',3,'moved')
> insert into tblResidents values (4,'harry',4,'moved')
> insert into tblResidents values (5,'sondine',5,'current')
> insert into tblResidents values (6,'jean-baptiste',6,'moved')
> insert into tblResidents values (7,'betty',7,'moved')
> insert into tblResidents values (8,'cornelius',8,'current')
> create table tblPhone (
> ResID tinyint ,
> Type varchar(5),
> [Number] varchar(10),
> constraint fk_phone foreign key (resid) references tblResidents
> )
> insert into tblPhone values (1,'work','055-1234')
> insert into tblPhone values(2,'home','155-1234')
> --insert into tblPhone values(3,'work','255-1234')--
> insert into tblPhone values(3,'home','355-1234')
> insert into tblPhone values(5,'work','455-1234')
> insert into tblPhone values(6,'work','555-1234')
> insert into tblPhone values(7,'home','655-1234')
> --insert into tblPhone values(7,'work','755-1234')-- uncomment to get
> betty's PH#
> SELECT A.Building, A.AptNum, R.[Name], P.[Number] as Phone, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum ) -- and r.status = 'moved')
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID AND P.Type = 'Work'
> WHERE A.Building = 3
> AND R.Status = 'Moved'
> -- AND (P.Type = 'Work') -- OR P.Type IS NULL)
> drop table tblPhone
> drop table tblResidents
> drop table tblApartments
> set nocount off
> Result set:
> Building AptNum Name Phone Status
> -- -- -- -- --
> 3 6 jean-baptiste 555-1234 moved
> 3 7 betty NULL moved
> Which is correct, 'cuz betty & jean-baptiste are the only residents of
> building 3 apts who have moved.
> ============
> If you change the FROM & WHERE clause to this:
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum )
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID --AND P.Type = 'Work'
> WHERE A.Building = 3
> AND R.Status = 'Moved'
> AND (P.Type = 'Work' OR P.Type IS NULL)
> The result set is:
> Building AptNum Name Phone Status
> -- -- -- -- --
> 3 6 jean-baptiste 555-1234 moved
> which means the "OR P.Type IS NULL" criteria doesn't pull betty's record
> as you suggested it would.
> This solves some problems I've had w/ LEFT JOINS not working as I had
> anticipated. Thanks for the info.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQiEODIechKqOuFEgEQLr0ACgylrysm6ilcay
4mfQ1n/B5GcLw3oAoJ+9
> DDimxetXGCEb6UEz2vEmaA4m
> =oICZ
> --END PGP SIGNATURE--
Hello -
Yes, you're right - the alternative form I suggested only works when the
person involved has no phone of any kind, so putting the P.Type = 'Work' in
the join condition, as others suggested, is the correct way of doing it.
Regards,
Simon|||On Sat, 26 Feb 2005 03:28:15 GMT, MGFoster wrote:
(snip)
>P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
>"hard coded" data in the JOIN's ON clause before, but I've always put,
>what could be a parameter, in the WHERE clause. Is there any increased
>efficiency in putting it in the ON clause rather than the WHERE clause?
Hi MGFoster,
Sorry for the late reply. The flu managed to get me down; I'm now
struggling to remove as much as possible from my 400+ message backlog
before my headache forces me back to bed again. :-)
Anyway, Simon already pointed out that in the case of outer joins, the
choice to put things in the WHERE clause or the ON clause influences the
results.
In the case of INNER joins, there is no performance difference, so
choose what suits you best. My preference (and I know I'm not alone with
this) is to code the "proper" joining criteria (usually following the
defined foreign keys) in the ON and the filter criteria in the WHERE.
But that's just my personal preference.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
fogofogo:
YoungEngineer wrote: