retrive all records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

retrive all records

Author

Topic

raghulvarma
Starting Member

21 Posts

Posted - 2009-02-06 : 03:06:19

got two queries which retrives the data.
the first query retrives the first (current) movement of the transport
the second query retrives the other details that is the previous movement.
Now I need to select all the details of that particular vessel movement which includes all the details of that particular vessel how should I do that?

Query 1.


SELECT a.cntnr_mvmnt_id,a.cntnr_no_cno, (case when (in_dt is null and out_dt is null) then cntnr_mvmnt_dt when in_dt is null then out_dt else in_dt end) cntnr_mvmnt_dt,(SELECT mvmnt_typ_nam FROM movement_type WHERE  mvmnt_typ_id = a.mvmnt_typ_id) Activity ,CONVERT (VARCHAR, (CASE  WHEN(a.typ_of_strg_id=2)THEN (SELECT prt_icd_cd FROM    port_icd WHERE   prt_icd_id = a.plc_of_strg_id)WHEN (a.typ_of_strg_id=4)THEN (SELECT vssl_cd	FROM vessel	WHERE   vssl_id = a.plc_of_strg_id)ELSE(SELECT trdng_prtnr_tcd FROM    Trading_Partner WHERE trdng_prtnr_bin = a.plc_of_strg_id )	  END)) Location ,a.rfrnc_id FROM  container_movement a WHERE   a.cntnr_no_cno  ='ttnu1234450' AND cntnr_mvmnt_id >=(SELECT MAX(cntnr_mvmnt_id)FROM container_movement WHERE   cntnr_no_cno = 'ttnu1234450' AND mvmnt_typ_id in(1,12))

query 2.


select a.cntnr_mvmnt_id, a.cntnr_no_cno, (case when (in_dt is null and out_dt is null) then cntnr_mvmnt_dt when in_dt is null then out_dt else in_dt end) cntnr_mvmnt_dt,(SELECT mvmnt_typ_nam FROM movement_type WHERE  mvmnt_typ_id = a.mvmnt_typ_id) Activity ,CONVERT (VARCHAR, (CASE  WHEN(a.typ_of_strg_id=2)THEN (SELECT prt_icd_cd FROM    port_icd WHERE   prt_icd_id = a.plc_of_strg_id)WHEN (a.typ_of_strg_id=4)THEN (SELECT vssl_cd	FROM vessel	WHERE   vssl_id = a.plc_of_strg_id)ELSE(SELECT trdng_prtnr_tcd FROM    Trading_Partner WHERE trdng_prtnr_bin = a.plc_of_strg_id )	  END)) Location ,a.rfrnc_id from container_movement a where cntnr_mvmnt_id>=(select top 1 cntnr_mvmnt_id from container_movement c where c.cntnr_no_cno = 'ttnu1234450' and c.mvmnt_typ_id in (1,12) AND c.cntnr_mvmnt_id<      (SELECT MAX(cntnr_mvmnt_id) FROM   container_movement d1 WHERE  d1.cntnr_no_cno  = 'ttnu1234450'  AND d1.mvmnt_typ_id IN (6)) order by out_dt desc) and a.cntnr_no_cno =  'ttnu1234450' and a.cntnr_mvmnt_id<=(SELECT MAX(cntnr_mvmnt_id)FROM container_movement d WHERE   d.cntnr_no_cno = 'ttnu1234450' AND d.mvmnt_typ_id in (6))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 03:15:15

The easiest way is to UNION ALL them.

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 05:19:24

to avoid the duplicates use union than union all

raghulvarma
Starting Member

21 Posts

Posted - 2009-02-06 : 05:33:58

quote:
Originally posted by bklr

to avoid the duplicates use union than union all

when I use the union operator I get the values continuously ie row1 from table1 followed by row1 from table2,then row2 from table2 followed by row2 of table 2.I do not want like that, I want the records fully from table1 followed by table 2.
How to get that?

raghulvarma
Starting Member

21 Posts

Posted - 2009-02-06 : 05:34:33

quote:
Originally posted by Peso

The easiest way is to UNION ALL them.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 06:00:25

[code]select {all columns except "theOrder"}
FROM (

-- query 1
SELECT 0 AS theOrder, a.cntnr_mvmnt_id, ...
union all

-- query 2
select 1 AS theOrder, a.cntnr_mvmnt_id, ...
) AS d
order by theOrder[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources