Getting information from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Getting information from two tables

Author

Topic

Rune Thandy
Starting Member

3 Posts

Posted - 2007-04-03 : 07:45:48

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 07:52:18

Look at this topic, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81373
Please provide proper sample data and expected output, with an explanation.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 08:37:54

Yes, that edit made a huge difference

SELECT		cc.CostCodeID,
		cc.CostCodeDesc,
		MAX(NULLIF(tt.CombiStatus, cc.CostCodeStatus)) AS Combistatus
FROM		CostCode AS cc
LEFT JOIN	TallyTable AS tt ON tt.ItemID = cc.CostCodeID
WHERE		cc.CostCodeStatus = 'Active'
GROUP BY	cc.CostCodeID,
		cc.CostCodeDesc
ORDER BY	cc.CostCodeID,
		cc.CostCodeDesc

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 08:39:50

[code]SELECT cc.CostCodeID,
cc.CostCodeDesc,
tt.CombiStatus AS Combistatus
FROM @CostCode AS cc
LEFT JOIN @TallyTable AS tt ON tt.ItemID = cc.CostCodeID AND tt.CombiStatus <> 'Active'
WHERE cc.CostCodeStatus = 'Active'
ORDER BY cc.CostCodeID,
cc.CostCodeDesc[/code]

Peter Larsson
Helsingborg, Sweden

Rune Thandy
Starting Member

3 Posts

Posted - 2007-04-03 : 08:50:49

I accidently pressed submit instead of preview. And while I was editing, you were so fast that you already answered the thread.

Where do I put in the projectid?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 09:46:27

In a WHERE clause?
Your sample data do not provide enough information to determine that according to your expected output.

SELECT		cc.CostCodeID,
		cc.CostCodeDesc,
		tt.CombiStatus AS Combistatus
FROM		@CostCode AS cc
LEFT JOIN	@TallyTable AS tt ON tt.ItemID = cc.CostCodeID AND tt.ProjectID = 456
WHERE		cc.CostCodeStatus = 'Active'
ORDER BY	cc.CostCodeID,
		cc.CostCodeDesc

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 09:56:35

High sweet Jesus!

Why did I ever put energy and time into this?
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=355563

Nice work RuneThandy.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 10:37:45

http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=20515

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 10:42:43

How many people's time has be occupied?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 10:51:11

As many as possible

or

Until he/she is satisfied with the answer

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 10:53:40

He can't find the time to answer either...
Waste of time here.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 10:59:15

It is common to some newbies

Madhivanan

Failing to plan is Planning to fail

Rune Thandy
Starting Member

3 Posts

Posted - 2007-04-04 : 03:33:13

I had to find an anser as quick as possible, so I tried more forums.
It's not for myself, but for work.
And I had gone home after you posted your posts.
But I do thank you for your time and energy you put in.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources