Select data where transaction dates are equal 1

[Hillary]

I am trying to pull Part ID's and Transaction ID's out of the database where there was a receipt and a return receipt of the same Part ID on the same day.

All the information is on one table, Inventory_Trans
Class "R" (for receipt)
Type "I" (in transaction)
Type "O" (out transaction)
Ex. Type "I" and Class "R" is a receipt
Type "O" and Class "R" is a return

For the select statement I have...

Select Part_ID, Transaction_ID from Inventory_Trans

I need help with the Where clause.

Thanks for any help.

Hillary

 

[grega]

Not sure of the exact structure of your table (keys, etc.), but try something along the lines of this - it's a self-join.

select a.part_id, a.transaction_id, b.transaction_id
from inventory_trans a, inventory_trans b
where a.type="I"
and a.class="R"
and a.part_id = b.part_id
and a.transaction_date = b.transaction_date
and b.type="O"
and b.class="R"

Greg.

 

[Hillary]

Thank you Greg! I am fairly new to SQL and only know how to write "simple" scripts.

Is it called a self join when you use the a and b labels? Are the labels always after the table names but before the field names?

Do you know of websites that show the advanced features of SQL?

Thanks agian.

Hillary

 

[stoggers]

Hi,

One of my favourite references is:

http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM

It has a DB2 bias but there are some really good examples...

Stoggers.

 

[grega]

No problem Hilary. Glad to be of help.

It's a self-join because the table is being joined to itself. Normal practice with SQL is to qualify a fieldname with the tablename tablename.fieldname to avoid any confusion. If you're joining or referring to more than 1 table with identical fieldnames, you have to do that anyway.

The from clause ..

from inventory_trans a, inventory_trans b

.. sets an alias for each table in the from clause. It usually makes things easier in the where clause, particularly if you have long tablenames. Here it's needed as both tables have the same name.

Greg.