You need to use subqueries.

I've seen that too, but also, I've seen that what seems a simple sintax problem is just the tip of the iceberg and the real problem needs sample data and...

I might be thinking outloud and it could be even more work, but is an idea.

Could you use a stored procedure that will assign all those values to output parameters?...

Why would you want to add a column in a stored procedure?

That won't be reusable.

joshphillips7145 (8/9/2012)

---

*upDate* I got rid of the error message but it does not return any data??

Select course1.Ref_Number, course1.enrollment

From course As course1 JOIN Course As course2

ON course1.Ref_Number = course2.Ref_Number

Group by course1.Ref_Number,...

You cannot accomplish that using a self join as you did.

The only way I figure it out, would be with a derived table.

SELECT course1.Ref_Number, course1.enrollment

FROM Course As course1

JOIN (SELECT...

Do you mean to use the subquery as a derived table?

I don't understand the problem, but I believe you don't need multiple aliases.

Can you explain in more detail and give the exact problem? Or what do you mean by this:

Problem...

No, you're still putting the subquery in the field list.

Put it in the WHERE clause where you can compare the result of the subquery to your field and filter...

The subquery should be on the WHERE clause.

Remember you're trying to compare a value against the average to filter information.

Do you need more tips? Can you change your query?

EDIT: You...

Now, this is very weird behavior.

Correct answers: 98% (414)

Incorrect answers: 2% (10)

Total attempts: 424

--------------------------------------------------------------------------------

1.INSTEAD OF triggers, AFTER triggers, constraints 6%

--------------------------------------------------------------------------------

2.INSTEAD OF triggers, constraints, BEFORE...

Another option, could be

SELECT ISNULL(NULLIF(fm_clinum, '') + CHAR(10), '') +

ISNULL(NULLIF(fm_contac, '') + CHAR(10), '')

...

FROM...

That was my first thought Sean, but he wanted to identify the empty strings to avoid additional lines.

Maybe you could use a DDL trigger or Policy-Based Management.

If you validate your blanks with LEN(field) > 0, you don't need to worry about the nulls.

SELECT CASE WHEN LEN(fm_clinum) > 0 THEN fm_clinum + CHAR(10) ELSE '' END +

...