Getting information from two tables
-
I've got the following two tables:
-costcode with costcodeid,costcodedesc,costcodestatus (which can be Active or Discontinued)
-tallycombi with projectid,projectdesc,itemid,itemdesc,unit,combistatus (Active or Discontinued).
In the costcode table the costcodeid is unique.
In the tallycombi table the combination of projectid and itemid is unique.
The costcodeid and costcodedesc for a certain project are inserted in the tallycombi table.
For a costcode item the unit will always be A.
There is another table for combining projects with other items, but that is not relevant for this problem.
In the table tallycombi a project and an item are linked together and can have the combistatus Active or Discontinued.
Now I need to show all Active costcodes and when they exist in the tallycombi table for a given projectid, the status of that combination has to be shown as well.
So for example:
costcodeid costcodedesc costcodestatus
00001 costcodeone Active
00012 costcodetwo Discontinued
00123 costcodethree Active
01234 costcodefour Active
projectid projectdesc itemid itemdesc unit combistatus
999 projectone 00123 costcodethree A Discontinued
456 projecttwo 00123 costcodethree A Active
456 projecttwo 00001 costcodeone A Active
The output for projectid='999' should be
costcodeid costcodedesc combistatus
00001 costcodeone NULL
00123 costcodethree Discontinued
01234 costcodefour NULL
How can I accomplish this?
-
SELECT a.costcodeid,a.costcodedesc,b.combistatus
FROM [coscodetable] a
LEFT JOIN [tallycombi] b
ON b.itemid = a.costcodeid
and b.projectid = '999'
Far away is close at hand in the images of elsewhere.
Anon. -
This works.
Thank you so very much.
It looks so simple now I see it.
I was working with left and whatever outer joins and couldn't get it right.
Thanks again.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply