How to design a SQL lookup table where order matters

Question

I am trying to create a lookup table where the order of the elements matters. My lookup table has the following structure

id table1id table2id 
1     1      1
2     1      2
3     1      3
4     2      2
5     2      1
6     2      3

My goal is to find table1id based off the table2ids. So an example query I run is

Select table1id 
from junctionTable 
where table2ids in (1,2,3)
group by table1id
Having count(table1id) = 3

This works, however, it will return both Table1Ids 1 and 2. I only want the Table1ID that has 1,2,3 in that specific order so it should return only TableId =1.

Putting an order column on the table works, however it makes the query a little more difficult as the number of items increase.

select table1id from junctionTable 
where table2id =1 and order =1 and    
table2id = 2 and order =2 
etc...

Is there anything else I can do that I am not thinking of? Or what would be the best way to handle this situation?

The question originated from me trying to normalize tables. See Normalize a table with tightly coupled data for reference

Is the desired ordering reflected by the `id` column? If not, yes you need some other column to define the ordering. Tables, by themselves, have no inherent ordering. — Damien_The_Unbeliever, Sep 09 '16 at 14:20
@Ven yes I do. I have updated my question to include my group by that I forgot. I included having because I could have 1 or only 2 ids and I want only ones with 3 or however many are in the in clause. — Brandon, Sep 09 '16 at 14:27

Arulkumar · Answer 1 · 2016-09-09T14:34:15.530

2

The query below can return your expected result:

WITH CTE AS (
    SELECT table1id, table2id, ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY id) AS CustomOrder
    FROM JunctionTable 
    WHERE table2id IN (1, 2, 3)
) 
SELECT table1id 
FROM CTE
WHERE table2id = CustomOrder
GROUP BY table1id
HAVING COUNT(DISTINCT table2id) = 3

Working demo with the given sample data:

DECLARE @JunctionTable TABLE (id INT, table1id INT, table2id INT);

INSERT INTO @JunctionTable (id, table1id, table2id) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 2),
(5, 2, 1),
(6, 2, 3);

WITH CTE AS (
    SELECT table1id, table2id, ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY id) AS CustomOrder
    FROM @JunctionTable 
    WHERE table2id IN (1, 2, 3)
) 
SELECT table1id 
FROM CTE
WHERE table2id = CustomOrder
GROUP BY table1id
HAVING COUNT(DISTINCT table2id) = 3

edited Sep 09 '16 at 14:34

answered Sep 09 '16 at 14:29

Arulkumar

12,966
14
47
68

1

This solution relies on the `id`s being queried for being specifically 1,2 and 3. It's not going to work for arbitrary values nor if the set being sought after is e.g. 2,1,3. – Damien_The_Unbeliever Sep 09 '16 at 14:35
1

@Damien_The_Unbeliever Here for `table1id = 2` the current order is `2,1,3` and the result is not consider the `table1id = 2` – Arulkumar Sep 09 '16 at 14:40
1

But it breaks on 234. I counts on 1 - x and non missing. Hey it works on 123 +1 – paparazzo Sep 09 '16 at 14:42

score 0 · Answer 2 · answered Sep 09 '16 at 14:45

0

If the ordering matters, then the ordering must be explicitly tracked by making it an attribute of the data. In your current model the ordering is being implicitly stored, and that doesn’t work because you should never assume anything about the order in which SQL stores and returns data.

Given the base table in your question, you could add a varchar ordering the “proper” table2id ordering, something like

Id  table1id  table2id  table2order
1       1         1        1,2,3
2       1         2        1,2,3
3       1         3        1,2,3
4       2         2        2,1,3
5       2         1        2,1,3
6       2         3        2,1,3

This of course is not normalized, so alternatively you could have a second table:

table1id  table2order
    1        1,2,3
    2        2,1,3

Using one or the other of these should support your objectives.

answered Sep 09 '16 at 14:45

Philip Kelley

39,426
11
57
92

