sql to show results - organised data from other tables and databases

I trying to make an ms SQL query to return results including information from other tables and databases.
the primary database is named HistoryMMYY  has a table labeled dbo.SignalHistory that has data arranged as follows [SignalID],[AccountID],[OperCode],[PortNum],[LineNum],[AlarmDate],[AlarmCode],[AlarmZones],[other],[RawData] I need to query that table and return data in the following way, each item is separated by a | help to avoid confusion.


Here is a sample of hISTORY0218.dbo.SignalHistory
SignalID,AccountID,OperCode,PortNum,LineNum,AlarmDate,AlarmCode,AlarmZones,other,RawData
110218,4,53,7,7,2/1/2018 6:00,ZAP,60,ITS HAPPENING,omg really
210218,4,53,7,7,2/1/2018 6:00,FIR,61,,123pdq456
310218,5,16,0,0,2/1/2018 6:00,NYC,,DOOR14567#4$6                            	

Open in new window


Here is a sample of SUBSCRIBER.dbo.Subscriber Data
AccountID,AcctLineCode,AcctNum,AcctNumArea,RadioAcctLineCode,RadioAcctNum,Name,Address,City,State,Zip
1,QUA,14,,,,MALLORDS,8675 SOUTH INTERSTATE 60,DUCKVILLE,IA,65432
2,DC,2346,,,,NO FUN GRADE SCHOOL,301 NORTH WALNUT,BLAND,MO,65656
4,CE,50,,,,HOUND, DON & JOAN,17679 POTATO ROAD 519,GORD,CA,12345
7,UC,2028,,,,DOLL, KEN & BARBIE,12870 STATE ROUTE O,AQUA,NSW,2230

Open in new window


Here is a sample of MISCELLANEOUS.dbo.Signal Codes
ID,Code,Description
1,FIR,Fire Alarm
2,ZAP,Zombie Apocalypse
3,NYC,Not yet closed

Open in new window


Here is a sample of SUBSCRIBER.dbo.Zone Lists
AccountID,Number,Description
4,60,BURGLAR - KITCHEN DOOR
4,61,EAST UPPER LEVEL smoke
5747,1,CONFERENCE ROOM
5747,2,REAR #1 OFFICE
5747,3,REAR #2 OFFICE
5747,4,REAR #3 OFFICE
45,2,BURGLAR - EAST LOWER LEVEL MOTION

Open in new window


SUBSCRIBER.dbo.Subscriber Data.AcctLineCode|SUBSCRIBER.dbo.Subscriber Data.AcctNum | SUBSCRIBER.dbo.Subscriber Data.Name | HistoryMMYY.dbo.SignalHistory.AlarmDate |  MISCELLANEOUS.dbo.Signal Codes.Description | SUBSCRIBER.dbo.Zone Lists.Description | SUBSCRIBER.dbo.Subscriber Data.Address | SUBSCRIBER.dbo.Subscriber Data.City | SUBSCRIBER.dbo.Subscriber Data.State | SUBSCRIBER.dbo.Subscriber Data.Zip | HistoryMMYY.dbo.SignalHistory.PortNum | HistoryMMYY.dbo.SignalHistory.LineNum | HistoryMMYY.dbo.SignalHistory.RawData

Im also only trying to get results for the last 3 hours, i however have no idea how to write this sql, I have only ever modified tables. any help with this query would be greatly appreciated.
LVL 1
csePixelatedAsked:
Who is Participating?
 
Shaun KlineLead Software EngineerCommented:
The comma at the end of the SELECT line should not be there. Here is updated SQL:
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zone.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData,
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   INNER JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   INNER JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND SignalHistory.AlarmZones = Zones.Number
   INNER JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -3, GETDATE())

Open in new window

0
 
Shaun KlineLead Software EngineerCommented:
Hopefully, this will get you started. Just complete the SELECT line with the columns you want displayed.

SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name, <other columns>
FROM [hISTORY0218.dbo.SignalHistory] SignalHistory
   INNER JOIN [SUBSCRIBER.dbo.Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   INNER JOIN [SUBSCRIBER.dbo.Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND SignalHistory.AlarmZones = Zones.Number
   INNER JOIN [MISCELLANEOUS.dbo.Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >- DATEADD(HOUR, -3, GETDATE())

Open in new window

0
 
csePixelatedAuthor Commented:
tried both
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zone.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData,
FROM [History0218].dbo.[SignalHistory] SignalHistory
   INNER JOIN [SUBSCRIBER.dbo.Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   INNER JOIN [SUBSCRIBER.dbo.Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND SignalHistory.AlarmZones = Zones.Number
   INNER JOIN [MISCELLANEOUS.dbo.Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >- DATEADD(HOUR, -3, GETDATE())

Open in new window

and
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zone.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData,
FROM [History0218.dbo.SignalHistory] SignalHistory
   INNER JOIN [SUBSCRIBER.dbo.Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   INNER JOIN [SUBSCRIBER.dbo.Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND SignalHistory.AlarmZones = Zones.Number
   INNER JOIN [MISCELLANEOUS.dbo.Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >- DATEADD(HOUR, -3, GETDATE())

Open in new window

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
csePixelatedAuthor Commented:
left the comma in your example so i removed it,  once parsed it worked however when i try to run it i get
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Zone.Description" could not be bound.
i expanded the hours to 3000 as im working with a backup server that is behind by a few days to a week
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zone.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   INNER JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   INNER JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND SignalHistory.AlarmZones = Zones.Number
   INNER JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -3000, GETDATE())

Open in new window

0
 
Shaun KlineLead Software EngineerCommented:
The table alias for your [Zone Lists] table is Zones. Update that column in your SELECT line to Zones.Description.
0
 
csePixelatedAuthor Commented:
oh, my bad, missed that. ty
now the column headings are perfectly arranged however i am getting no data, why would this be?
to be sure i executed on a current server as well
0
 
Shaun KlineLead Software EngineerCommented:
If the fields in the join are not correct, that would be one reason. I made a guess at which columns should be used based on the sample data provided.

Another reason would be if the join fields have no data. From you sample data, the last signal history row (SignalID: 310218) has no AlarmZones, so no data would be returned as there is no way to match against your [Zone Lists] table.

You can change the INNER JOIN key words to OUTER JOIN to ensure data is always returned. From there, you may be able to determine the correct fields to match on.
0
 
csePixelatedAuthor Commented:
So if any data is left blank the entire line will be skipped?
as you can see the zone list there is no 'AccountID' they simply called the column 'ID', from there is there a way to make absent data show up as nothing?
0
 
csePixelatedAuthor Commented:
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zones.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   INNER JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   INNER JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.ID AND SignalHistory.AlarmZones = Zones.Number
   INNER JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -30, GETDATE())

Open in new window

is still returning nothing i changed the 5th line to reflect SignalHistory.AccountID = Zones.ID
0
 
csePixelatedAuthor Commented:
never mind, im wrong Id is on signal codes...
0
 
csePixelatedAuthor Commented:
I realise you answered my question before i asked it, i tried to set to OUTER JOIN
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zones.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   OUTER JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   OUTER JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.ID AND SignalHistory.AlarmZones = Zones.Number
   OUTER JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -30, GETDATE())

Open in new window


I am now getting

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'JOIN'.
0
 
Shaun KlineLead Software EngineerCommented:
Sorry, it is LEFT JOIN, not OUTER JOIN. Long day.
0
 
csePixelatedAuthor Commented:
It's all good
it would appear the join is not working as the results are only showing information from the Signal History table, that also explains why it was not showing results before.
0
 
csePixelatedAuthor Commented:
I take that back i am getting the info from the 'MISCELLANEOUS.dbo.Signal Codes' table that is the only other table that worked.
0
 
Shaun KlineLead Software EngineerCommented:
If you have the ability to look at the design of the tables in SQL Server Management Studio, you can look at the keys of the tables you need, which will help determine the correct fields to use in the JOIN statements. Without the table designs, it will be difficult to find the correct matching columns.
0
 
csePixelatedAuthor Commented:
i see what happened, it seems as though it worked except the description field, its sorting by the date although its jumbled i need to sort by alarm date the field is in the format YYYY-MM-DD HH:MM:SS.000
It starts with data that does not match in the 1st few fields then its sorted by date
0
 
csePixelatedAuthor Commented:
Worked like a charm, thanks Shaun
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zones.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.ID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -3, GETDATE())
ORDER BY AlarmDate

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.