Join two tables

Question

Suppose these 3 tables:

COURT:

ID -------------------- 1 2 3 

ARRIVE:

  shipID Atime -------------------- 1 t1 1 t3 

VACATION:

  shipID Ltime -------------------- 1 t2 1 t4 

I need a query that returns:

  shipID Atime Ltime ------------------------------ 1 t1 null 1 null t2 1 t3 null 1 null t4 

where t1> t2> t3> t4

This result is acceptable for:

  shipID Atime Ltime ------------------------------ 1 t1 null 1 t3 null 1 null t2 1 null t4 

Answer 1

Try the following:

 SELECT DISTINCT t1.shipid, a.atime, l.ltime FROM ( SELECT shipID, atime AS time from arrive UNION ALL SELECT shipID, ltime AS time from `leave` ) AS t1 LEFT JOIN arrive AS a ON a.shipid = t1.shipid AND a.atime = t1.time LEFT JOIN `leave` AS l ON l.shipid = t1.shipid AND l.ltime = t1.time 

Look here:

• SQL Fiddle Demo

This will give you:

 | SHIPID | ATIME | LTIME | ---------------------------- | 1 | t1 | (null) | | 1 | t3 | (null) | | 1 | (null) | t2 | | 1 | (null) | t4 | 

Answer 2

Try:

 SELECT shipID, atime, null ltime from arrive UNION ALL SELECT shipID, null atime, ltime from `leave` order by coalesce(atime,ltime) 

SQLFiddle here .

Answer 3

basically you want to sort by alternating values, not in order by one whole column, then another ...

 --Dummy Tables CREATE TABLE #ship (ID int) CREATE TABLE #arrive (ID int, atime DateTime) CREATE TABLE #leave (ID int, ltime DateTime) --Dummy Data INSERT INTO #ship (ID) values (1); INSERT INTO #arrive (ID, atime) VALUES (1, '2013-05-29 00:00:00') INSERT INTO #arrive (ID, atime) VALUES (1, '2013-05-29 12:00:00') INSERT INTO #leave (ID, ltime) VALUES (1, '2013-05-29 06:00:00') INSERT INTO #leave (ID, ltime) VALUES (1, '2013-05-29 18:00:00') SELECT i.ID, CASE WHEN i.label = 'l' then i.thetime else null end as atime, CASE WHEN i.label = 'a' then i.thetime else null end as ltime FROM ( SELECT 'l' as label,ID,atime as thetime FROM #arrive UNION SELECT 'a' as label,ID,ltime as thetime FROM #leave ) as i ORDER BY i.thetime --Cleanup DROP TABLE #ship DROP TABLE #arrive DROP TABLE #leave 

Results.

 ID atime ltime ----------- ----------------------- ----------------------- 1 2013-05-29 00:00:00.000 NULL 1 NULL 2013-05-29 06:00:00.000 1 2013-05-29 12:00:00.000 NULL 1 NULL 2013-05-29 18:00:00.000 

Answer 4

Try Query

 select * from (select s.id as shipid,a.Atime as Atime,null as Ltime from Ship s inner join Arrival a on s.id=a.shipid union select s.id as shipid,null as Atime, l.Ltime as Ltime from Ship s left join Leave1 l on s.id=l.shipid)t where t.Atime is not null or t.Ltime is not null 

SQL Fiddle