Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

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

    "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!
    Attached Thumbnails Attached Thumbnails ThreeTables.jpg  
    Attached Files Attached Files
    Last edited by ManyTimes; 03-12-11 at 20:36.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    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.
    Last edited by ManyTimes; 03-14-11 at 18:05.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    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? "
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    'll for sure look those local temp tables up, thanks again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •