March 11, 2004 at 4:24 am
Hi,
(sorry for the dodgy title)
I have 3 tables:
<SCHEMA>
tblProduct
productId
productName
tblProductAttribute
productId
attributeId
tblAttribute
attributeId
attributeName
<\SCHEMA>
Now, in tblAttribute, I want to change the attributeId for several attributes.However, these attributeId’s are already tied to tblProductAttribute.
I really don’t want to DELETE and then re-enter all the products, just because I need to change the Id’s of a few attributes.Can T-SQL be my friend here?
Cheers,
Yogiberr.
March 11, 2004 at 5:11 am
The method depends upon whether and how you have used DRI...
If, as your "schema" pseudocode implies, you have not used DRI, you can just use UPDATE statements on each table. If you don't have exclusive access to the database, then update both tables within an explicit transaction.
If you use DRI with the ON UPDATE CASCADE option, this sort of thing is easy. Read about this in the "CREATE TABLE" topic in BOL.
--Jonathan
March 11, 2004 at 6:15 am
Hi Jonathan.
I used the "ON UPDATE CASCADE" option, and it worked, cheers.
The reason i wanted to change the attributeId is because I wanted my query to return the results in the ORDER BY attribId:
<code>
SELECT DISTINCT
p.productId,
p.productName,
a.attribName,
pa.attribValue
FROM
tblProduct p,
tblProductAttribute pa,
tblAttribute a
WHERE p.productId = pa.productId
AND a.attribId = pa.attribId
AND p.categoryId = @categoryId
ORDER BY a.attribId --THIS IS THE LINE I ADDED TO THE PREVIOUSLY WORKING QUERY
<\code>
So, it gives me an error:
<error>
ORDER BY items must appear in the select list if
SELECT DISTINCT is specified.
<\error>
If I remove the "SELECT DISTINCT" clause, it gives me an error at the presentation layer, where I am doing some jiggery pokery with the result sets.
Is there any way round this?
Essentially, I am asking if I can:
1) keep the SELECT DISTINCT clause.
2) ORDER By an element that does not appear in the select list.
Do you think this is possible?
Cheers,
yogiberr
March 11, 2004 at 6:38 am
You cannot do that because it's not logical. Once you ask for just distinct combinations, any value not included in those combinations is not defined for the result. How could it be? Let's take a simple example from Northwind:
USE Northwind
GO
SELECT DISTINCT o.CustomerID, s.CompanyName
FROM Orders o JOIN Shippers s ON o.ShipVia = s.ShipperID
Now you wish to order these by OrderDate. As the rows being removed from the result set by using DISTINCT could have many dates both before and after other rows of the result set, it makes no sense to order by that. If you want some arbitrary order, you could use GROUP BY and an aggregate function, e.g.:
SELECT p.productId, p.productName, a.attribName, pa.attribValue
FROM tblProduct p JOIN tblProductAttribute pa ON p.productId = pa.productId
JOIN tblAttribute a ON pa.attribId = a.attribId
WHERE p.categoryId = @categoryId
GROUP BY p.productId, p.productName, a.attribName, pa.attribValue
ORDER BY MIN(a.AttribId)
--Jonathan
March 11, 2004 at 7:19 am
brilliant,
I added on a wee extra to the clause, so my presentation layer wouldn't have kittens.
<code>
ORDER BY MIN(p.productId), MIN(a.AttribId)
<\code>
thanks bud,
yogiberr
March 11, 2004 at 7:24 am
Shouldn't that just be:
ORDER BY p.ProductId, MIN(a.AttribId)
?
--Jonathan
March 11, 2004 at 7:31 am
ok,
i got caught up in the moment
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply