I got some great help in my previous question. This one builds on top of that. I want to retrieve information of the joined queries but I do not know if that is possible in SQL.

Here is a dummy database with civilians. Every row contains their name, city, street, street number, and job. Minimal example to generate DataBase in SQLite:

import sqlite3
from pathlib import Path


def main():
    build_cmd = """CREATE TABLE civs (
    name TEXT,
    city TEXT,
    street TEXT,
    snumber INTEGER,
    job TEXT) """

    pdb = Path("dummy.db")
    if pdb.exists():
        pdb.unlink()
    conn = sqlite3.connect(str("dummy.db"))

    cur = conn.cursor()
    cur.execute(build_cmd)

    insert_cmds = """
    BEGIN TRANSACTION;
    INSERT INTO civs VALUES ('john', 'dublin', 'butchers_street', '42', 'butcher');
    INSERT INTO civs VALUES ('marie', 'london', 'butchers_street', '3', 'baker');
    INSERT INTO civs VALUES ('keith', 'london', 'camel_road', '7', 'carpenter');
    INSERT INTO civs VALUES ('anna', 'dublin', 'butchers_street', '9', 'butcher');
    INSERT INTO civs VALUES ('steve', 'london', 'maceys_alley', '1', 'blacksmith');
    INSERT INTO civs VALUES ('beth', 'dublin', 'butchers_street', '68', 'grocer');
    INSERT INTO civs VALUES ('lisa', 'dublin', 'church_street', '32', 'nun');
    COMMIT;
    """

    cur.executescript(insert_cmds)
    cur.close()
    conn.commit()
    conn.close()

if __name__ == '__main__':
    main()

Let's say I want to retrieve in one row (so one row per person who lives in a John's street). I use "a" John, because theoretically you can of course have multiple Johns.

• the street where a John lives
• the city where a John lives
• the job of a John
• the name of a person who lives in the same street and city as a John
• the job of a person who lives in the same street and city as a John

I got this far, which retrieves the information of the main query.

SELECT
    civs.street, civs.city, civs.name, civs.job
FROM civs
JOIN
    (SELECT city, street
     FROM civs
     WHERE name='john') jcivs

    ON civs.street = jcivs.street
        AND civs.city = jcivs.city

which gives

but "the job of a John" is sorely missing and I get john as a separate row, which I do not want as we are interested in neighbors of John. So I am not sure how I can refer to jcivs from my outermost SELECT. So this does not work as jcivs does not exist for the outer SELECT:

SELECT
    civs.street, civs.city, jcivs.job, civs.name, civs.job

Expected result:

NOTE: efficiency is important for me. You can assume that it is a cold table and that all columns are indexed.

CodePudding user response：

There is no need to create an id column because SQLite provides for every table a rowid column (which is the actual primary key of the table).

Do a simple self join like this:

SELECT c1.*
FROM civs c1 INNER JOIN civs c2
ON (c2.city, c2.street) = (c1.city, c1.street) AND c2.rowid <> c1.rowid
WHERE c2.name = 'john';

See the demo.

CodePudding user response：

This should be one approach:

WITH civs2 AS (
  SELECT civs.*, row_number() OVER () as id FROM civs
 )
 SELECT
    civs2.street, civs2.city, jcivs.job, civs2.name, civs2.job
FROM civs2
JOIN
    (SELECT city, street, job, id
     FROM civs2
     WHERE name='john') jcivs

    ON civs2.street = jcivs.street
        AND civs2.city = jcivs.city
        AND civs2.id <> jcivs.id

From your query, we:

1. add the job to the jcivs subquery; and
2. Demonstrate the use of an ID by creating a CTE with a ID based on row number. Then, we make sure civs.id <> jcivs.id

If you had an ID to your table, that might be more efficient as we could add this to the join civs.id <> jcivs.id.