Hello! Activating a product group, if number of products for sale are greater than 0

ManyTimes · #1 (**permalink**) 03-11-11, 19:40

"Basically what the title says"?

Uploaded a picture of the three tables... attached a script creating three tables, with the data needed (sql2008)...
PS1: table2 in the picture, has a column named "subgroupID" (the car-table), which is wrong, it should have been "groupID", my bad.
PS2: do not question the "ID" column in the "group-table", nor anything else, this is purely an example...

What I want is to update the table "Group", set the "active" column to 1, if, and only if number of cars below that group has a car that is available for sale.

How can a car be available for sale? If it is registered with a price in the "Cost-table (table3)", then the group and all subgroups this car is within shall be activated.

With those 3 tables in the picture, I want this result for the GroupTable:

Code:

ID  groupID subgroupID active title
1   1          0              1        Cars
2   2          1              1        Red Cars
3   3          2             0         Red Jaguar
4   4          2             1          Red Porsche
5   5          2             1          Red Ferrari
6   6          0             0          Bikes

The main group "Cars" should turn into active, because a car below this group is for sale.

I want Red cars to be active, because some red car is for sale.

I want Red Jaguar to be NOT active (active = 0), because no jaguar has a registered cost (price/prize) in the Cost-table.

The cars Porsche and Ferrari has a cost/prize (a seller), hence update this group and set it to be active.

I want "Bikes" active to be 0, because no bikes exists.

Any suggestions?

So far I've got it "working",... This is close to what I want, but then again not really.

Code:

DECLARE @tbl1 TABLE
(groupID int)

DECLARE @tbl2 TABLE
(groupID int)

DECLARE @tbl3 TABLE
(groupID int)

/* LEVEL 0*/
INSERT INTO @tbl1(groupID)
(
	SELECT DISTINCT t1.groupID
	FROM groups as t1
	LEFT JOIN groups as t2 ON t1.groupID = t2.subgroupID
	LEFT JOIN groups as t3 ON t2.groupID = t3.subgroupID
	LEFT JOIN car as t4 ON t3.groupID = t4.groupID
	LEFT JOIN carcost as t5 ON t4.ID = t5.carID
	where t1.active = 0 AND t2.active = 0 and t3.active = 0
	GROUP BY t1.groupID, t1.title, t5.carID, t5.cost, t4.carname
	HAVING COUNT(t5.ID) > 0
)
UPDATE groups
set active = 1
where groups.groupID IN (Select t1.groupID from @tbl1 as t1);

/* LEVEL 1*/
INSERT INTO @tbl2(groupID)
(
	SELECT DISTINCT t1.groupID
	FROM groups as t1
	LEFT JOIN groups as t2 ON t1.groupID = t2.subgroupID
	LEFT JOIN car as t3 ON t2.groupID = t3.groupID
	LEFT JOIN carcost as t4 ON t3.ID = t4.carID
	where t1.active = 0 AND t2.active = 0 and t1.subgroupID IN (SELECT groupID from @tbl1)
	GROUP BY t1.groupID, t1.title, t4.carID, t4.cost, t3.carname
	HAVING COUNT(t4.ID) > 0
)
UPDATE groups
set active = 1
where groups.groupID IN (Select t1.groupID from @tbl2 as t1);

/* LEVEL 2*/
INSERT INTO @tbl3(groupID)
(
	SELECT DISTINCT t1.groupID
	FROM groups as t1
	LEFT JOIN car as t2 ON t1.groupID = t2.groupID
	LEFT JOIN carcost as t3 ON t2.ID = t3.carID
	where t1.active = 0 AND t1.subgroupID IN (SELECT groupID from @tbl2)
	GROUP BY t1.groupID, t1.title, t3.carID, t3.cost, t2.carname
	HAVING COUNT(t3.ID) > 0
)
UPDATE groups
set active = 1
where groups.groupID IN (Select t1.groupID from @tbl3 as t1);

SELECT * from groups;

As you have probably figured, this works only for a group where there is an exact amount of subgroups, in this case 2 subgroups, (level 0 > level 1 > level 2). If there is a car within the 2. subgroup with a price, then those groups are activated.
I want it to work without the limitation of 2 subgroups, lets say; 4 subgroups...
(of course: I know the total amount of subgroups and the minimum (1-5), hence I can copy paste this code, making it work for "all groups"... But, oh, cmon!

)

A bit off-topic:
Is there another function I can use instead of "Having count(ID) > 0"? My point:
I want to stop the "Count(ID)" if it reaches greater than 0, as in: if it is 1, do not need to count further rows...
The "car-cost"-Table has about 700.000-800.000 rows... So making the count(ID) count up to 30.000 is pointless.
Was I clear enough?

Edit: Hmpf... Maybe I should just do this programmatically, as in through C++/C#, through some recursion, and whatnot! But, oh... cmon!

Wim · #2 (**permalink**) 03-14-11, 07:40

Wow! If all questions on this forum would be that well documented ... Well done!

First a few things I noticed about the data model:
- Naming convention: use plural everywhere or singular everywhere, don't mix: Groups and Car.
- I see no value in declaring both ID and GroupId in the Groups table. I kept Id and threw out GroupId to make it consistent with the other tables.
- In the Groups table. The name of SubGroupId is misleading, I renamed it to ParentGroupId.
- The ParentGroupId of Groups record with id = 1, is set to "0", you should make that NULL or set it to the id of the record itself "1" (so it references itself), but that can be tricky with an identity column. That way you can define a FK constraint on the Groups table between ParentGroupId and groups.Id. As there is no Groups record with Id = 0, it would raise a foreign key violation error.

This is the code you were looking for

Code:

select * from #groups;

WITH CTE (GroupId)
AS
(SELECT #Car.GroupId
From #carcost
	INNER JOIN #car ON
		#carcost.carID = #car.ID
WHERE #carcost.cost > 0
	UNION ALL
SELECT #groups.ParentGroupID
FROM CTE
	INNER JOIN #groups ON
		CTE.GroupId = #groups.ID	
)
UPDATE U
SET U.active = 1
FROM #groups as U
	INNER JOIN (SELECT DISTINCT GroupId
				FROm CTE
				) as T ON
		U.ID = T.GroupId;
		
select * from #groups;

ManyTimes · #3 (**permalink**) 03-14-11, 16:57

Quoting myself:
"do not question the "ID" column in the "group-table", nor anything else, this is purely an example...".

So why you mention the stuff you did, is above me.
SubgroupID is not misleading. ParentgroupID is not misleading. It is a matter of preference... oh, who cares?

Apart from that, big thanks, but one question comes to my mind:
1. The hash tag in front of a table name, never seen it, so why? Or just a preference so you can easier tell what is what (syntax preference only)?
At least my version of sql 2008 does not allow hash tag in front of a table name.

Wim · #4 (**permalink**) 03-15-11, 08:17

Quote:

Quoting myself:
"do not question the "ID" column in the "group-table", nor anything else, this is purely an example...".

You're right. I reread your post. Now I noticed. I missed that when I read your post the first time.
Personal memo: get new glasses or clean current ones.

Quote:

SubgroupID is not misleading.

I don't agree, as it references a higher level Group, not a lower level one (something I would call a subgroup). But "oh, who cares?

"

Quote:

1. The hash tag in front of a table name, never seen it, so why? Or just a preference so you can easier tell what is what (syntax preference only)?
At least my version of sql 2008 does not allow hash tag in front of a table name.

Look up Local Temporary tables. I am using it on a local MSSQL 2008 instance, so it should work for you too. You must define those tables with
CREATE TABLE #Car ... instead of
CREATE TABLE Car ...
Then it should work. Using local temp tables prevents your test database to get cluttered by dozens of little test tables after a while.

ManyTimes · #5 (**permalink**) 03-15-11, 12:58

'll for sure look those local temp tables up, thanks again.

Internet Services

Web Development

Digital Marketing

Consumer Tech