Help with special query, confused
in [General]
|
From: Angela on 30 Apr 2010 02:14 Hello, I have two tables. Table1: Date/Time Number Product 12/03/2009 7870055 PLANA 12/05/2009 7870055 PLANA Table2: Date/Time Number Product 14/04/2009 7870055 Done Well as one can see, the number is common in both tables. I would like to return a query with following result. Result: Date/Time Number Product 12/05/2009 7870055 PLANA My guess is the date column has to play an important role here but how, that is confusing. Thanks.
From: Steve on 30 Apr 2010 02:46 Angela, What do the two tables represent? Are there any other fields involved? The schema of the tables is identical, so it's not clear from your example why you even have two tables. Your query result looks like it came from Table 1 only, but you didn't list what the query was. Also, what field is unique in each table? Typically, that will be the field that you use to link the tables. Could you post the full schema for both tables and provide a little more detail about what you are trying to query? "Angela" <imsguy(a)gmail.com> wrote in message news:1b44371e-f214-4924-8ce0-bf261d0933f9(a)b18g2000yqb.googlegroups.com... > Hello, > > I have two tables. > > Table1: > Date/Time Number Product > 12/03/2009 7870055 PLANA > 12/05/2009 7870055 PLANA > > Table2: > Date/Time Number Product > 14/04/2009 7870055 Done > > > Well as one can see, the number is common in both tables. > I would like to return a query with following result. > > Result: > Date/Time Number Product > 12/05/2009 7870055 PLANA > > My guess is the date column has to play an important role here but > how, that is confusing. > > Thanks.
From: Angela on 30 Apr 2010 03:10 Hello Steve, The actual scheme is around 50 columns. The unique column is product. First comes table1, The date suggests the order. It is not logical to have a "Done" for a date in table2 that comes after the date in table 1 April comes after March.. so line of April should match with March but April is incorrect after the month of May.. so in query result I should hopefully the line of May. For the sake of example, I have given different dates. Date for "Done" can be same for date of "plan". This way we would be able to identify for which plans, we did not have a done line. Infact we would have two queries. One to show the matchs & one to show the left overs.
From: Steve on 30 Apr 2010 03:39 Angela, I would need to see the columns in both tables to understand what the tables are for. If Table1 is for listing your products, then I would name the table Products. If Table 2 is for product status, then maybe you could call it Status. In your example, the Product column does not contain unique values, so can't be used as a primary key for queries. I also do not understand why you would have the same Product listed with different date values. What is this table for? I think you have some schema problems to resolve before you can fix your queries. "Angela" <imsguy(a)gmail.com> wrote in message news:6330d410-839d-4665-abe0-1988bec9b48e(a)w36g2000yqw.googlegroups.com... > Hello Steve, > > The actual scheme is around 50 columns. > > The unique column is product. > > First comes table1, The date suggests the order. > It is not logical to have a "Done" for a date in table2 that comes > after the date in table 1 > > April comes after March.. so line of April should match with March but > April is incorrect after the month of May.. so in query result I > should hopefully the line of May. > > For the sake of example, I have given different dates. Date for "Done" > can be same for date of "plan". > > This way we would be able to identify for which plans, we did not have > a done line. > > Infact we would have two queries. > > One to show the matchs & one to show the left overs.
From: Angela on 30 Apr 2010 03:58
Hey Steve, Thanks for writing. Well the unique identifier is a problem. Just a thought, is it possible to create a query with table1 with an additional column that would add a count to the occurance of Number column. Like the query will look like Date/Time Number Product Occurance 12/03/2009 7870055 PLANA 1 12/05/2009 7870055 PLANA 2 and then match it with table2 with similar treatment Date/Time Number Product Occurance 14/04/2009 7870055 Done 1 Well you can name anything to the tables since its an example. First table is for plan. Second table is the status. |