ReadyFreddy ReadyFreddy - 5 months ago 7
SQL Question

Get data from two tables using by data from another table in SQL Database

I have got 3 tables on my database. Lets assume they are named as ACC, POS, CON.

At ACC table i have got ID and NAME columns. At POS table, there is LAT and LONG columns and at CON table, AccID and FrID columns.

to give an example.

ACC / POS / CON
ID NAME / ID LAT LONG / AccID FrID
1 Mike 1 10 15 1 2
2 Bob 2 20 25 1 4
3 Jack 3 18 21 2 3
4 Rocky 4 37 45 2 1


This is the data from my 3 tables. Now i want to select all LAT and LONG values from POS and NAME values from NAME where AccID in FrID.

To be more specific, i want to check for Mike's friend's Lat Long. At Con table there is two friends of Mike, ID=2 and ID=4 so i want to get a table like.

ACC_NAME LAT LONG
Bob 20 25
Rocky 37 45


can you give me a query example for this problem. Thank you.

Answer

You can try by using INNER JOIN. Here is full Query:-

SELECT ACC.Name, POS.LAT, POS.LONG FROM CON
INNER JOIN ACC ON CON.FrID = ACC.ID
INNER JOIN POS ON POS.ID = ACC.ID
WHERE CON.AccID = 1

Explanation Here

  1. Get All friends of Mike By

    SELECT * FROM CON WHERE CON.AccID = 1 -- Mike Account Id

  2. Get Friends Name by Join with Acc Table

    SELECT ACC.Name FROM CON INNER JOIN ACC ON CON.FrID = ACC.ID WHERE CON.AccID = 1

  3. Get Lat ang Long by Join with POS Table

    SELECT ACC.Name, POS.LAT, POS.LONG FROM CON INNER JOIN ACC ON CON.FrID = ACC.ID INNER JOIN POS ON POS.ID = ACC.ID WHERE CON.AccID = 1