CODEWITHSUNDEEP
sqlinner-join
freddy
freddy

Reputation: 394

Query three tables with inner join

I'm working on a sql database with three tables.

PageIds Links Ranks

I want to query the database with a page name and find all links for the given page name, furthermore I want to get Links sorted by a Score.

The PageName used for the query either exists as PageA or as PageB, and if PageA then I want PageBs PageName and PageBs PageScore - and vice versa.

I know I have to use inner joins, but how should I handle 'or' in inner join and how do get PageId in PageIds, to query other table and then PageName in PageIds.

This what I have tried, but it take quite some time and then I will not get the opposite PageName

select * from PageIds 
inner join Links
on Links.PageAId = PageIds.PageId or Links.PageBId = PageIds.PageId
inner join Ranks 
on Ranks.PageId = Links.PageBId
where PageIds.PageName = @searchternm

PageIds

Get PageId for PageName matching @searchterm

+--------+----------+
| PageId | PageName |
+--------+----------+
|   1234 | NameA    |
|   4321 | NameB    |
|   3321 | NameC    |
+--------+----------+

Links

if previously found PageId = PageAId, then get PageBId and PageName for PageBId, else get PageAId and PageName for PageAId

+---------+---------+
| PageAId | PageBId |
+---------+---------+
|    1234 |    3321 |
|    4321 |    3321 |
|    1234 |    4321 |
+---------+---------+

Ranks

if previously found PageId = PageAId, then get score for PageBId, else get score for PageAId

+--------+-----------+
| PageId | PageScore |
+--------+-----------+
|   1234 |      1    |
|   3321 |      4    |
|   4321 |      2    |
+--------+-----------+

If input is NameB then the output should be:

PageName, Score

NodeC, 4
NodeA, 1

Thanks for your help.

Upvotes: 0

Views: 102

Answers (1)

Eterm
Eterm

Reputation: 1808

If I understand your requirements correctly you'll want to join the PageIDs table twice, once as PageAIDs and once as PageBIDs.

Then have one query pick up pageA details where pageB matches, then union it with a reversed query for pageB details where pageA matches.

SELECT PageA.PageName, RanksA.Score FROM Links
INNER JOIN PageIDs [PageA] on Links.PageAID = PageA.PageID
INNER JOIN PageIDs [PageB] on Links.PageBID = PageB.PageID
INNER JOIN Ranks [RanksA] on Links.PageAID = RanksA.PageID
WHERE PageB.PageName = @PageName
UNION 
SELECT PageB.PageName, RanksB.Score FROM Links
INNER JOIN PageIDs [PageA] on Links.PageAID = PageA.PageID
INNER JOIN PageIDs [PageB] on Links.PageBID = PageB.PageID
INNER JOIN Ranks [RanksB] on Links.PageBID = RanksB.PageID
WHERE PageA.PageName = @PageName

Upvotes: 1

Related Questions