CODEWITHSUNDEEP
sql
teocka
teocka

Reputation: 137

Combine two tables

Supose these 3 tables:

SHIP : 

    ID                   
--------------------

    1                           
    2                            
    3

ARRIVE:

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

LEAVE:

 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 to :

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

Upvotes: 4

Views: 151

Answers (4)

Eoin Campbell
Eoin Campbell

Reputation: 44308

basically you want to order by interleaved values as opposed to ordering by one whole column, then the other... 

--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 in.

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

Upvotes: 1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

Try this 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

Upvotes: 0

user359040
user359040

Reputation:

Try:

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

SQLFiddle here.

Upvotes: 3

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Try this:

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

See it in action here:

This will give you:

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

Upvotes: 1

Related Questions