CODEWITHSUNDEEP
sqlsql-servert-sql
dpalm
dpalm

Reputation: 143

SQL Server 2008 Complex Query & Table Join

This is a little difficult to explain but I will try my best. I have a database which maintains information on Marine Shipping etc. I have the following columns to work with. (There are others but they don't have any purpose for my study) I have Message_ID, Latitude, Longitude, MMSI (This represents individual ship signals, hence they are unique to ships) Ship_type, Vessel_name.

So here's the issue

Essentially what I need to do is append the Ship_type and Vessel_name to the lines with Message_ID's 1 and 3 by way of joining through the MMSI number which is shared by a Message_ID 5.

the queries I have so far.. 

WHERE (latitude > 55 and latitude < 85 and longitude > 50 and longitude < 141) And (Message_ID = 1 or Message_ID = 3);

Other Query 

WHERE Message_ID = 5;

How do I join all Ship_type and Vessel_name that result in the second query to the first query?

I FEEL LIKE IT SHOULD BE MENTIONED THAT EVERYTHING IS WITHIN ONE TABLE ENTITLED dbo.DecodedCSVMEssages_Staging THAT HAS ABOUT 100 MILLION ENTRIES.. :S

Upvotes: 2

Views: 1095

Answers (3)

RBarryYoung
RBarryYoung

Reputation: 56785

I would probably do it like this:

SELECT
     t13.Message_ID, 
     t13.Latitude, 
     t13.Longitude, 
     t13.MMSI,
     t5.Ship_type, 
     t5.Vessel_name
FROM yourTable As t13
OUTER APPLY (   SELECT TOP 1 * 
                FROM  yourTable As t5
                WHERE t5.Message_ID = 5
                  AND t5.MMSI = t13.MMSI
             ) As t5
WHERE t13.Message_ID IN(1,3)
  AND t13.latitude > 55 
  and t13.latitude < 85 
  and t13.longitude > 50 
  and t13.longitude < 141

Upvotes: 2

Sonam
Sonam

Reputation: 3466

with ship_cte(Ship_type,Vessel_name,MMSI)
as(select Distinct Ship_type,Vessel_name,MMSI  from TableName WHERE Message_ID = 5)

select b.Ship_type,b.Vessel_name,a.other_columns 
from tableName a join ship_cte b on a.MMSI=b.MMSI
WHERE (a.latitude > 55 and a.latitude < 85 and a.longitude > 50 and a.longitude < 141) 
And (a.Message_ID = 1 or a.Message_ID = 3);

Here in the first part of the query I'm getting ship_type and vessel_name for all the rows where message_id=5, and then I'm joining this part of the query with main table on the basis of MMSI number.

Upvotes: 0

Dan Bellandi
Dan Bellandi

Reputation: 546

I think you want something like this:

select Message_ID, Latitude, Longitude, MMSI, x.Ship_type, x.Vessel_name
from table t
outer apply (select Ship_type, Vessel_name from table x where x.MMSI=t.MMSI and x.Message_ID=5) x
where t.Message_ID in (1,3) and (latitude > 55 and latitude < 85 and longitude > 50 and longitude < 141);

Upvotes: 0

Related Questions