Databases
StackOverflow2013
Postsdbo
POSQL Table Creation from Joining Data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Table Creation from Joining Data
    primarykey
    data
    text
    <p>Before I lay out the question, here’s the information concerning my data:</p> <pre><code>Table Name: dbo.DecodedCSVMessages_Staging Columns: MMSI, Message_ID, Time, Vessel_Name, Ship_Type, IMO, Dimension_to_Bow, Dimension_to_stern, Dimension_to_port, Dimension_to_starboard, Draught, Longitude, Latitude </code></pre> <p>I need to create a New Table. This following is what I need in the table:</p> <p>I am interested in all this data, <strong>but I only need Message_ID’s that are 1 or 3. ** Problem is, Message_ID’s 1 and 3 **lack</strong> the following: (Which is only available with Message_ID's 5.) </p> <pre><code>Vessel_Name, Ship_Type, IMO, Dimension_to_Bow, Dimension_to_stern, Dimension_to_port, Dimension_to_starboard, Draught </code></pre> <p>For Message_ID’s 1 and 3, those columns are marked NULL. All they have is </p> <pre><code>Longitude, Latitude, Time, MMSI </code></pre> <p>(which are <strong>all marked NULL</strong> for <strong>Message_ID's equaling 5</strong>)</p> <p>MMSI is the primary key in this instance. Message_ID’s 1, 3 and 5 will all have MMSI numbers that represent a given ship. These MMSI’s though are reoccurring as each ship sends out multiple Message’s of type 1, 3 and 5. So say we have an MMSI of 210293000, This number will be alongside several Message_ID’s different types. So what I need to do is grab all the Message_ID’s that are 1 and 3 and append the information from the Message_ID’s that are 5 to the1’s and 3’s. So in that, the columns are no longer NULL. </p> <p>Last but not least, I have to select only Message_ID 1’s and 3’s that fall within the following: </p> <pre><code>Where Latitude &gt; 55 and Latitude &lt;85 and Longitude &gt; 50 and Longitude &lt; 141; </code></pre> <p>Example of how a few columns look:</p> <pre><code>MMSI/ Message_ID /Time/Ship_type/Vessel_Name/Latitude/Longitude 21029300, 3, 2012-06-01, NULL, NULL, 56.528003, 85.233443 21029300, 5, 2012-07-01, 70, RIO_CUBAL, NULL, NULL 2109300, 1, 2012-08-01, NULL, NULL, 57.432345, 131.123343 2109300, 1, 2012-09-01, NULL, NULL, 62.432345, 121.123343 2109300, 1, 2012-09-02, NULL, NULL, 65.432345, 140.123343 21029300, 5, 2012-08-01, 70, RIO_CUBAL, NULL, NULL </code></pre> <p>The end result would be as follows from this data:</p> <pre><code>21029300, 3, 2012-06-01, 70, RIO_CUBAL, 56.528003, 85.233443 2109300, 1, 2012-08-01, 70, RIO_CUBAL, 57.432345, 131.123343 2109300, 1, 2012-09-01, 70, RIO_CUBAL, 62.432345, 121.123343 2109300, 1, 2012-09-02, 70, RIO_CUBAL, 65.432345, 140.123343 </code></pre> <p>Thanks! </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdpalm
    1. USdpalm
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Table Creation from Joining Data
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COfor one, your question is hard to read. i improved it, but you could polish it further. then we'd probably be more interested if you could something you tried and that failed for one reason or another (please mention the reason).
      singulars
      1. POSQL Table Creation from Joining Data
      1. USmnagel
    2. COFor a given ship you have multiple rows with `Message_ID` being `1` or `3`? And you have multiple rows with `Message_ID` being `5` for those ships, too? How do you intend to connect them? Meaning: Which 3-valued row do you want to connect to which 5-valued row, for example?
      singulars
      1. POSQL Table Creation from Joining Data
      1. USOnkel Toob
    3. COThat's exactly it, a single ship will blast out many message 1, 3 and 5. but each message contains specific information. The message 1 and 3's have lat and long but don't have vessel name or type etc.. They only blast out that information in message 5's. But I need that info as well. So i need to create a new table with only the message 1 and 3's (within my given lat longs), but with the information from the message 5's included. (as it is not included with 1's and 3's)
      singulars
      1. POSQL Table Creation from Joining Data
      1. USdpalm
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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