StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Taking into account all the information from the comments section (especially about the one-time-thing note) you can try the following SQL statement. This will, however, only work if the relevant data of the "type 5" messages really is consistent as you stated!</p> <pre><code>SELECT Messages.MMSI ,Messages.Message_ID ,Messages.TIME ,Type5Messages.Vessel_Name ,Type5Messages.Ship_Type ,Type5Messages.IMO ,Type5Messages.Dimension_to_Bow ,Type5Messages.Dimension_to_stern ,Type5Messages.Dimension_to_port ,Type5Messages.Dimension_to_starboard ,Type5Messages.Draught ,Messages.Longitude ,Messages.Latitude INTO [DataBaseName].[dbo].[YourNewTableName] FROM dbo.DecodedCSVMessages_Staging Messages LEFT OUTER JOIN ( SELECT DISTINCT MMSI ,Vessel_Name ,Ship_Type ,IMO ,Dimension_to_Bow ,Dimension_to_stern ,Dimension_to_port ,Dimension_to_starboard FROM dbo.DecodedCSVMessages_Staging WHERE Messages.Message_ID = 5 ) Type5Messages ON Messages.MMSI = Type5Messages.MMSI WHERE Messages.Message_ID IN (1,3) AND Messages.Latitude > 55 AND Messages.Latitude < 85 AND Messages.Longitude > 50 AND Messages.Longitude < 141; </code></pre> <p>In this statement you select all "type 1" and "type 3" rows and join them with all "type 5" rows that have the same <code>MMSI</code> value. If the "type 5" information is consistent regarding the selected columns, there will be only one entry for each "type 5" row and therefore every "type 1" and "type 3" row will appear only once. You should definitely do some testing, though.<br> The <code>INTO [DataBaseName].[dbo].[YourNewTableName]</code> line (which you will have to adjust naming-wise) creates a new table (if you choose a name that has not been used yet) with the original table's data types and inserts the selected rows. You might want to alter this table later on if you need indixes, keys, relationships or whatsoever.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload