Select specific value via simple select query - I am a beginner!

[Milleh]

Say I have a table with 3 fields: TransferID, Department, and Program (ignore the fact that I don’t have a unique primary key in this example). For a given TransferID there are several records, each record containing a value for Department, and some containing a value for Program, some being null. Say that I want to return all records in the table, but I want a column that returns the applicable Program value associated with a particular department, for each TransferID.

Example:

1, Sales, 223
1, Martketing, null
1, Maintenance, 228
2, Sales, 334
2, Management, 556
2, Engineering, null
etc…

I want my query to return for each record, the associated Program for the ‘Sales’ department for the specific TransferID. So I would want the result to be:

1, Sales, 223
1, Marketing, 223
1, Maintenance, 223
2, Sales, 334
2, Management, 334
2, Engineering, 334

My query does not work though, it doesn’t return the correct program value as per my example above, and here is what I am doing:

Select t1.TransferID, t1.Department,

(CASE WHEN t1.Program is not null THEN t1.Program where Department = ‘Sales’ else (select t1.Program where t1.Department = 'Sales') end) as ‘Program’

Any ideas?

Thank you

 

[gmmastros]

I would use a derived table approach here. Let me show you how.

First, I recognize that each TransferId must get a program value based on the original data, but only where department = 'sales'. So, let's write a query that returns TransferId and Program where deparment = sales.

Select TransferId, Program
From   YourTableName
Where  Department = 'Sales'

It's a simple enough query to write. But... now we are going to use that query in a much larger query to return the data the way we want it.

Think of it this way... if the data returned by the previous query were in a real table, the query would be pretty simple, right?

[tt][blue]
Select YourTableName.TransferId,
YourTableName.Department,
FicticiousTable.Program
From YourTableName
Inner Join FicticiousTable
On YourTableName.TransferId = FicticiousTable.TransferId
[/blue][/tt]

With SQL Server, there is a derived table technique that allows you to use a query as though it were a table. There are some rules for using this syntax, but the rules are pretty simple. You need to use parenthesis around the query, and you MUST us an alias.

Select YourTableName.TransferId, 
       YourTableName.Department,
       [green]FicticiousTable[/green].Program
From   YourTableName
       Inner Join [!]([/!]
         [blue]Select TransferId, Program
         From   YourTableName
         Where  Department = 'Sales'[/blue]
         [!]) As FicticiousTable[/!]
         On YourTableName.TransferID = [green]FicticiousTable[/green].TransferId

The part in blue is exactly the same as the first query we wrote. The part in red is the necessary syntax to make this a derived table. The parts in green are highlighted to show you that you must use the alias everywhere in your query where you use data from the derived table.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Milleh]

Hmm interesting, thank you very much for that - I will try this later this morning after some meetings.

I appreciate your help and will provide feedback after I try to incorporate your advice.

 

[Milleh]

It worked! Thanks again for your help.

 

[gmmastros]

I'm less concerned with it working, and more concerned with your understanding.

Does it make sense to you? The derived table technique is very powerful, and can be used in a lot of creative ways. Understanding it is important.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Milleh]

*I think* that I understand the logic, especially since it's producing the desired results in my particular case. I will have to apply this same logic to more complex scenarios down the road which will test whether or not my understanding is correct - if it is not I will definitly come back for more information