Member Avatar for 54uydf

my table has 2 fields, both are Keys.
I save similar items in that table , like if 2 items are similar but have different Ids (in another table) I use this table to know that they are similar.

so if items 1111, 1112 are the same and 1000,1002,1004 are the same, it'll looks like this-
field1, field2
1111, 1112
1112, 1111
1000, 1002
1000, 1004
1002, 1000
1002, 1004
1004, 1002
1004, 1000

now I want to display the option to delete the connection between 2 items using a form, but I don't want the user to pick 1111,1112 AND 1112,1111 for removal, I want 1 connection to be displayed, and then I'll twist the vars and remove the other connection, but I don't know how to write a query that displays each connection only once!
help please!

  • 2 Contributors
  • 3 Replies
  • 227 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

— is right that the trick is to treat each pair in a canonical order so the unordered pair appears only once. A concise, display-only approach is to normalize each row on the fly and select distinct normalized pairs:

SELECT DISTINCT
  LEAST(field1, field2) AS id_a,
  GREATEST(field1, field2) AS id_b
FROM your_table
ORDER BY id_a, id_b;

That returns one row per unordered connection. If you want to exclude self-links use WHERE id_a <> id_b (or use field1 < field2 as a simpler filter when you know both directions exist).

For a long-term fix (prevent storing both directions at all), add schema-level canonical columns and a UNIQUE index so duplicates cannot be inserted. Example:

ALTER TABLE your_table
  ADD COLUMN canonical_a INT AS (LEAST(field1, field2)) STORED,
  ADD COLUMN canonical_b INT AS (GREATEST(field1, field2)) STORED,
  ADD UNIQUE INDEX uniq_pair (canonical_a, canonical_b);

That makes inserts idempotent and simplifies deletes: either delete by the canonical pair or remove both directions in one statement:

DELETE FROM your_table
WHERE (field1 = ? AND field2 = ?)
   OR (field1 = ? AND field2 = ?);

Notes and cautions: concatenating IDs into strings (e.g., CONCAT) can mis-order or be ambiguous for numeric IDs; using two canonical numeric columns avoids that. Also, normalizing for display is quick, but enforcing uniqueness at the schema level is the safest long-term solution.

Recommended Answers

All 3 Replies

Member Avatar for pritaeas

One way would be to create a calculated column that concatenates both fields with the smallest first, so you can order and distinct that column.

Although that may work, I guess there has to be a better way.

Member Avatar for 54uydf

I'm not sure what u meant but doing a simple DISTINCT on 1 culumn won't work since I'm going to get
1111,
1112,
1000,
1002,
1004.

and if I wanted to do a second query for each row to find it's conection then I'm going to get
1111-1112
and then for 1112 gonna get 1111, so I have the same connection twice..
:S

Member Avatar for pritaeas

Something like:

SELECT field1, field2, IF(field1 < field2, CONCAT(field1, '-', field2), CONCAT(field2, '-', field1)) AS field3
FROM table
GROUP BY field3
ORDER BY field3

This may not work for you in all case, but it will give you the idea.

Edited by pritaeas because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.