473,729 Members | 2,121 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Left Join and Where Statements

If I have two tables and need to do a left outer join and include a
where statement on the second table, it seems like the left outer join
becomes an inner join. For example:

Table: Names

id Name StatusCode
1 John 11
2 Henry 22
3 Bob 33
4 Sue 44

Table: StatusCode

StatusCode Modifier Description
11 Job Tech
11 Hobby Piano
22 Hobby Video
33 Job IRS

In Access, I need to combine the two tables to get
id Name Job
1 John Tech
2 Henry
3 Bob IRS
4 Sue

I assumed you could do this with a statement like
Select Names.*, StatusCode.Modi fier, StatusCode.Desc ription
>From Names LEFT JOIN StatusCode on Names.StatusCod e =
StatusCode.Stat usCode
Where StatusCode.Modi fier = "Job"
(I know the code syntax isn't quite correct, but access creates the
correct syntax and the question is more related to function).

Using this, the query seems to exclude Henry and Sue because they
don't have a Job. I don't get this...since it is a left outer join,
why are they being excluded? If I remove the where statement, it
displays all the names but the people like John are listed on two
rows, one with the Job and one with the hobby. Any ideas on how to do
this? Do I need to create a sub-query to first pull just the items
from the StatusCode table with a StatusModifier of Job? It seems like
this works, but I don't get why the left join isn't working.

Thanks,
Andrew V. Romero

Aug 31 '07 #1
3 2691
Hi, Andrew.
id Name StatusCode
Never use a Reserved word as a column name. Name and Description are
reserved.
Table: StatusCode

StatusCode Modifier Description
Never use the same names for a table and a column. You'll be sorry when you
start coding.
I don't get this...since it is a left outer join,
why are they being excluded?
It doesn't matter that the query is using a LEFT JOIN to gather the rows.
Your WHERE clause requires that only the rows from the query that have
Modifier = "Job" can be in the resulting data set. You eliminated all other
rows with your qualifier.

Instead, change the names of the table and the columns to avoid introducing
bugs, and use a subquery in the FROM clause (called an inline view) to get
only the rows you want. Try:

SELECT ID, FName, StatusDesc AS Job
FROM Names LEFT JOIN
(SELECT StatusCode, Modifier, StatusDesc
FROM StatusCodes
WHERE Modifier = 'Job') AS SC ON Names.StatusCod e = SC.StatusCode;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
Aug 31 '07 #2
On Aug 31, 1:34 am, "rrstud...@icqm ail.com" <rrstud...@icqm ail.com>
wrote:
If I have two tables and need to do a left outer join and include a
where statement on the second table, it seems like the left outer join
becomes an inner join. For example:

Table: Names

id Name StatusCode
1 John 11
2 Henry 22
3 Bob 33
4 Sue 44

Table: StatusCode

StatusCode Modifier Description
11 Job Tech
11 Hobby Piano
22 Hobby Video
33 Job IRS

In Access, I need to combine the two tables to get
id Name Job
1 John Tech
2 Henry
3 Bob IRS
4 Sue

I assumed you could do this with a statement like
Select Names.*, StatusCode.Modi fier, StatusCode.Desc ription>From Names LEFT JOIN StatusCode on Names.StatusCod e =

StatusCode.Stat usCode
Where StatusCode.Modi fier = "Job"
(I know the code syntax isn't quite correct, but access creates the
correct syntax and the question is more related to function).

Using this, the query seems to exclude Henry and Sue because they
don't have a Job. I don't get this...since it is a left outer join,
why are they being excluded? If I remove the where statement, it
displays all the names but the people like John are listed on two
rows, one with the Job and one with the hobby. Any ideas on how to do
this? Do I need to create a sub-query to first pull just the items
from the StatusCode table with a StatusModifier of Job? It seems like
this works, but I don't get why the left join isn't working.

Thanks,
Andrew V. Romero
The problem is in the where statement.
Try: Where StatusCode.Modi fier = "Job" OR StatusCode.Modi fier is null
All columns in the right side of the join will be null for rows that
don't have matching entries.

Aug 31 '07 #3
Hi.
The problem is in the where statement.
Try: Where StatusCode.Modi fier = "Job" OR StatusCode.Modi fier is null
All columns in the right side of the join will be null for rows that
don't have matching entries.
Unfortunately, that leaves out the rows where Modifier = "Hobby" or any
other string besides the desired "Job." Therefore, Henry, who doesn't also
have a job listed (unlike John, who has both a job and a hobby listed),
won't be included in the resulting data set.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
Aug 31 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2363
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not null default 0 auto_increment, a1 varchar(64) not null default '',
0
1802
by: Justin | last post by:
Hi. I decided to tidy up some of my queries, but I came across something that stumpt me: mysql> SELECT -> jobs.jobId, -> jobs.active, -> jobs.title, -> jobs.listed, -> industries.industry, -> occupations.occupation,
1
6927
by: Justin Hennessy | last post by:
Hi all, I am working with a computer hardware asset database and I am trying to get information out of it for each PC in my organisation. Here is the basic table structure: Table1 -> Table2 -> Table3 -> Table4 -> Table5
2
2742
by: Scott Snella | last post by:
Hi, I hope this is the right place to post, but, I am having a problem with an UPDATE command and a LEFT JOIN, I am using something like: UPDATE table_a LEFT JOIN table_b ON table_a.field1=table_b.field1 SET table_b.field6='1' WHERE table_a.field2='1'; Something along those lines. Anyway, from what I can see on various web sites, documentation, etc, I think it should work, however, I keep getting this error...
4
4106
by: jbm05 | last post by:
Hi, I'm curious about the computational complexity of a query I have. The query contains multiple nested self left joins, starting with a simple select, then doing a self left join with the results, then doing a self left join with those results, etc. What puzzles me is that the time required for the query seems to grow exponentially as I add additional left joins, which I didn't expect. I expected the inner select to return about 25...
7
31562
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
6
9031
by: Thomas Beutin | last post by:
Hi, i've a speed problem withe the following statement: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p LEFT OUTER JOIN ot_kat_prod AS pz ON ( p.p_id = pz.p_id ) WHERE p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37'; This is terrible slow compared to the inner join: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
2
1458
by: paulmac106 | last post by:
Hi, I have 2 tables: tblStatements and tblLines (one to many) Any tblStatements record can have many associated records in tblLines. The search criteria is against tblLines (ie tblLines.fldDateofService tblLInes.fldStatmentID (one to many) I need to return a recordset that will look like (after i pass the date range)
3
13417
by: torpecool | last post by:
Hello Everyone, I have been searching around for an answer to this question with no luck. I hope that some of you may have a couple of good ideas I could try. I am running MySQL 4.1.20. In one of my databases, I have two tables: patients, visits.
0
8925
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9206
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4797
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3240
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2692
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2166
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.