July 13, 2016 at 6:20 am
I have a question I am hoping that the members here can help me with. I have little experience but was tagged to work with the development team as the database admin. In the military so dont have much choice to try =P
[Primary] Table
(pk) Server_ID | Date_Entered
[Mission] Name
(PK) Mission_ID | Server_ID (FK to Primary)
[Site] Table
(PK) Site_ID | Server_ID (FK to Primary)
[Location] Table (Static Fact Table)
Site_ID | Latitude | Longitude
[Connection] Table (Static Fact Table)
Connection_ID | Site_Start |Site_End
My query looks like this:
USE [ServerDB]
SELECT Mission.Project_ID, Primary.Server_ID, Site.Site_ID, Connection.Connection_ID, Connection.Site_Start, Location.Latitude, Location.Longitude, Connection.Site_End, Location.Latitude AS End_Lat, Longitude AS End_Long
FROM Mission_ID LEFT JOIN
Primary ON Mission.Server_ID = Primary.Server_ID LEFT JOIN
Site ON Primary.Server_ID = Site.Server_ID LEFT JOIN
Location ON Site.Site_ID = Location.Site_ID LEFT JOIN
Connection ON Location.Site_ID = Connection.Site_Start LEFT JOIN
Connect as Connection2 ON Location.site_ID = Connection.Site_End
Where Mission_ID = ‘Mission ID Target’
My output comes out mostly correct however the Site_End Latitude/Longitude (End_lat/End_Long) is just repeating the Site_Start Lat/long. Looks like...
| Mission_ID | Server_ID | Site_ID | Connection_ID | Site_Start | Latitude | Longitude | Site_End | End_Lat | End_Long
| Test_Mission | TRN001002 |Lackland | BaslineConnect | Lackland | 88.5544 | -145.8724 | Robins | 88.5544 | -145.8724
I am sure I am either going about this incorrectly or missing something obvious.
Please help!
Thanks!
David
July 13, 2016 at 6:36 am
You'll need to join to your location table again, using the Site_Id for the end lat and long. At the moment, the query only joins on the start location site_ID so it only returns the lat and long for there.
I'd also consider obfuscating the lat and long on your posted question. If you're military there may be OpSec considerations.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 13, 2016 at 6:49 am
Ok I will try that, the data/columns and such are all made up, so no ops concerns. 🙂
July 13, 2016 at 8:18 am
Morning,
Can you please type a example of how you would change my query to rejoin locations? i keep trying to rejoin the table but getting repeating and/or duplicating the elat/elong still.
Thanks for your help!
David
July 13, 2016 at 8:32 am
Without going into specifics, the concept is something like this:
SELECT...
FROM MissionTable mt --mt is an alias
LEFT JOIN ReferenceTable rt1 ON mt.SourceLocation = rt1.Location
LEFT JOIN ReferenceTable rt2 ON mt.DestinationLocation = rt2.Location
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 13, 2016 at 8:35 am
From your post:
SELECT Mission.Project_ID, Primary.Server_ID, Site.Site_ID, Connection.Connection_ID, Connection.Site_Start, Location.Latitude, Location.Longitude, Connection.Site_End, Location.Latitude AS End_Lat, Longitude AS End_Long
FROM Mission_ID LEFT JOIN
Primary ON Mission.Server_ID = Primary.Server_ID LEFT JOIN
Site ON Primary.Server_ID = Site.Server_ID LEFT JOIN
Location ON Site.Site_ID = Location.Site_ID LEFT JOIN
Connection ON Location.Site_ID = Connection.Site_Start LEFT JOIN
Connect as Connection2 ON Location.site_ID = Connection.Site_End
Where Mission_ID = ‘Mission ID Target’
It appears that this line:
Connect as Connection2 ON Location.Site_ID = Connection.Site_End
is the bit that needs adjusting:
Connect as Connection2 ON Location.Site_ID = Connection2.Site_End
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 13, 2016 at 8:35 am
Try this, you might need to check if you need to use the start or the end of the connection or both. I don't know, so I just left that part commented.
SELECT m.Project_ID,
p.Server_ID,
s.Site_ID,
c.Connection_ID,
c.Site_Start,
l.Latitude,
l.Longitude,
c.Site_End,
l2.Latitude AS End_Lat,
l2.Longitude AS End_Long
FROM Mission AS m
LEFT JOIN [Primary] AS p ON m.Server_ID = p.Server_ID
LEFT JOIN Site AS s ON p.Server_ID = s.Server_ID
LEFT JOIN Connection AS c ON s.Site_ID = c.Site_Start --OR s.Site_ID = c.Site_End
LEFT JOIN Location AS l ON c.Site_Start = l.Site_ID
LEFT JOIN Location AS l2 ON c.Site_End = l2.Site_ID
Where Mission_ID = 'Mission ID Target';
July 13, 2016 at 8:35 am
Edit: Not needed after Thomas and Luis's answers.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 13, 2016 at 9:14 am
Thank you for all the responses, I will work on changing (and understanding) the examples and incorporate it into my actual query. =)
Cheers!
July 13, 2016 at 12:29 pm
Thanks for all the help! I used Luis's example to help wrap my head around it and incorporated it into my main query and it works great.
Just started using SQL last month so learning by firehouse at work...:crazy:
David
July 13, 2016 at 1:14 pm
You need to be sure that the information is correct. In SQL queries, it's very easy to get information that seems correct but it isn't. I gave you what seemed logical to me (with a format that I find easier to read), but I might have been wrong. Understand the query and ask questions that you might have. Get help from someone else to test the code.
July 14, 2016 at 9:05 am
Luis Cazares (7/13/2016)
You need to be sure that the information is correct. In SQL queries, it's very easy to get information that seems correct but it isn't. I gave you what seemed logical to me (with a format that I find easier to read), but I might have been wrong. Understand the query and ask questions that you might have. Get help from someone else to test the code.
In addition to the solution proposed by Luis Cazares, do heed his advice as to what essentially amounts to "just enough knowledge to be dangerous" or "the apprentice sorcerer". Most emphatically, the fact that records are being returned is no guarantee that you are getting EXACTLY what you EXPECTED. Especially in a hundred-line + results set where it might be difficult to notice that some records are missing. The golden rule applies: if you do not fully understand the code given to you, DO NOT PUT IT IN PRODUCTION. This one rule is guaranteed to save you untold amount of grief later down the road.
Be advised that you have only scratched the surface.
It takes time and experience to move from competence level 1 (unconscious incompetence - don't even know you don't know) to level 4 (unconscious competence - you've become so good at it, it just flows out without even having to think hard about it). In between there are levels 2 (conscious incompetence - there's hope for you because you know you have to work at it) and level 3 (conscious competence - you're good but you take time to work out the correct solution). And a confirmation of it all is that when asked how they rate their knowledge, level 1's give themselves the highest ratings.
First and foremost, you should set out to have a clear understanding between an [font="Courier New"]INNER JOIN[/font] and a [font="Courier New"]LEFT OUTER JOIN[/font].
Next up on your "TODO" list is to understand the impact of [font="Courier New"]NULL[/font] on the data being retrieved.
The issue of date handling is also full of gotcha's. There are ways to do it really wrong.
Reading this forum regularly is a good way to accelerate your progress. In addition to producing correct results, you will also have to get into performance issues.
A minor pet peeve on writing style: being accustomed to reading from left to right and top to bottom
FROM Mission AS m
LEFT JOIN [Primary] AS p ON m.Server_ID = p.Server_ID
bothers me a bit, I prefer to show that the second table be explicitely marked as depending from the first table.
FROM Mission AS m
LEFT JOIN [Primary] AS p ON p.Server_ID = m.Server_ID
You have just stepped into a brave new world. Welcome.
July 14, 2016 at 9:32 am
j-1064772 (7/14/2016)
A minor pet peeve on writing style: being accustomed to reading from left to right and top to bottom
FROM Mission AS mLEFT JOIN [Primary] AS p ON m.Server_ID = p.Server_ID
bothers me a bit, I prefer to show that the second table be explicitely marked as depending from the first table.
FROM Mission AS m
LEFT JOIN [Primary] AS p ON p.Server_ID = m.Server_ID
It's funny how my preference goes the opposite way for very similar reasons. 😀
For me, columns on the left should belong to the table on the left and columns on the right should belong to the table on the right, but to each his own. As with the use of leading & trailing commas or tabs & spaces, it's basically a matter of habit.
Thank you for the post going further on the advice. 😉
July 14, 2016 at 10:33 am
Luis Cazares (7/14/2016)
j-1064772 (7/14/2016)
Thank you for the post going further on the advice. 😉
My pleasure.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply