Joining 2 tables & reporting values

[unicorn7]

Hi there

I have 2 SQL tables
the first one shows the order date....and the second one shows the date that the order was completed

Table1
-------
Order Code - Order Date

001 - 10/10/2009
002 - 11/10/2009
003 - 11/10/2009
004 - 12/10/2009
005 - 14/11/2009

Table2
-------
Order Code - Order Completed

002 - 11/11/2009
003 - 15/12/2009
005 - 16/12/2009


the thing i wanna do is a SELECT query that shows the following ...

Order Code - Order Date - Order Completed - Status(Constant Value)
001 - 10/10/2009 - NULL - ON GOING
002 - 11/10/2009 - 11/11/2009 - DONE
003 - 11/10/2009 - 15/12/2009 - DONE
004 - 12/10/2009 - NULL - ON GOING
005 - 14/11/2009 - 16/12/2009 - DONE

I think that this is think is possible .....but i really don't know how to do it :/
I would really be great full of any help

[LKBrwn_DBA]

You could try a simple outer join.

[unicorn7]

thnx anyway...
a full outer join did the trick ...and because I wanted to do it in MS ACCESS...it went like this

SELECT table1.OrderCode,'DONE' AS STATUS
FROM table1
LEFT JOIN table2
ON table1.OrderCode = table2.Order Completed
UNION ALL SELECT table2.Order Completed,'ON GOING' AS STATUS
FROM table1
RIGHT JOIN table2
ON table1.OrderCode = table2.Order Completed
WHERE table1.OrderCode IS NULL
ORDER BY table1.OrderCode;


and the RESULT WAS

Order Code - Order Date
001 - 10/10/2009 - ON GOING
002 - 11/10/2009 - DONE
003 - 11/10/2009 - DONE
004 - 12/10/2009 - ON GOING
005 - 14/11/2009 - DONE

well thanx anyway...you didn't help a lot...(cause i tried the "JOIN" my self before...) but you were the catalyst to make my head tick a little better
thank you

[PAVB]

well thanx anyway...you didn't help a lot

you are doing all in your power to avoid getting help in the future, aren't you?

[unicorn7]

you are doing all in your power to avoid getting help in the future, aren't you?

No that's not the thing...
the thing was that i was hopping for getting the complete query as an answer...
when I saw the answer that i should do an "outer join" I got a bit disappointed and angry at the same time...like ..."Ok I know that i need an outer join...but I tried it and it doesn't work....how can iI make it WORK... )

then I did some more experiments and finally the problem was resolved...

LKBrwn_DBA gave me the boost to find my the answer by my self....although i could ask for another helping hand...but I knew that would take more time than to take my butt and sit it on the chair and start working. (And as you can see you replied 5 days after my last post...plz that's not a reason 2 start arguing again..it is just a fact)

I never wanted to offend anyone with my writing and my thoughts...I just really need it help and I really got practically one....and I'm really greatfull that someone gave me an answer nevertheless I just entered the forum...I'm not an ingrate person...I was just lazy and confused with finding the answer

[LKBrwn_DBA]

No that's not the thing...
the thing was that i was hopping for getting the complete query as an answer...
...etc ...

To get better help next time, we suggest you let us know what you have tried and post the query. That way when we see something simple we know it is not a request to do your homework and that you already tried a solution that did not work for you and thus better help you solve the issue.