Clash Royale CLAN TAG#URR8PPP

2 join queries in sqlite

can any one help me on this, Im new with databases and queries. I'm using sqlite; and my database contains 2 tables. edges and nodes .
tables are like:

nodes
_______________________
NO name family
9808 antony bits
6757 saly wood



edges
_______________________
ID Source Target
1 9808 6757
2 9808 6757
3 6757 9808
4 6757 9808
5 9808 6757


the query should replace source and target with "name&family" and "name&family" then count the frequency of each edges which results in this:

Source target frequency
"antony bits" "saly wood" 3
"saly wood" "antony bits" 2


1 Answer
1

start off with .mode column, then run this query:

.mode column

select a.name||' '||a.family as source, b.name||' '||b.family as target, count() as freq from edges as c
left join nodes as a on c.source = a.NO
left join nodes as b on c.target = b.NO
group by source, target
order by freq desc ;


output is:

source target freq
----------- ---------- ----------
antony bits saly wood 3
saly wood antony bits 2


The trick is double join the table on the source and target columns.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.