Help with special query, confused
in [General]
|
From: tonino-fasolino on 30 Apr 2010 05:15 "Angela" <imsguy(a)gmail.com> schrieb im Newsbeitrag 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: Steve on 30 Apr 2010 17:18 Angela, Without seeing the entire schema, I can't really suggest how to fix this. With relational databases, every table must have a primary key that is unique for each record. To link a secondary table to the first, it must contain a foreign key which matches the primary key in the first table. There can be multiple instances of the foreign key in the secondary table. Have a look at this example: http://www.geekgirls.com/databases_from_scratch_3.htm It gives a fairly high level overview of some of the problems encountered when designing a relational database. "Angela" <imsguy(a)gmail.com> wrote in message news:89e076ad-18e5-4abc-8229-c74a51eb08da(a)p2g2000yqh.googlegroups.com... > 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.
From: KARL DEWEY on 3 May 2010 19:26
Maybe this is what you want -- SELECT Table1.YourDate, Table1.Number, Table1.Product FROM Table1 LEFT JOIN Table2 ON Table1.Number = Table2.Number WHERE Table1.YourDate >= Table2.YourDate AND Table2.Product = "Done" ORDER BY Table1.YourDate, Table1.Number, Table1.Product; -- Build a little, test a little. "Angela" wrote: > 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. > . > |