Query involving Two Left Joins to the same table

[John_sc]

What I have is two tables. The first has a primary key ID, while the second has a compound key ID and Nbr. There is a one to many relationship between the two tables.



I create a query using QBE. I inserted the first table, the added the second table twice. I connected each table via the ID field using left joins.

I then used a constraint where in the first copy of table 2, the Nbr should equal 1. In the second copy of table 2 the constraint is Nbr = 2.
My result is not what I expected. I expected The ID to show for all records, and if there was a matching record and Nbr equaled 1 or 2 these values should show up.

What I got was only those records where the ID had two records, with nbr = 1 and nbr = 2.

So I did get

ID        table2.Nbr         table2_1.Nbr
3                    1            2
5                    1            2

I expected (and wanted)

ID        table2.Nbr         table2_1.Nbr
1                 1
2                 1
3                 1                 2
4                                   2
5                 1                 2

Here is the query

SELECT Table1.ID, Table2.Nbr, Table2_1.NbrFROM (Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID) LEFT JOIN Table2 AS Table2_1 ON Table1.ID = Table2_1.ID
WHERE (((Table2.Nbr)=1) AND ((Table2_1.Nbr)=2));

Can anyone indicate how to accomplish this and where I went wrong?

[CJ_London]

you need to cater for the nulls as well if you are putting criteria on the 'right' table

WHERE (((Table2.Nbr)=1 OR Table2.Nbr is null) AND ((Table2_1.Nbr)=2 OR Table2_1.Nbr is null));

[ssanfu]

if there was a matching record and Nbr equaled 1 or 2 these values should show up.

Maybe try changing the "AND" to "OR"

SELECT Table1.ID, Table2.Nbr, Table2_1.Nbr FROM (Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID) LEFT JOIN Table2 AS Table2_1 ON Table1.ID = Table2_1.ID
WHERE (((Table2.Nbr)=1) OR ((Table2_1.Nbr)=2));

[John_sc]

This response was for post #3

I think you misinterpretted what I was going for. If there are 5 records in table1, I only want exactly 5 rows out.
This did not work.

What I get is

ID        table2.Nbr         table2_1.Nbr1                 1                 1
2                 1                 1
3                 1                 1
3                 1                 2
3                 2                 2
4                 2                 2
5                           1                            1
5                           1                            2
5                            2                           2 2

[John_sc]

Sorry, I replied out of order. This response was for Post #2, adding the nulls to the constraints.

I still do not get the rows with nulls for some reason. i am only getting the rows with the first value = 1 and the second value = 2

[John_sc]

Database7.accdb

Attached is a sample database which illustrates my problem. It has the two tables and the query.

I am still not getting what I want.
I tried to simply things by not including the second copy of the second table

Table1 just has the ID field with 4 records, with ID - 1, 2, 3, and 4
Table 2 has two fields of ID and Nbr, which combined are the primary key. The 4 records I have are

[Table1]
ID
1
2
3
4

[Table2]
ID        Nbr
1        1
2        2
3        1
3        2

To simply things I am just going to look to see which ID has a Nbr #1 (so I am not putting in table2 twice here). I do want to see all IDs. So I want the following output

ID      Nbr
1        1
2
3        1
4

With the following SQL

SELECT Table1.ID, Table2.Nbr
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE (((Table2.Nbr)=1 Or (Table2.Nbr) Is Null));

I get the following. Note, the ID #2 is not shown

ID        Nbr
1         1
3         1
4

ID #2 record has a value in Table 2, but it is not 1, and does not show up in the output (but I want it to show). Note that ID #4 is not in table 2 but does show up.

[orange]

Can you try again to tell us WHAT you want as a result set?

This will give you 5 rows, but I don't know what you want.

SELECT Table1.ID as  a, Table2.id,Table2.Nbr as b
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE (Table2.Nbr) is null or table2.nbr is not null

a	id	b
1	1	1
2	2	2
3	3	1
3	3	2
4

[John_sc]

Let me give you the underlying problem. I was given an unnormalized table. It had an ID, Name, Address1, City1, State1, Address2, City2, State2, ... Address10, City10, State10. I normalized it to have two tables. The first has just ID, Name, and the second has ID, Nbr, Address, City, State, where Nbr captures which number block the value is in. From the normalized tables, I was trying to get a flat file displayed, in effect rebuilding the content of the original unnormalized table. So I only want each ID shown once, and I wanted to display the address info if it was in the original table.

To simplify the problem, I am just trying to display the address in the first block (i.e., nbr = 1). The problem I am getting is if there is No nbr = 1 but there is a nbr = 2 for the ID, the row is not showing up. So in my case i want the query result to be

ID       nbr
1         1
2        null
3         1
4        null

eventually I would add additional table2s to the query with the constraint nbr = 2, nbr = 3, ... , nbr = 10. But right now I can not get the query to work the way I want with just the two tables.

I hope that clears things up.

[orange]

I guess it's just me, but I don't understand your post. It hasn't been a good morning --I 'm having difficulty finding files I copied yesterday. It seems win 10 has done some housekeeping that I don't believe I requested.

Anyway, I'm not following this:

To simplify the problem, I am just trying to display the address in the first block (i.e., nbr = 1). The problem I am getting is if there is No nbr = 1 but there is a nbr = 2 for the ID, the row is not showing up. So in my case i want the query result to be

Perhaps an example with address/block etc to match the description would help?? ID and nbr add to the confusion (in my view). Or, 5 records from your real data and an example of what you want from those records.

