Left JOIN
-
I need to be able to pull information from two tables. The first table is the master table that contains the essential information about some object, wherease the second table has some other data related to that object. I need to pull information from the first table regardless of whether there is a match in the second table (class LEFT JOIN), but if there is a record in the second table, I need to pull data only from the latest record in the second table. So, the query would look something like: SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON table1.field1=table2.field1 WHERE ???? I have an autonumber field in the 2nd table that can be used to retrieve the latest record from table2. Could that be used to retrieve the latest record, and, if so, how? Thanks.
Your concept is correct, but a better table representation would help. What is the data in table2? What fields do you need if present and what is the autonumber field? Tim
-
I need to be able to pull information from two tables. The first table is the master table that contains the essential information about some object, wherease the second table has some other data related to that object. I need to pull information from the first table regardless of whether there is a match in the second table (class LEFT JOIN), but if there is a record in the second table, I need to pull data only from the latest record in the second table. So, the query would look something like: SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON table1.field1=table2.field1 WHERE ???? I have an autonumber field in the 2nd table that can be used to retrieve the latest record from table2. Could that be used to retrieve the latest record, and, if so, how? Thanks.
I believe what you are looking for is known as an "Outer Join".
-
Your concept is correct, but a better table representation would help. What is the data in table2? What fields do you need if present and what is the autonumber field? Tim
Thanks. Let's see. From table2 at present I only need to retrieve one field of table, so I don't actually need table2.* in the select statement. Rather, I would just need something like table2.VE. The data in table 2 is simply the results of some process run on the object described in table1. They are linked by some unique object identifier. I need to pull information about the object regardless of whether the process has been run or not. If the process has been run, I need to retrieve the VE field for that object. The basic problem is that there may be multiple records for the proces run on the object, so multiple records in table2 for the object it is linked to in table1. Some of those records may be from the same day, while others may be from 3 months, 6 months, or 10 years ago. I need the latest when pulling up the data. The autonumber field in table2 is called ID and the name of the field in table2 that I need to retrieve now is called VE (but I may need to retrieve other fields later). I appreciate the help.
-
I believe what you are looking for is known as an "Outer Join".
Thanks, but I believe LEFT JOIN and LEFT OUTER JOIN are interchangeable with the database I am using. I believe, though, that with the LEFT JOIN, if there is more than one record in the 'right' table that matches the fields in the left table, then the additional records are also included in the result set. What I need is just one particular matching record from the 'right' table; in this case, the last matching record from the 'right' table.
-
Thanks. Let's see. From table2 at present I only need to retrieve one field of table, so I don't actually need table2.* in the select statement. Rather, I would just need something like table2.VE. The data in table 2 is simply the results of some process run on the object described in table1. They are linked by some unique object identifier. I need to pull information about the object regardless of whether the process has been run or not. If the process has been run, I need to retrieve the VE field for that object. The basic problem is that there may be multiple records for the proces run on the object, so multiple records in table2 for the object it is linked to in table1. Some of those records may be from the same day, while others may be from 3 months, 6 months, or 10 years ago. I need the latest when pulling up the data. The autonumber field in table2 is called ID and the name of the field in table2 that I need to retrieve now is called VE (but I may need to retrieve other fields later). I appreciate the help.
-
Thanks, Tim. The query, modified to best match my situation, seemed to work as it should for the most part, though the result set still had more records than the number of records in table1. Table1 has 10080 records, whereas the result set from the query had 10087. I'll have to sort through it and find out where those additional records are coming from, but this helped me to get started. Thanks again.
-
Thanks, Tim. The query, modified to best match my situation, seemed to work as it should for the most part, though the result set still had more records than the number of records in table1. Table1 has 10080 records, whereas the result set from the query had 10087. I'll have to sort through it and find out where those additional records are coming from, but this helped me to get started. Thanks again.
You're welcome...
-
You're welcome...
Tim, I don't know if you're still paying attention to this thread, but this is what I tried based on your advice: SELECT Details.*, Results.VE FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber); It worked for the most part, but there were 7 more records in this result table from this query than are present in the 'LEFT' table. I scanned through the result table to find the duplications and it seems that the duplications were instances in the 'RIGHT' table where more than one record was present for the object stored in the left table. As I mentioned Friday, there can be more than one process result in the RIGHT table for the object stored in the LEFT table, but I only want the latest process result. In most cases, this query eliminates the duplicate process results, but if there were two process results with different VE data, then it seems the subquery in the following query returns both records. If you could help me understand why, I would appreciate it. Thanks. SELECT Details.*, Results.VE FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);
-
Tim, I don't know if you're still paying attention to this thread, but this is what I tried based on your advice: SELECT Details.*, Results.VE FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber); It worked for the most part, but there were 7 more records in this result table from this query than are present in the 'LEFT' table. I scanned through the result table to find the duplications and it seems that the duplications were instances in the 'RIGHT' table where more than one record was present for the object stored in the left table. As I mentioned Friday, there can be more than one process result in the RIGHT table for the object stored in the LEFT table, but I only want the latest process result. In most cases, this query eliminates the duplicate process results, but if there were two process results with different VE data, then it seems the subquery in the following query returns both records. If you could help me understand why, I would appreciate it. Thanks. SELECT Details.*, Results.VE FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);
You are getting multiple values because you are selecting VE, SerialNumber and ID from results. If you only want the lastest VE, remove SerialNumber from your query. That should give you only the latest VE value. If you have more questions, ask away. Tim
-
You are getting multiple values because you are selecting VE, SerialNumber and ID from results. If you only want the lastest VE, remove SerialNumber from your query. That should give you only the latest VE value. If you have more questions, ask away. Tim
Thanks, but I don't need just the latest VE, but the latest VE tied to that serial number. The VE field is simply a text field that can be PASS, FAIL, or an empty string; there is nothing remotely unique about it. I need the VE value, but it only matters insofar as it's "attached" to the serial number.