Help with query

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.

FlemmingHi

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

<flemming.madsen@.gmail.com> wrote in message
news:1111790204.659952.289470@.z14g2000cwz.googlegr oups.com...
>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|||(flemming.madsen@.gmail.com) writes:
> 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],propert
yvalue[3]
> (when I pass a projectid)
> I just don't seem to be able to do that. Any ideas?

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.userpropertyd
AND upv1.userid = u.userid
LEFT JOIN userpropertyvalues upv2
ON up2.userpropertyid = upv2.userpropertyd
AND upv2.userid = u.userid
LEFT JOIN userpropertyvalues upv3
ON up3.userpropertyid = upv3.userpropertyd
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
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||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:
> 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.userpropertyd
> AND upv1.userid = u.userid
> LEFT JOIN userpropertyvalues upv2
> ON up2.userpropertyid = upv2.userpropertyd
> AND upv2.userid = u.userid
> LEFT JOIN userpropertyvalues upv3
> ON up3.userpropertyid = upv3.userpropertyd
> AND upv3.userid = u.userid
> WHERE u.projectid = @.projectid
> AND up1.projectid = @.projectid
> AND up2.projectid = @.projectid
> AND up3.projectid = @.projectid

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
http://www.microsoft.com/sql/techin.../2000/books.asp

Help with Outer join

How can I join 3 tables that represent many-many realtions, in a way
that returns all the values of one table and an extra column of
true/false or null or some way to to show that the record exists or
doesnt exist in the link table.
This is so that when I pass a paramter of an author ID I can create an
array of checkboxes form the record set that are either ticked or not
ticked.
E.g for Pubs, output something like this:
author exists title
1 False 1
1 True 2
Thanks
hals_leftUSE Pubs
SELECT A.au_id, T.title,
CASE WHEN U.title_id IS NOT NULL
THEN 'Y' ELSE 'N' END AS exist
FROM Authors AS A
JOIN Titles AS T
ON A.au_id = '267-41-2394'
LEFT JOIN TitleAuthor AS U
ON U.au_id = A.au_id
AND U.title_id = T.title_id
David Portas
SQL Server MVP
--|||use pubs
Go
SELECT A.au_id, CASE WHEN EXISTS(SELECT 1 FROM TitleAuthor T
WHERE T.Au_id= A.Au_id) Then 1 Else 0 End as Exist
FROM Authors A
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:c04136bd.0502280242.5c8a6a27@.posting.google.com...
> How can I join 3 tables that represent many-many realtions, in a way
> that returns all the values of one table and an extra column of
> true/false or null or some way to to show that the record exists or
> doesnt exist in the link table.
> This is so that when I pass a paramter of an author ID I can create an
> array of checkboxes form the record set that are either ticked or not
> ticked.
> E.g for Pubs, output something like this:
> author exists title
> 1 False 1
> 1 True 2
> Thanks
> hals_left

help with my UPDATE query

I'm receiving this error: Incorrect syntax near the keyword 'SET'

CODE:

Code Snippet

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
UPDATE dbo.pnpcart_Customer_Details
SET dbo.pnpcart_Customer_Details.Prefix = @.Prefix,
SET dbo.pnpcart_Customer_Details.FirstName = @.FirstName,
SET dbo.pnpcart_Customer_Details.MiddleName = @.MiddleName,
SET dbo.pnpcart_Customer_Details.LastName = @.LastName,
SET dbo.pnpcart_Customer_Details.Address = @.Address,
SET dbo.pnpcart_Customer_Details.City = @.City,
SET dbo.pnpcart_Customer_Details.State = @.State,
SET dbo.pnpcart_Customer_Details.Zip = @.Zip,
SET dbo.pnpcart_Customer_Details.HomePhone = @.HomePhone,
SET dbo.pnpcart_Customer_Details.CellPhone = @.CellPhone,
SET dbo.pnpcart_Customer_Details.Email = @.Email
END

Thanks for the help in advanced!

-Thanks,
Rich

Use the following query...(are you forget your where clause, the current query will update all the records in the table)

UPDATE dbo.pnpcart_Customer_Details
SET Prefix = @.Prefix,
FirstName = @.FirstName,
MiddleName = @.MiddleName,
LastName = @.LastName,
Address = @.Address,
City = @.City,
State = @.State,
Zip = @.Zip,
HomePhone = @.HomePhone,
CellPhone = @.CellPhone,
Email = @.Email

Where (logical expression)

|||GREAT! That works perfectly for now. I'm going to have to revamp my code to work with a JOIN along with asp.net grid control.

Thanks,
Rich