Exercise 002: The Outer Join

Technorati tags: Outer Join, jet SQL, access, parameter query

Copyright statement: You can reprint it at will.Hyperlink formIndicate the source and author information of the following articles andThis statement

Author: Xixi

Source: http://blog.csdn.net/slowgrace/archive/2008/10/05/3016635.aspx

 

In other words, I wrote such a nested join in access:

1. Total select sum (tblrecipe_food.sngnutquant) as sngnutquant, tblnustan. idnustan, tblnustan.txt nuname
2. From tblmeal inner join (tblrecipe_food inner join (tblfood right join tblnutstan
3. On tblfood. lngnuttypeid = tblnutstan. idnutstan)
4. On tblrecipe_food.lngfoodid = tblfood. idfood)
5. On tblmeal. lngrecipeid = tblrecipe_food.lngrecipeid
6. Group by tblnutstan. idnutstan, tblnutstan.txt nutname, tblmeal. datemeal
7. Having (tblmeal. datemeal) = #10/6/2008 #));

Click data table view. The error message "connection expressions are not supported" is displayed ". If you change the last right join to inner join, no error is returned. The problem is that I want to retrieve all records in the tblnutstan table, whether or not there are associated records in tblfood, so I have to use right join.

The above SQL statements are not good-looking. I pasted the design view corresponding to access on the top. Note that the arrows between the two tables on the left represent a right link. Click the data table view in this view, and an error is returned: "The SQL statement cannot be executed because it contains an external connection with ambiguity. If you force one of the connections to be executed first, create another query to execute the first connection, and then include the query in the SQL statement ."

After reading this prompt, I decided to split it into two queries and try to write a query like this:

1. Select tblfood. lngnuttypeid, tblrecipe_food.sngnutquant
2. From tblmeal inner join (tblrecipe_food inner join tblfood on tblrecipe_food.lngfoodid = tblfood. idfood) on tblmeal. lngrecipeid = tblrecipe_food.lngregreeid
3. Where (tblmeal. datemeal) = #10/7/2008 #));

Name it zzqrydayprmfoodnutquan. The corresponding design view is as follows:

Then perform Outer Join Based on the query:

1. Select tblnustan.txt nutname, tblnustan. lngmin, tblnustan. lngmax, sum (zzqrydayprmfoodnutquan. sngnutquant) as total sngnutquant
2. From tblnutstan left join zzqrydayprmfoodnutquan on tblnutstan. idnutstan = zzqrydayprmfoodnutquan. lngnuttypeid
3. Group by tblnustan.txt nutname, tblnustan. lngmin, tblnustan. lngmax, tblnustan. idnustan
4. Order by tblnutstan. idnutstan;

The corresponding design view is as follows:

This is the case. I am also dizzy. It is not just a bit. I tried changing the subquery fields several times during this period. Remember that the total field and the condition field are exchanged. After that, I was very happy to think about how to write this SQL statement into the code. In the code, I want to get a date in real time and replace the above #. I don't know how to write a query based on a dynamically generated record set. Because select statements are used to search for data from objects already stored in the database, I estimate that a temporary table must be generated for the first query result first, and then left join the temporary table, I am not very familiar with these issues. The stored procedure with parameters may also be available, but I forgot about it.

So I finally did this: my husband made a dynamic recordset and obtained the data for the Outer Join. Then, I took the data of this record set (one Field) copy the data to a field in the tblnutstan table where all records are to be displayed one by one. Finally, all records are displayed in a form based on the tblnutstan table. In fact, I wanted to do this from the very beginning. I am familiar with all the links involved in this process and will soon be able to make it happen. I just suddenly came up with a good wish. I wanted a query to solve all the problems. I just tried it. I was exhausted. Finally, I hope it would be okay. This reminds me of my sleep when my teacher talked about the join statement at school. Alas, I guess I won't go to bed when I listen to this lesson today :~ (

P.s. With the help of tiger_zhao, the problem was finally solved. However, because the program has been tested in the above stupid way, so I am too lazy to go over it, so I will record the method here. There are two main points:

1. In the subquery for external connections, zzqrydayprmfoodnutquan does not include a parameter, that is, datemeal is not used as a condition field, but as a grouping field. In this way, zzqrydayprmfoodnutquan is not a query with parameters, but an object stored in the database without parameters. This query essentially finds all the records of the day.
2. In the outer join, zzqrydayprmfoodnutquan's datemeal field is used as the join condition for left join.

The design view of zzqrydayprmfoodnutquan is as follows:

The SQL statement is as follows:

1. Select tblfood. lngnuttypeid, sum (tblrecipe_food! Sngquantity * tblfood! Sngunitnutquant) as sngnutquantsum, tblmeal. datemeal
2. From tblfood inner join (tblmeal inner join tblrecipe_food on tblmeal. lngrecipeid = tblrecipe_food.lngrecipeid) on tblfood. idfood = tblrecipe_food.lngfoodid
3. Group by tblfood. lngnuttypeid, tblmeal. datemeal;

The SQL statement of zznutanna is as follows (note that the blacklist is not annoying ):

1. Select tblnustan.txt nutname, tblnustan. lngmin, tblnustan. lngmax, zzqrydayprmfoodnutquan. sngnutquantsum
2. From tblnutstan left join zzqrydayprmfoodnutquan on (tblnutstan. idnutstan = zzqrydayprmfoodnutquan. lngnuttypeid)And (zzqrydayprmfoodnutquan. datemeal) = #10/9/2008 #)))
3. Group by tblnustan.txt nutname, tblnustan. lngmin, tblnustan. lngmax, zzqrydayprmfoodnutquan. sngnutquantsum, tblnustan. idnustan
4. Order by tblnutstan. idnutstan;

Zznutanna's design view cannot be pasted out, because although it can query the correct results, the system says that the query cannot be displayed in the design view; I simply open this query after saving the disk, and the effect is even more surprising. It will flash the correct query results, then report the error "connection expressions not supported", and then close it. As a matter of fact, I can no longer open this query. Fortunately, I copied it to this blog post. So I am not very familiar with whether this method works in the Code. Let's talk about it later.