SQL join with references to references

Question

I am still new to SQL. I have been making good progress on my project until I ran into this problem. I have tried to search the net for this kind of problem but I cannot find anything specific to this or I am not using the correct keywords in my search.

I have three tables relevant to this problem. Apparently I do not have any kind of "describe table" command to be able to copy the output. My other queries with joins are working as expected.

Table 1 - "Sites"

ID int auto-increment key, site_code short text, site_name short text, more but not relevant.

Table 2 - "Hubs"

ID int auto-increment key, HUB int (lookup from Sites.ID), more but not relevant.

Table 3 - "DialPlan"

ID int auto-increment key, site int (lookup from Sites.ID), HUB int (lookup from Hubs.HUB), more but not relevant.

When viewing the query for "DialPlan" I need to see "DialPlan.site" being replaced by "Sites.site_code" for that specific int. I need to see "DialPlan.HUB" being replaced by "Sites.site_code" for that specific int. Example of table output without joins:

DialPlan: 28, 29, 2, 203 That last number is not relevant. Sites.ID = 29, Sites.site_name = BENN. Hubs.ID = 2, Hubs.HUB = 27, Sites.ID = 27, Sites.site_name = BRAG. So, the output I need to see when using the join is: 28, BENN, BRAG, 203. I am not getting that, I am getting: 28, BENN, BENN, 203.

My search query is:

select 
    Sites.site_code, Sites.site_name, Sites.site_code as Hubs.HUB, 
    DialPlan.OC
from 
    DialPlan
left join 
    Sites on DialPlan.site = Sites.ID
left join
    Hubs on DialPlan.HUB = Hubs.ID
left join
    Hubs on Hubs.HUB = Sites.ID;

I have tried to change field 3 using "AS" and even tried "=" and several other things. If I try to put field 3 as "Sites.site_code" then the output is the same as the first "Sites.site_code" lookup. I am not sure how to proceed. I have tried so many things now that I am not even sure exactly what I have tried. I saw one thread where there were multiple dots per column and I have no idea what that is used for. Does anyone have any ideas?

score 1 · Accepted Answer · answered Dec 17 '15 at 00:03

OK, I have found the answer and it is called an inner query. Actually there are two inner queries. I am posting this so that others may benefit from this. The problem with referencing the first table from two fields that are on the same table is that SQL cannot determine that the second call means a new search. So, you must perform an inner query to resolve this. Here is the code. As an Access admin I have always been able to get this kind result easy but I am behind the power curve for SQL. Here is the working code sample.

SELECT Sites.site_code, Sites.site_name,
    ( SELECT site_code FROM Sites WHERE ID =
        ( SELECT HUB FROM Hubs WHERE DialPlan.HUB = Hubs.ID )
    ),
DialPlan.OC
FROM DialPlan
LEFT JOIN Sites
ON DialPlan.site = Sites.ID;

The output is what I was looking for, "BENN Benning BRAG 203". I hope this helps someone.

Anton · Answer 2 · 2015-12-15T19:52:06.093

0

When you use the AS clause you are just giving that column a title for your table. It will not fill in the data from your alias in place of the data you are aliasing.

If you want to list the data for Hubs.hub you should SELECT Hubs.hub and then give it the title you want using AS mytitle.

Also you dint need that last join as your tables are already joined.

Also, the describe command is DESC and it should work... DESC sites

select 
 Sites.site_code, Sites.site_name, Hubs.HUB as Site, DialPlan.OC
from 
  DialPlan
left join 
  Sites on DialPlan.site = Sites.ID
left join
  Hubs on DialPlan.HUB = Hubs.ID

edited Dec 15 '15 at 19:52

answered Dec 15 '15 at 19:39

Anton

422
2
9

The output from that query is "BENN Benning 27 203" so it is not replacing the 27 with "BRAG". Do I need an inner query or something? In Access I would need to do a inner query but this is not Access. – Goff Dec 15 '15 at 20:24
It seems "DESC Sites" results in "unexpected token: DESC" it may not be implemented in my software. It is not that important. – Goff Dec 15 '15 at 20:30
That query will print out whatever your site_code is, followed by your site_name, followed by your HUB followed by your OC attributes, in that order. If you want to rearrange them just do it in the SELECT. – Anton Dec 15 '15 at 21:37
oh ok, i think im starting to decipher what you are trying to ask :D You want to show the site_code for another site alongside the details of the first site in the query :D so what you do is get the site details and then join the query with another one which fetches the HUB details using a WHERE hub.HUB = Site_code(or whatever the column is that the two tables share.). – Anton Dec 15 '15 at 21:51
Right, I may not have described it very well but the current output is an int for the third column instead of the text from site_code. Column 1 does not have to equal column 3 but they do both pull from the same table. – Goff Dec 16 '15 at 00:17
So the column you want to display goes in your Select, then you join it with itself and in the Where clause you check that your shared values are equal. I think you are using hub and site_code – Anton Dec 16 '15 at 00:31
I need to replace the "Hubs.HUB" with the "site_code" that matches the references from "DialPlan.HUB". I have tried using inner queries but I have not been able to figure this out with SQL. – Goff Dec 16 '15 at 20:28

SQL join with references to references

2 Answers2