sql server (alternate): I have 3 tables

A table of users containing the fields always required for a user.
users:
userid
email
name
password
projectid

A table of extra properties for a user that can be different from each
project.
These properties can be "location", "department", and so on.
userproperties
userpropertyid
propertyname
projectid

A table of the value of each of the extra properties for each user
userpropertyvalues
userpropertyid
userid
userpropertyvalue


Now I want to do a select statement that returns

userid, email, name, password, propertyname[1], propertyname[2],
propertyname[3]

[userid],[email],[name],[password],[propertyvalue1],[propertyvalue2],propertyvalue[3]

(when I pass a projectid)

I just don't seem to be able to do that. Any ideas?

Thank you very much in advance.

Flemming

Hi

You design does not look to be correct. If a user can only be in one project
then I would not expect projectid to be in userproperties but I would expect
userid to appear in it. If a user can have multiple projects then I would
expect both to be in userproperties and userpropertyvalues. It is also not
clear how you would rank these assuming propertyid is a numeric then the
first property has the the minimum propertyid, the second property is the
minimum propertyid greater then the first propertyid, the third property is
the minimum propertyid greater then the second propertyid. You can then join
to the users to userproperties/userpropertyvalues three times to get the
values you want.

Also check out how to post DDL and example data at
http://www.aspfaq.com/etiquett­­e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.­­htm#inserts
It is also useful to post your current attempts at solving the problem.

John


[quoted text, click to view]

(flemming.madsen@gmail.com) writes:
[quoted text, click to view]
[userid],[email],[name],[password],[propertyvalue1],[propertyvalue2],propert
yvalue[3]
[quoted text, click to view]

For this kind of questions, it always a good idea to include:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This permits anyone who answer to post a tested query.

In you case, there are several loose ends. For instance, in the
userpropertyvaules, I would expect a projectid, since I would
expect (projectid, userpropertyid) to be the primary key of
userproperties. It seems now that userpropertyid alone is the
key. Another loose end is how you now which propertyvalue is #1
and so on. Furthermore, do we know if all users have all properties
for a project?

So this query is very much just a sketch, but hopefully you can work
from it.

SELECT u.userid, u.email, u.name, u.password, upv1.userpropertyvalue,
upv2.userpropertyvalue, upv3.userpropertyvalue
FROM users u
JOIN userproperties up1 ON u.projectid = up1.projectid
JOIN userproperties up2 ON u.projectid = up2.projectid
JOIN userproperties up2 ON u.projectid = up2.projectid
LEFT JOIN userpropertyvalues upv1
ON up1.userpropertyid = upv1.userpropertyíd
AND upv1.userid = u.userid
LEFT JOIN userpropertyvalues upv2
ON up2.userpropertyid = upv2.userpropertyíd
AND upv2.userid = u.userid
LEFT JOIN userpropertyvalues upv3
ON up3.userpropertyid = upv3.userpropertyíd
AND upv3.userid = u.userid
WHERE u.projectid = @projectid
AND up1.projectid = @projectid
AND up2.projectid = @projectid
AND up3.projectid = @projectid


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Erland,

Your query works. Thank you very much.

One issue though: I might not in advance be aware of the number of
properties for each user.

Any ideas on alterting the query (or splitting it up) so I can deal
with that?

Thank you very much,
Flemming

Update:

Erland,

I modified your proposal slightly and I have now solved my problem.

Once again, thank you very much for your kind help.

Sincerely,
Flemming

Erland Sommarskog (esquel@sommarskog.se) writes:
[quoted text, click to view]

Flemming said that my query worked, which is sort of funny, because I
forgot there conditions in the WHERE clause, which I had intended to
read:

WHERE u.projectid = @projectid
AND up1.projectid = @projectid
AND up2.projectid = @projectid
AND up3.projectid = @projectid
AND up1.propertyname = 'propertyname1'
AND up2.propertyname = 'propertyname2'
AND up3.propertyname = 'propertyname3'



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at