Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
i have 2 tables
puf : id_farm,dataora,cod,nume,tip,id(key)
iep : id_farm,dataora,cod,nume,tip,id(key)
in puf i have :
'500','2010-10-10','1','nume1','tip1','1'
'500','2010-10-10','2','nume2','tip2','2'
'500','2010-10-10','3','nume3','tip3','3'
'500','2010-10-10','4','nume4','tip4','4'
'500','2010-10-10','5','nume5','tip5','5'
in iep i have :
'500','2010-10-11','1','q','tip1','1'
'500','2010-10-11','2','z','tip2','2'
'500','2010-10-11','3','i','tip3','3'
'500','2010-10-12','1','q','1','4'
'500','2010-10-13','2','z','2','5'
What i need is a select that will return this dates based on data given
for 2010-10-10 :
'500','2010-10-10','1','nume1','tip1','1'
'500','2010-10-10','2','nume2','tip2','2'
'500','2010-10-10','3','nume3','tip3','3'
'500','2010-10-10','4','nume4','tip4','4'
'500','2010-10-10','5','nume5','tip5','5'
for 2010-10-11 :
'500','2010-10-11','1','q','tip1','1'
'500','2010-10-11','2','z','tip2','2'
'500','2010-10-11','3','i','tip3','3'
'500','2010-10-10','4','nume4','tip4','4'
'500','2010-10-10','5','nume5','tip5','5'
for 2010-10-12 :
'500','2010-10-11','1','q','tip1','1'
'500','2010-10-11','2','z','tip2','2'
'500','2010-10-11','3','i','tip3','3'
'500','2010-10-12','1','q','1','4'
'500','2010-10-10','5','nume5','tip5','5'
for 2010-10-13 :
'500','2010-10-11','1','q','tip1','1'
'500','2010-10-11','2','z','tip2','2'
'500','2010-10-11','3','i','tip3','3'
'500','2010-10-12','1','q','1','4'
'500','2010-10-13','2','z','2','5'
What is the select that will do this trick ?
Thank you
 
[edit]SHOUTING removed, Code block added - OriginalGriff[/edit]
Posted 3-Nov-12 2:09am
Edited 3-Nov-12 2:29am
v2
Comments
OriginalGriff at 3-Nov-12 8:29am
   
DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalisation if you want to be taken seriously.
ancientrd at 3-Nov-12 8:39am
   
Sorry, Caps was on.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Firstly, your question is uncomplete about primary key and foreign key(You did'nt mentioned it which one is primary and which one is foreign).
 
If you didn't built a foreign key for the table just build a new FK for the 2nd table then on behalf of that call what you want by using select statement and providing date as a condition.
 
For further details click on primary key[^] and foreign key here.[^]
  Permalink  
Comments
ancientrd at 3-Nov-12 9:54am
   
how to add the key if the puf is like this
'500','2010-10-10','1','nume1','tip1','1'
'500','2010-10-10','2','nume2','tip2','2'
'500','2010-10-10','3','nume3','tip3','3'
'500','2010-10-10','4','nume4','tip4','4'
'500','2010-10-10','5','nume5','tip5','5'
'501','2010-10-10','1','nume1','tip1','6'
'501','2010-10-10','2','nume2','tip2','7'
'501','2010-10-10','3','nume3','tip3','8'
'501','2010-10-10','4','nume4','tip4','9'
'501','2010-10-10','5','nume5','tip5','10'
'502','2010-10-10','1','nume1','tip1','11'
'502','2010-10-10','2','nume2','tip2','12'
'502','2010-10-10','3','nume3','tip3','13'
'502','2010-10-10','4','nume4','tip4','14'
'502','2010-10-10','5','nume5','tip5','15'
and iep is like this:
'500','2010-10-11','1','q','tip1','1'
'500','2010-10-11','2','z','tip2','2'
'500','2010-10-11','3','i','tip3','3'
'501','2010-10-12','1','q','1','4'
'501','2010-10-13','2','z','2','5'
'501','2010-10-11','1','q','tip1','6'
'502','2010-10-11','2','z','tip2','7'
'502','2010-10-11','3','i','tip3','8'
'502','2010-10-12','1','q','1','9'
'502','2010-10-13','2','z','2','10'
Abhishek Pant at 3-Nov-12 10:05am
   
Add id you mentioned as primary key or foreign key of one the table then write conditional select. or you can make a new table to store them on runtime using stored procedure function.
Abhishek Pant at 3-Nov-12 10:08am
   
Then what did you think to call all the data from both tables. and if this is it why did you built keys.
ancientrd at 3-Nov-12 11:49am
   
what i need is :
1. determine the highest date from iep for each id_farm,cod from puf and compare with the date from puf and choosed the highest one based on the input date
Abhishek Pant at 3-Nov-12 12:26pm
   
Then why didn't you mentioned this above in place of writing all stuff. Thats why for comparing I told you you will need a instance. Though your concepts are not clear I tell you primary key is a unique key that can be compared with any table with a secondary key i.e. Foreign Key .First of all find out date in Decresing order i.e.|| SELECT iep.datora,puf.id_farm,puf.cod FROM iep,puf Where iep.datora=puf.datora ORDER BY datora DESC ||This will make the highest date in top. then use TOP property of sql and compare them by taking instance.U may use nested query or you can use stored procedure so that you do not have to run it again.
Abhishek Pant at 3-Nov-12 12:28pm
   
SELECT iep.datora,puf.id_farm,puf.cod FROM iep,puf Where iep.datora=puf.datora ORDER BY datora DESC |This is the query you can use to order the highest date and comapare it using top function|
Abhishek Pant at 3-Nov-12 12:33pm
   
You can also use group by statement with having clause.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 CHill60 360
1 Sarvesh Kumar Gupta 238
2 OriginalGriff 163
3 Sergey Alexandrovich Kryukov 163
4 DamithSL 153


Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 3 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid