Database

Why, the only reason I can think of for this is to service a presentation requirement, that is a report, output or a form requirement. The relevant word is PRESENTATION, do this type of formatting at the correct place, not in the database, as has been suggested. This is not a suggestion it is a general rule, a database is for the data, not pissing about with formatting and layout. Never underestimate the power of human stupidity RAH

Try something similar to this: select p_id, p_name, p_path, case when uw.username is not null then uw.username when um.username is not null then um.username else null end creator, uw.username, um.username from pics left join usersmobile um on pics.p_userId_mobile = um.p_userId_mobile left join UsersWWW uw on pics.p_userId_www = uw.p_userId_www But your problem is really bigger than the query, your datamodel isn't very good. You need to normalize the tables to avoid corruption of your data Here's a suggestion (with room for improvement): Image imageid PK name path users /*only one usertable*/ userid PK username accesstype accesstypeid PK accesstype /*www and mobile*/ useraccess useraccessid PK userid FK accesstypeid FK unique key (userid,accesstypeid) imagecreated imagecreatedid PK imageid FK useraccessid FK createdate unique key (imageid,useraccessid) /*an image can only be created once*/ imagechanged imagechangedid PK imageid FK useraccessid FK changedate As this allows storage of several changes per image you need to rethink your original query, or use max(changedate) to get the last change "When did ignorance become a point of view" - Dilbert

You're welcome. "When did ignorance become a point of view" - Dilbert

Excellent! Thanks a mil.

Whatever it returns first is the first. Or maybe you're using it wrong.

Wouldn't only string functions work? Just guessing.

3 tables Location LocationID int identity(1,1) Location varchar(20) Machine MachineID int identity (1,1) LocationID int Machine varchar(20) TranTable TranID identity (1,1) MachineID Direction Date This assumes a machine can have only 1 location and the machine can be checked in/out many times. Using a date on the transaction table will allow you to get durations, and state on a given date Never underestimate the power of human stupidity RAH

The OS has very little relevance of the edition you should use, it depends on your database requirements, This may help http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx[^] If you are asking about a development environment then use either developer or express versions. Never underestimate the power of human stupidity RAH

Yikes - wish I hadn't asked. Okay, well, I'd be looking for the first reported error and concentrate on that: Starting up database 'master'. Error: 17207, Severity: 16, State: 1. Start with that and see how far you get. (If anyone else has a better suggestion now would be the time...) me, me, me "The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!" Larry Niven

I'm not sure what level of detail you're looking for in your auditing, but have you checked out what's built in to SQL Server 2008 - http://msdn.microsoft.com/en-us/library/dd392015.aspx[^]? Scott

Thanks....Any sample example/link will be appriciated.

Have you considered DBMS_CRYPTO? Here's the link: http://www.oracle.com/technology/oramag/oracle/05-jan/o15security.html[^]

Hey, why did you use "ELSE IF" when "ELSE" is same??? thats like if a = 1 then "abc" else if a = 2 then "222" else "222"

What you are doing is generally a sensible approach but you need to view the Actual Execution Plan that Sql Server generates to see if they are being used by the Query Optimizer. There is a free pdf download from Red-Gate[^] that is very good. http://www.simple-talk.com/books/sql-books/sql-server-execution-plans/[^] If you are willing to spend some money, then buy this book. SQL Server 2008 Query Performance Tuning Distilled[^] I can't recommend this book highly enough. Although it is targeted at Sql2008, it's still very applicable to Sql2005 as well.

I had thought about answering this as I knew something was wrong but could not think what was. i.j.russell you have hit it on the head.

For MS SQL Server I use ISNULL like ISNULL(FirstName + ' ', '') + ISNULL(LastName, '') I put a space after the first name but if it is null then the space does not get added.

use Exec ProcedureName @Variable Never underestimate the power of human stupidity RAH

iam using this query but not getting the proper result. select companyname,branchname,location from users,company,branch on user.companyid=company.companyid innerjoin company.companyid=branch.branchid i want the user blongs to the company and all related branches can you suggest.

John Gathogo wrote: Is there any benefit in setting a primary key in a table variable? Yes, mainly to ensure referential integrity. That's probably an unsatisfying answer. Imagine that your table only has "name" defined in it. We could add "John" as a record, and have a list of things that John should do in some other table. Might look like this; TABLE Users (Name NVARCHAR(36)) "John" "Peter" TABLE Tasks (Name NVARCHAR(36), ThingToDo NVARCHAR(50)) "John", "Water the plants" "John", "Cook dinner" "Peter", "Write an article for CodeProject" We're in trouble as soon as someone adds another user with the firstname "John"; we would no longer be able to differentiate between the tasks of the first "John" and the second "John". Now, it's a "best practice" to define a set of column-names that make a record unique. We could add the [Birthdate] of the user to the "Users" table, that way we could distingiush between the first and the second Johnny. It's easier[^] to just add a generated identity and to use that as a primary key. A primary key is often accompanied by a clustered index. There's a heap of information on MSDN[^], if that is your kind of thing :) I are Troll :suss:

I'm sure that there is a more appropriate forum for this question to be in.