SQL Help, working out if a column is all of one value

[minestrone]

It I have 2 tables which model a one to many relationship.

table bag ( id , status )

table item( id , bag_id , status )

I want a query that will see if all the status for the item table are all '1' and update bag status to '1', they have to be all '1' though.

Is there some way to do this?

Thanks

[DimPrawn]

It I have 2 tables which model a one to many relationship.

table bag ( id , status )

table item( id , bag_id , status )

I want a query that will see if all the status for the item table are all '1' and update bag status to '1', they have to be all '1' though.

Is there some way to do this?

Thanks

UPDATE bag
SET status = 1
WHERE EXISTS (SELECT 'All ones' FROM item INNER JOIN bag ON id = bag_id
GROUP BY bag_id HAVING COUNT(*) = (SELECT COUNT(*) FROM item X WHERE X.bag_id = bag.bag_id AND status = 1))

Off the top of my head.

[crack_ho]

Something like this...

UPDATE bag
SET status = 1
WHERE id in (
	SELECT bag_id FROM
	ITEM
	GROUP BY bag_id
	HAVING min(isnull(status,0)) = 1 AND max(isnull(status,0)) = 1
)

[minestrone]

Cheers lads!!

[Sir_Edward_Matheson]

It I have 2 tables which model a one to many relationship.

table bag ( id , status )

table item( id , bag_id , status )

I want a query that will see if all the status for the item table are all '1' and update bag status to '1', they have to be all '1' though.

Is there some way to do this?

Thanks

I realise this is nothing to do with your question and that some of it applies to SQL Server specifically, but here goes...

The primary keys should be of the format <tablename>id so that the name matches the foreign key. This follows from the argument of those of us that say primary keys must not only be unique across a database but they must also be uniquely named so as not to break 1NF. I firmly advise against ever naming primary keys id or using reserved words as name, status and so on for field names. Having had to deal with over 400 tables with id as the primary key in each table, it becomes very difficult to work out which id somebody is talking about and identifying joins becomes really difficult.

Furthermore, pretty much every tool I have come across for setting up a schema defaults to matching columns with the same name.

The only argument for using id as the primary key is that there is less typing required to write employee.id as opposed to employee.employeeid. There are two major issues with this argument:
1. Less typing is more important than long term maintainability. I am fairly sure I do not need to explain why this is wrong.
2. The assumption that somebody would ever write employee.id = employee.employeeid. There may well be people who do this but I much prefer a.employeeid = b.employeeid which as you can see renders the argument of less typing to be practically irrelevant.

I should also add that another one of my gripes is the use of "uniqueidentifier" for the primary key, even when there is no replication involved (this is almost always the case, IME.) There is no need for this and from a performance perspective is slower than using an integer identity. A uniqueidentifier is 16 bytes whereas an int is 4 bytes. Now think of clustered keys and fragmented indexes and I am sure you get the picture. Oh, IIRC joins on uniqueidentifiers mean that the database engine does a character by character match prior to the join.

[DimPrawn]

I should also add that another one of my gripes is the use of "uniqueidentifier" for the primary key, even when there is no replication involved (this is almost always the case, IME.) There is no need for this and from a performance perspective is slower than using an integer identity. A uniqueidentifier is 16 bytes whereas an int is 4 bytes. Now think of clustered keys and fragmented indexes and I am sure you get the picture. Oh, IIRC joins on uniqueidentifiers mean that the database engine does a character by character match prior to the join.

Although what you say is technically correct at the database level, many modern OO applications have to deal with multiple users and multiple objects than need a unique identifier before the object is persisted to the database. In this case, assigning a GUID to each object in the middle layer is the best solution.

The days of client-server apps is gone and relying on the database to assign a unique id is not feasable or desirable.

Also, a few bytes is not really an issue. Other fields in the same table may have many hundreds or thousands of bytes, so the PK does not make the storage any bigger in relation.

A good pros/cons article:

http://databases.aspfaq.com/database...imary-key.html

[minestrone]

Although what you say is technically correct at the database level, many modern OO applications have to deal with multiple users and multiple objects than need a unique identifier before the object is persisted to the database. In this case, assigning a GUID to each object in the middle layer is the best solution.

The days of client-server apps is gone and relying on the database to assign a unique id is not feasable or desirable.

Also, a few bytes is not really an issue. Other fields in the same table may have many hundreds or thousands of bytes, so the PK does not make the storage any bigger in relation.

A good pros/cons article:

http://databases.aspfaq.com/database...imary-key.html

Aye, it's an OO ORM app I am working on, needs a small stored proc though.

Also, Sir_Edward, the tables were just dummy things, I don't want to start printing off real schema details to the web.