@Phillip Would creating a varChar column that lists the order be more efficient/better database practice then creating an Order column that is an int? – Brandon Sep 09 '16 at 15:00
Ultimately, it depends on how the data is being worked with. What identifies "proper order" for a table1id? If this is regularly defined, managed, worked with as a set of rows in a table, then the column solution might be best (though such queries do get pretty ugly). If it is regularly stored and worked with as some form of simple delimited list, then the "single attribute" tag idea is appropriate (and imho would be much simpler to work with). – Philip Kelley Sep 09 '16 at 16:04
@Phillip Thank you for the help. This question originated trying to normalize a table where I had 3 id columns where the order was being tracked since all the data was in one row. However, I wanted to make this more dynamic and not have to create a new column each time we wanted to track another material. Thinking about it, this should have been my question to begin with. – Brandon Sep 09 '16 at 16:32

score 0 · Answer 3 · answered Sep 09 '16 at 14:50

0

select table1id 
from 
(
select table1id, (table2id - id) as diff
from junctionTable 
where table2ids in (1,2,3)
)
group by table1id, as diff 
having count(*) = 3

answered Sep 09 '16 at 14:50

paparazzo

44,497
23
105
176

Edmond Quinton · Answer 4 · 2016-09-09T15:27:27.560

In order to solve this problem, you will have to create a lookup table that defines the desired order of the target table2id’s you want to use since the INclause does not impose an order when matching the two sets.

If you have SQL Server 2012 or higher then you can use the LAG windowing function to help solve the problem as follow:

DECLARE @table2IdInOrder TABLE
(
    OrderID  INT IDENTITY(1,1)
    ,table2id INT
);

INSERT INTO @table2IdInOrder
VALUES(2), (1), (3);


WITH CTE_JT AS (
    SELECT   JT.id
            ,JT.table1id
            ,JT.table2id
            ,JT.table2id - LAG(JT.table2id, 1, 0) OVER(PARTITION BY JT.table1id ORDER BY JT.id) AS DeltaID
            ,COUNT(JT.table2id) OVER(PARTITION BY JT.table1id) AS RecordCount
    FROM @junctionTable JT
    WHERE   JT.table2id IN (SELECT table2id FROM @table2IdInOrder)
)
, CTE_OT
AS
(
    SELECT   OT.OrderID
            ,OT.table2id
            ,OT.table2id - LAG(OT.table2id, 1, 0) OVER(ORDER BY OT.OrderID) AS DeltaID
            ,COUNT(OT.table2id) OVER(PARTITION BY (SELECT 1 AS ID)) AS RecordCount
    FROM    @table2IdInOrder OT
)
SELECT      DISTINCT JT.table1id
FROM        CTE_JT JT
INNER JOIN  CTE_OT OT ON OT.table2id = JT.table2id AND OT.DeltaID = JT.DeltaID AND OT.RecordCount = JT.RecordCount;

score 0 · Answer 5 · answered Sep 09 '16 at 17:33

This query checks if the order of Id and Table2Id is the same.

WITH CTE AS (
    SELECT table1id, table2id
    ,orderIsOK = ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY id) 
               - ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY table2id)   
    FROM JunctionTable 
    WHERE table2id IN (1, 2, 3)
) 
SELECT table1id 
FROM CTE
WHERE orderIsOK = 0
GROUP BY table1id
HAVING COUNT(DISTINCT table2id) = 3

Alternatively you may wish to specify desired order explicitly. In the below example required order is specified as (2,1,3)

WITH desiredOrder(rowNumber, value) as (
    select * 
    from (
        values (1,2)
        ,(2,1)
        ,(3,3)
        ) t (rowNumber, value)
),  
CTE AS (
    SELECT table1id, table2id
    ,orderIsOK = ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY id)
            - do.rowNumber
    FROM @JunctionTable t
    JOIN desiredOrder do ON t.table2id = do.value
) 
SELECT table1id 
FROM CTE
WHERE orderIsOK=0
GROUP BY table1id
HAVING COUNT(DISTINCT table2id) = 3

How to design a SQL lookup table where order matters

5 Answers5