0

I am quite new to SQL and Sqlite and I might not be doing this right, however and for academic reasons I am trying to learn the basics with a dirty database I created myself. Pardon me if the vocabulary is not correct, I am still in learning process.

I created a set of tables each with a different kind of entity but all of them with a similarity: they share multiple ocurrences of a same Foreign Key with different values.

Table M1: Entity with 2 items:

+------------+---------+--------+-------+
 Primary Key  Unique ID   Data1   Data2
+------------+---------+--------+-------+
      1          x1      103021   103022

Table M2: Entity with 3 items:

+------------+---------+--------+-------+-------+
 Primary Key  Unique ID   Data1   Data2   Data3
+------------+---------+--------+-------+-------+
      1          y1      103021   103022  103001

All previous data columns are Foreign Keys to a Unique Key hereafter (Table N): 

+------------+---------+--------+-------+
 Primary Key  Unique ID   Data1   Data2
+------------+---------+--------+-------+
      1         100002     ...     ...
      2         103001     ...     ...
      3         103021     ...     ...
      4         103022     ...     ...

All I need to do is to extract all the entities which share a given unique item (My Foreign Key). For instance given 103022 two entities would be given: x1 and y1.

Now I have been trying several days and I have managed to get some of the info I need with a JOIN statement.

INNER JOIN:

SELECT N.id, T1.id FROM N INNER JOIN M1 as T1 on T1.data1=N.id OR T1.data2=N.id

CROSS JOIN:

SELECT N.id, T1.id FROM N CROSS JOIN M1 as T1 WHERE T1.data1=N.id OR T1.data2=N.id

INNER JOIN will only work if I do one table at a time and post-process it in real time with python, and I understand that CROSS JOIN generates a cartesian product of my data and can easily use too much memory when I will add the other tables and boolean conditions. So I think it is not an option.

Are there any other ways I have not thought of?

Thank you All!

Chang
  • 37
  • 8
  • 1
    can you let know what is output you want ...? – Pranay Rana Dec 19 '17 at 09:14
  • 1. Your text is not clear about M1, M2 & N or what output you want. Use enough sentences to clearly explain. Read & act on [mcve]. Tell us exactly what columns are FKs to other columns. 2. You have misconceptions about implementations. Those two joins will be implemented in the same way in any practical DBMS. https://stackoverflow.com/q/44917/3404097 Also, you need to learn a lot more about what parts of queries mean & how to write straightforward queries before you should worry about implementation'optimization. – philipxy Dec 21 '17 at 06:26
  • @philipxy : First of all, I made clear that I am here to learn, no need to criticize. Whereas I can easily find the answer to all your doubts in my question I have read your link. It does not help much. On the other hand, you cannot possibly suggest that INNER and CROSS Join have the same effect, CROSS join makes my process crash. – Chang Dec 21 '17 at 09:36
  • I don't know what you mean by "criticize". I am telling you some helpful stuff. Re the link, comma is cross join with lower precedence than the joins using "join"--there is a comment by me on the question to that effect. Re your crash, whatever your problem is, it isn't cross join where vs inner join on. https://stackoverflow.com/a/25957600/3404097 https://sqlite.org/optoverview.html We disagree re the clarity of your message. It might be saying things consistent with what you are doing but it doesn't explain it. – philipxy Dec 21 '17 at 11:20
  • Hi. In case you have some choice over the design of these tables, if you want queries like that then they are likely poorly designed. They are "pivoted"--data variation has been put into column names. See an intro to relational database design. Pivoted tables are trying to look like formatted output for people, but they are are awkward to query. – philipxy Dec 21 '17 at 20:18

1 Answers1

1

i think you are looking for Union of table 

SELECT N.id, T1.id FROM N INNER JOIN M1 as T1 on T1.data1=N.id OR T1.data2=N.id
Union
SELECT N.id, T1.id FROM N INNER JOIN M2 as T1 on T1.data1=N.id OR T1.data2=N.id

Note : considering N as main table and M1,M2 is other tables you want to join with.

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • Thank you, it works. Is this the most efficient way to do it? – Chang Dec 19 '17 at 09:42
  • @Chang - welcome..if you want from different table than this is way I know and mostly there is no other way i guess ...please do accpet/upvote if it workes or helped you – Pranay Rana Dec 19 '17 at 09:49