2

Im completely stuck in this, might just have been gotten mad and it's real simple.. butncould anyone help me write an sql select statement that would solve this issue ?
Problem statement and examples are on the picture :)

Thanks in advance!
(Data not the actual data needed but resembles structure im trying to do)

Comments
  • 2
    I have an easy and but very ineffitient one.

    Hope it helps.

    @Highlight

    SELECT DISTINCT tblproduct.fldProduct FROM tblproduct

    WHERE (SELECT COUNT(*) FROM tbllevel) <> (SELECT COUNT(DISTINCT tblfactor.fldLevel_Id)

    FROM tblfactor

    WHERE tblfactor.fldProduct_Id = tblproduct.fldProduct_Id);
  • 2
  • 1
    @highlight @p100sch first of all, my description might have been bad, and thanks alot for helping out! But this answer just gives me all product names all the time for some reason 😅🤔

    Also Didnt know <> but just googles it, thats a nice operator

    Also verry sorry, i need to get the levelnames that isnt precent not the product names

    I need it so when i ex make a new product, and add a record in factor with that fk to the new product and an fk to an level, i need to know the levels which still needs to be set together with that new product in order to have a combination of that new product x all levels in the factor table 😅
  • 1
  • 2
    @BitByteBoolean highlight is a bot that just makes a nice syntax highlight of whatever comes after it's mention. But the problem here is that no Item has all levels.
    LEVEL 1: 1, 2, 3, 4
    LEVEL 2: 1, 2
    LEVEL 3: 3
  • 1
    @p100sch exactly and that is what i need my select to figure out, it should tell me what level an item needs filled before it has all levels so in the example you just mentioned the sql select should give me

    Something like

    Level 2 - item 3
    Level 2 - item 4
    Level 3 - item 1
    Level 3 - item 2
    Level 3 - item 4

    This i can then use to know what gaps i need filled so all levels has all product types in the factor table 🤔
  • 3
    @BitByteBoolean In that case you need to select the level and product without joining them. That gives you all possible pairings.

    Then you specify that the productId-levelId pair is not allowed to exist in the factor table.

    My version is ineffitient but it works:

    @Highlight

    SELECT DISTINCT tbllevel.fldLevel_Id, tblproduct.fldProduct_Id

    FROM tblproduct, tbllevel

    WHERE NOT EXISTS(SELECT * FROM tblfactor

    WHERE tblfactor.fldLevel_Id = tbllevel.fldLevel_Id

    AND tblfactor.fldProduct_Id = tblproduct.fldProduct_Id);
  • 3
  • 0
    @p100sch god damn, if i could upvote 100x id smash that button!!!

    Thank you ever so much, did exactly the trick!

    Could you by chance break it down in babysteps/query 101 what you did there? I havent really worked with not exists before so this puzzles my mind alittle, and id like to know what happens under the hood here 🤔😅
  • 1
    @BitByteBoolean
    Because you need to detect a lack of relationship between two elements, you can't join Levels and Item over Factro.
    So first you select all possible combinations of Levels and Products by not joining the two tables. This effect should be familiar to you.
    Now that you have them you need to narrow them down by excluding all Item-Level Pairs that have a Factor entry.
    You create a where statement where you test if a result set contains any elements with exists().
    We pass it a sub select statement where we retrieve all Factors with the Level and Product ID of the current Level-Product pair.
    You do that by referencing the ID fields of the outer selects tables and compare them to the Factors fields.
    Now every time a Level-Product pair is liked via a Factor the result set of the sub select has atleat one row and the exists returns true.
    To only get Pairs that do not share a Factor, you need to negate it.
    For every row the subselect will be executed again and the IDs will be replaced with the current rows IDs.

    Is this a good enough explanation? You can ask me on the specifics if you need to.
Add Comment