a and b r not tables but views. i am using multiple views .
The sql for the views are as follows:
CREATE view cch_dubber_dublist_view_a1 as select distinct
a.playlist,a.material_id,a.destination_locator,a.destination,a.air_time,a.traffic_duration,a.comment,a.title,a.device_name,m.media_locator
as source_locator,m.medium_name as source from cch_dubber_dublist_view11 a
left outer join lib_copy_view m on a.material_id=m.material_id
create view cch_dubber_dublist_view_b1 as select
a.playlist,a.material_id,a.destination_locator,a.destination,a.air_time,a.traffic_duration,a.comment,a.title,a.device_name
from cch_dubber_dublist_view11 a,dev_device_view d inner join lib_copy_view
c on d.device_name= c.medium_name where c.material_id = a.material_id AND
c.media_locator = a.destination_locator AND d.device_name = a.device_name
CREATE view cch_dubber_dublist_view11 as
select distinct a.playlist as playlist,
a.material_id as material_id,
a.media_locator as destination_locator,
a.video_source as destination,
a.traffic_duration as traffic_duration,
a.comment as comment,
a.title as title,
b.device_name,a.traffic_start_time as air_time
from dev_device_view b inner join cch_playlist_view a on b.source_name
=a.video_source
where (A.material_id is not null) and (A.material_id <>'')
and (a.TRAFFIC_START_TIME IN(SELECT min(TRAFFIC_START_TIME) FROM
CCH_PLAYLIST_TBL
WHERE CCH_PLAYLIST_TBL.MATERIAL_ID =a.MATERIAL_ID))
union
select distinct a.playlist as playlist,
a.material_id as material_id,
a.media_locator as destination_locator,
a.backup_video_source as destination,
a.traffic_duration as traffic_duration,
a.comment as comment,
a.title as title,
b.device_name,a.traffic_start_time as air_time
from dev_device_view b inner join cch_playlist_view a on
b.source_name=a.backup_video_source
where (A.material_id is not null) and (A.material_id <>'')
and (a.TRAFFIC_START_TIME IN(SELECT min(TRAFFIC_START_TIME) FROM
CCH_PLAYLIST_TBL
WHERE CCH_PLAYLIST_TBL.MATERIAL_ID =a.MATERIAL_ID))
_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
