Two tables data with condition

[ponneri]

I know this is simple, but I'm lost !

I have two tables A and B. Both have a common field PO_NUM. My query is supposed to get 3 fields from table A and 5 fields from table B. Condition is : Only those records that have a matching PO_NUM in both tables plus one field in table B must match user entered runtime date criteria.

Ex: All the records from 1-30 Sept 16, when user enters Sep-16 at runtime

How can I do this ?

I wrote a query with datepart function but it says too complex to be evaluated ?
No syntax or other error message.

Can someone please help soon ??

 

[Minty]

What was the query you wrote - post up the SQL please?

You should just join the two tables on the PO_Num field and put your criteria in.

 

[ponneri]

It was like : ( not using the exact field names)

select a.f1,a.f2.a.f3,b.f1,b.f2.b.f3,b.f4,b.f5
from a,b
where a.po_num = b.po_num
and datepart("m",[b.po_date] = [Enter Month Name ?])

 

[Minty]

Okay - so at the moment there is no join. In the query window instead of the criteria a.po_num = b.po_num you need

Select a.f1,a.f2.a.f3,b.f1,b.f2.b.f3,b.f4,b.f5
FROM a inner join b on a.po_num = b.poNum

I think your criteria section looks ok.

 

[ponneri]

Thank you so much !

I thought the a.po_num = b.po_num was same as the inner join clause.

I'll try that and see.

 

[Minty]

Thank you so much !

I thought the a.po_num = b.po_num was same as the inner join clause.

I'll try that and see.

It sort of nearly is - but will force access to query each value in each table individually to get the result. That would make it very complex to compute hence your original error.

On SQL server it would recognise the statement as a join and automatically create it if you were in the query or view designer.

 

[ponneri]

Thanks again ! Excellent !!