Problem with SQL Query

[stanley18]

Let's assume I have such table: Pictures: p_id p_name p_path p_userId_mobile_addition [int] p_userId_mobile_modification [int] p_userId_www_addition [uniqueidentifier] p_userId_www_modification [uniqueidentifier] p_userId_mobile keys reference UsersMobile table. p_userId_www keys reference UsersWWW table. These keys give us information about the user that added or modified the picture. There are 4 keys as addition and modification can be performed with www or PocketPC interface. How to create a view presenting: 1. p_id, 2. p_name, 3. p_path 4. username (from UsersMobile or UsersWWW) for addition 5. username for modification through www 6. username for modification through PocketPc Platform: MS SQL Server 2005 Best regards,

[Mschauder]

where is the problem? :confused: what did you try? we make no query for you X|

[Jorgen Andersson]

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