Query problem.

[ramu_rp2005]

Hi all,
Can anybody solve my query problem. I use mysql query and it shows the error:

ERROR 1060 (42S21): Duplicate column name 'audio_sid'

i'm not able to figure out why.the query is below.

Select * from (Select *,case when a.audio_sid is NULL then v.video_sid ELSE a.audio_sid END as SID,case when a.audio_prgm_num is NULL then v.video_prgm_num ELSE a.audio_prgm_num END as PRNO from(Select audio_sid,audio_prgm_num,video_sid,video_prgm_num from audio a left join video v on(audio_sid=video_sid and audio_prgm_num=video_prgm_num) union select audio_sid,audio_prgm_num,video_sid,video_prgm_num from video v left join audio a on (audio_sid=video_sid and audio_prgm_num=video_prgm_num))T left join audio a on (T.audio_sid=a.audio_sid and T.audio_prgm_num = a.audio_prgm_num)left join video v on(T.video_sid=v.video_sid and T.video_prgm_num=v.video_prgm_num))TT left join pcr_info p on(p.pcr_sid=TT.SID and TT.PRNO=p.pcr_prgm_num) right join stream s on (TT.SID=s.stream_id and s.stream_name like 't%');

[bogu]

Do u have audio_sid field in more then one table?
I assume is in "audio", but don't u have the same field in one of the other tables (video,pcr_info,stream)?
Maybe if u add your tables structure, you'll get more help ...

Anyway, u should tell to mysql each field from witch table it is. Like in here:
This your SELECT:

Select audio_sid,audio_prgm_num,video_sid,video_prgm_num from audio a 
LEFT JOIN video v on(audio_sid=video_sid and audio_prgm_num=video_prgm_num)

This is how u should do ...

Select a.audio_sid,a.audio_prgm_num,v.video_sid,v.video_prgm_num from audio a 
LEFT JOIN video v on(a.audio_sid=v.video_sid and a.audio_prgm_num=v.video_prgm_num)

[ramu_rp2005]

well. the database struture is below as u had asked.their is no audio_sid field twice in any tables.

Stream Table:

+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| stream_id | varchar(10) | NO | PRI | | |
| stream_name | varchar(99) | YES | | NULL | |
| stream_prgm_count | int(3) | YES | | NULL | |
| stream_location | varchar(200) | YES | | NULL | |
| stream_mux_bitrate | float | YES | | NULL | |
| stream_size | float | YES | | NULL | |
| stream_packet_size | int(3) | YES | | NULL | |
| stream_type | varchar(50) | YES | | NULL | |

Video Table:

+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| video_sid | varchar(10) | NO | PRI | | |
| video_prgm_num | int(11) | NO | PRI | 0 | |
| video_pid | int(11) | NO | PRI | 0 | |
| video_type | varchar(25) | YES | | NULL | |
| video_userdata | tinyint(1) | YES | | NULL | |
| video_resolution | varchar(15) | YES | | NULL | |
| video_chroma_format | varchar(15) | YES | | NULL | |
| video_aspect_ratio | varchar(10) | YES | | NULL | |
| video_frame_rate | float | YES | | NULL | |
| video_bitrate | float | YES | | NULL | |
+---------------------+-------------+------+-----+---------+-------+

Audio Table:

+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| audio_sid | varchar(10) | NO | PRI | | |
| audio_prgm_num | int(11) | NO | PRI | 0 | |
| audio_pid | int(11) | NO | PRI | 0 | |
| audio_type | varchar(25) | YES | | NULL | |
| audio_sr | int(11) | YES | | NULL | |
| audio_mode | varchar(15) | YES | | NULL | |
| audio_bitrate | float | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+

Pcr_info Table:

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| pcr_sid | varchar(10) | NO | PRI | | |
| pcr_prgm_num | int(11) | NO | PRI | 0 | |
| pcr_pid | int(11) | NO | PRI | 0 | |
+--------------+-------------+------+-----+---------+-------+

Stream_id is primary key
Video_sid,audio_sid,pcr_sid are foreign keys referencing stream_id.