[John_sc]

Ok, here is the database with the underlying problem. i inserted a third table which was the original table. In my case I actually have 10 sets of columns (Address, City, State), and not the 3 shown here.

I also gave you a second query which shows the result I am looking for drawn from the OriginalData Table. If you compare the results from query 1 & 2, you will see the the query from the normalized tables does not include row 2.

Database7b.accdb

[Micron]

You're probably going to think I'm an idiot even though I've read, re-read and re-re-read your posts and looked into your db, because I came up with this:
SELECT OriginalData.ID, OriginalData.Address1 FROM OriginalData;
In my defense, I'm assuming you want the actual value that's in the second field and not 1's or the word null. Often people substitute bits of information with something that they think simplifies the issue, but it often directs us poor souls down the wrong path. So feel free to correct me.
However, if it is what you want, then I think your reply will be that you don't see how to get the other 10 or so sets of data from the rest of the table. Without seeing what you really have, I can only speculate that a set of Union queries would be the answer.

[John_sc]

I appreciate your patience, and apologize if I have not been clear. I tried to boil the problem down to the absolute minimum to try to identify the problem.

I think I did find a solution. But I am not sure why my original approach would not work. Let me review, and I will give my solution at the bottom.

• I was given a flat file which repeated entries in each row, numbering each entry (i.e., Address1, Address2, Address3, ..., Address10)
• I normalized the flat file by creating two tables. The first had the content that appeared once (i.e., ID, FName), and the second had a field to hold the repeating values, along with a pointer to indicate where the values occurred in the flat file. There were nulls randomly throughout the list of values. So there might have been an Address2 even if there was no Address1.
• I wanted to be able to rebuild the flat file from the normalized tables. so each ID row would appear exactly once in the output.
• To test out the query, I focused only on outputting the Address1 results. So I used a left join and added the constraint nbr = 1.
• This worked properly except whee there was no value for Address1, but there were values for other Addresses in the flat file.

• What is confusing me is that the Left Join is suppose to give me all the rows in Table1 (which are all the IDs in the table), and only those fields that match from the connected table. So I don't know why I don't get 4 rows output when there are 4 rows in Table 1.
• I also don't understand why I get row 4 (where there is no corresponding rows in table2) but don't get row 2 where there is a row with ID=2 in table2.

I do not want to use my OriginalTable which is the flat file even though it is giving the correct result. I was really trying to stay away from non-normalized tables.

My solution that works

I created multiple queries, one for each address block. For my sample database I have 2 queries (q1 and q2), which only has Table2 and a constraint on Nbr. So for q1, Nbr = 1. For q2, Nbr = 2
Then I Left joined the queries to Table1. In my full database, I will need 10 subqueries. See attached database

Database7c.accdb

I am still confused why the original query did not work.

Thanks to all that tried to help.

[Micron]

What is confusing me is that the Left Join is suppose to give me all the rows in Table1 (which are all the IDs in the table), and only those fields that match from the connected table.

You have this

SELECT Table1.ID, Table2.Nbr, Table2.Address FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE (((Table2.Nbr)=1 Or (Table2.Nbr) Is Null));

You get this

ID	Nbr	Address
1	1	111 Apple
3	1	333 Clark
4

because they are the only values where Nbr is 1 or Null. You cannot get ALL the rows from 1 if you're going to put criteria on 2. You have not quoted the join clause correctly. It is not "and only those fields that match". It is "where the joined fields are equal". I guess what's important is that you got it to work, but I have no clue if you dismissed the Union query suggestion and opted for 10 subqueries instead.

[John_sc]

Thanks for the reply. I will need to think about what you said. In the QBE editor for the link, I am picking option 2 which states "Include All records from 'Table1' and only those records from 'Table2' where the joined fields are equal." It is not returning "All records from Table1". I am thinking the issue is a sequencing issue, regarding when the constraint is implemented. I am not seeing why I am getting ID = 4, which is not in table2 at all, and not ID=2. It just seems odd, and points to the importance of validating your queries.

Regarding your suggestion about Unions. I did not see how to do this efficiently given the data that I had. The data could have multiple blank addresses, so I would need to have a separate query to handle each possibility. It seemed that I would need to union a huge number of queries. Any and all combinations of blank Addresses among the 10 possible slots would need to be handled. With that approach I would need to union 1003 queries

• a query where there all 10 addresses are given for nbr = 1 to 10
• Combination (10 pick 1) = 10 queries where one of the addresses is missing
• Combination (10 pick 2) = 45 queries where two of the 10 addresses are missing
• Combination (10 pick 3) = 120 queries where three of the 10 addresses are missing
• Combination (10 pick 4) = 210 queries where four of the 10 addresses are missing
• Combination (10 pick 5) = 252 queries where four of the 10 addresses are missing
• Combination (10 pick 6) = 210 queries where four of the 10 addresses are missing
• Combination (10 pick 7) = 120 queries where three of the 10 addresses are missing
• Combination (10 pick 8) = 45 queries where two of the 10 addresses are missing
• Combination (10 pick 9) = 10 queries where one of the addresses is missing

There may be another way of doing this union, but I did not see it. I very much appreciate your input. It did move me toward a solution.

[CJ_London]

just taken a look at your 'working' query and data provided, a crosstab would do the same (assuming your data has not been too simplified)

TRANSFORM First(Table2.Address) AS FirstOfAddress
SELECT Table1.ID
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
GROUP BY Table1.ID
PIVOT Table2.Nbr