Need help to create select statement

Question

0.00/5 (No votes)

See more:

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

ancientrd

Updated 3-Nov-12 2:29am

OriginalGriff

v2

Add a Solution

Comments

OriginalGriff 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 3-Nov-12 8:39am

Sorry, Caps was on.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

*Abhishek Pant* · Answer 1 · 2012-11-03T03:47:00

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.[^]

Posted 3-Nov-12 3:47am

Abhishek Pant

Comments

ancientrd 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 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 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 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 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 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 3-Nov-12 12:33pm

You can also use group by statement with having clause.