combine two separate queries

Question

combine two separate queries

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323741 More actions · Answer 1

it looks like some inline strings/code that is being constructed in c#, which is then being executed, presumbly by LoadDatatable?

i'm not sure where =\'" + dtg + "\' is coming from, but combining the two queries i think would look something like this:

DECLARE @dtg varchar(10) --?=\'" + dtg + "\' ? what datatype?

select

ships.name,

ais_data.mmsi,

ships.country,

ais_data.course,

ais_data.sog,

ais_data.heading,

ais_data.latitude,

ais_data.longitude

from ships

LEFT JOIN ais_data

ON ships.mmsi=ais_data.mmsi

INNER JOIN (select

ais_data.mmsi,

MAX(ais_data.mssisdtg1)

from ais_data

where ais_data.dtg=@dtg

and ais_data.message BETWEEN 1 AND 3

group by ais_data.mmsi

)MyAlias

ON ais_data.mmsi=MyAlias.mmsi

and ais_data.mssisdtg1=MyAlias.mssisdtg1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tahirg2000 SSC Rookie Points: 38 More actions · Answer 2

Sir, Thanks for your advice!!!

Basically dtg is coming from a web form. I hard coded the dtg here. I applied the query, but it is giving me some error. I am not very expert in databases thats why I am asking for help. I will act upon your advice and one day I will also be able to help others.:-)

select

ships.name,

ais_data.mmsi,

ships.country,

ais_data.course,

ais_data.sog,

ais_data.heading,

ais_data.latitude,

ais_data.longitude

from ships LEFT JOIN ais_data ON ships.mmsi=ais_data.mmsi

INNER JOIN (select ais_data.mmsi,MAX(ais_data.mssisdtg1) from ais_data where ais_data.dtg='2015-02-27'

and ais_data.message BETWEEN 1 AND 3 group by ais_data.mmsi) MyAlias

ON ais_data.mmsi=MyAlias.mmsi

and ais_data.mssisdtg1=MyAlias.mssisdtg1

I think last two lines are giving following errors:

Msg 8155, Level 16, State 2, Line 1

No column was specified for column 2 of 'MyAlias'.

Msg 207, Level 16, State 1, Line 14

Invalid column name 'mssisdtg1'.

Lowell SSC Guru Points: 323741 More actions · Answer 3

i forgot the alias for the max:

MAX(ais_data.mssisdtg1) AS mssisdtg1

select

ships.name,

ais_data.mmsi,

ships.country,

ais_data.course,

ais_data.sog,

ais_data.heading,

ais_data.latitude,

ais_data.longitude

from ships LEFT JOIN ais_data ON ships.mmsi=ais_data.mmsi

INNER JOIN (select ais_data.mmsi,MAX(ais_data.mssisdtg1) AS mssisdtg1 from ais_data where ais_data.dtg='2015-02-27'

and ais_data.message BETWEEN 1 AND 3 group by ais_data.mmsi) MyAlias

ON ais_data.mmsi=MyAlias.mmsi

and ais_data.mssisdtg1=MyAlias.mssisdtg1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!