2 relationships between 2 tables - how can i query and return the correct results?
Posted on 2004-10-01
Two tables involved in this particular query:
STATUS and METHOD
in Status table, there are two fields that refer to the primary key of method table, as the status table is trying to catch how something was received and also how something was sent out. eg. sent out via courier, and returned via fax, for example.
I do not know how to write the SQL statement to bring back the correct results as it seems to be bringing back "Fax for both sent and received method, even though the sent method in the database is clearly courier.
Any one have any ideas?
The join looks like this:
STATUS join Method on Method.methodid = status.sentmethod
and Method.methodid = status.receivedmethod
I know this is not correct, but don't know how to resolve this....
Looking forward to your responses.