Pairing a row in a table with other row(s) in the same table?

Question

0.00/5 (No votes)

See more:

I need to make a view that pairs rows in a table that has this DDL:

SQL

CREATE TABLE Events (
    source        TEXT,
    entity1       TEXT,
    entity2       TEXT,
    datetime      TEXT,
    backOdd1      TEXT NOT NULL,
    backOdd2      TEXT NOT NULL,
    insertionDate TEXT,
    PRIMARY KEY (
        source,
        entity1,
        entity2
    )
);

Vague depiction of what I have in mind

I'm currently in an SQLite database and I'm losing my head over this. I'm still noobish with databases and don't even know where to begin with this. I tried several times messing with a SELECT statement and an INNER JOIN to which I don't actually know what to join to. If anyone could give me a direction that would be great

Posted 4-Jan-16 15:46pm

Member 12244050

Add a Solution

Comments

Sascha Lefèvre 4-Jan-16 23:12pm

Do you want to do this all in SQL only or do you want to feed it into some application which you develop? In the latter case I could probably help.

Member 12244050 4-Jan-16 23:43pm

As a matter of a fact I am developing a Java application with Selenium along this. The program inputs the Events as a row in the Events table in the SQLite database. Now I need to find a way to group them in a way that you can see an Event being pared to another Event in the same table, with only two conditions: the sources must be different and the two entities must be the same (ignoring order of course).

2 solutions

Solution 1

I think something like this is what you're after:

SQL

SELECT e1.source, e2.source FROM Events e1 INNER JOIN 
(SELECT source FROM Events e2 
ON e1.entity1=e2.entity1 AND e1.entity2 = e2.entity2 AND e1.source <> e2.Source)

Posted 4-Jan-16 21:17pm

Midi_Mick

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Accepted Answer · 2016-01-05T11:23:00

Solution 2

I wouldn't know of a way to do this with SQL only in SQLite. So this is the best solution I can think of:

1. Get all different entity1/entity2-pairs:

SQL

SELECT DISTINCT entity1, entity2 FROM Events;

2. In Java filter out the duplicates (those pairs that only differ in order).

3. Loop through the remaining pairs and query the matching events:

SQL

SELECT * FROM Events WHERE (entity1=@e1 AND entity2=@e2) OR (entity1=@e2 AND entity2=@e1);

Where @e1 and @e2 are the parameter names for the filtered entity pairs.

Posted 5-Jan-16 11:23am

Sascha Lefèvre

Comments

Member 12244050 5-Jan-16 21:02pm

I just came here to close the topic and I saw that solution. That is farily my solution. I unfortunately had to take it to Java instead of doing it all on SQLite. Felt it was much easier to compare the events on the software rather than in a view. Thank you all for the answers!

Sascha Lefèvre 6-Jan-16 3:17am

You're welcome. Good Luck!