query help - 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)

query help

Author

Topic

Ambikaa
Starting Member

43 Posts

Posted - 2009-03-06 : 03:56:16

Hi,

In my below query, how to check like my condition?

m_videos (table)
Video_netid=20
video_netid=82 both has the record in m_videos
but, m_tv table has the record only for video_netid=20, the m_tv fields are tvid, videoid,... this videoid FK to Video_id PK in m_videos. In m_tv, it has record only for video_netid=20 and not for 82.

I need to collect only the record that has video_netid=20 , video_netid=82 and channelid=6 network id is (video_netid=20) from m_tv table

select
Video_id
from m_Videos v
LEFT JOIN m_Networks n ON v.video_Netid=n.net_id
LEFT JOIN m_Videogroups vg ON v.video_GroupId=vg.vgrp_id
LEFT JOIN m_tv tv ON tv.videoid=v.video_id
left join m_channels c on v.video_channelid=c.chan_id
WHERE c.chan_Id=16 and v.video_netid=82 AND v.Video_Approved=1 AND v.video_Status=1 and v.Video_Deleted=0
AND n.Net_Status=1 AND n.Net_Deleted=0
AND c.Chan_Status=1 AND c.Chan_Deleted=0
AND vg.Vgrp_Status=1 AND vg.Vgrp_Deleted=0
AND tv.eventdate=convert(char(10),getdate(), 110)
order by convert(char(8), tv.BeginTime, 108) asc

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-06 : 04:36:41

select
Video_id
from m_Videos v
LEFT JOIN m_Networks n ON v.video_Netid=n.net_id AND n.Net_Status=1 AND n.Net_Deleted=0
LEFT JOIN m_Videogroups vg ON v.video_GroupId=vg.vgrp_id AND vg.Vgrp_Status=1 AND vg.Vgrp_Deleted=0
LEFT JOIN m_tv tv ON tv.videoid=v.video_id AND tv.eventdate=convert(char(10),getdate(), 110)
LEFT JOIN m_channels c on v.video_channelid=c.chan_id AND c.chan_Id=16 AND c.Chan_Status=1 AND c.Chan_Deleted=0
WHERE v.video_netid = 82
AND v.Video_Approved=1
AND v.video_Status=1
AND v.Video_Deleted=0
order by convert(char(8), tv.BeginTime, 108) asc

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 05:06:31

[code]
when u use left join condition should be in (on condition only)
SELECT
Video_id
FROM
m_Videos v
LEFT JOIN
m_Networks n ON v.video_Netid=n.net_id AND n.Net_Status=1 AND n.Net_Deleted=0
LEFT JOIN
m_Videogroups vg ON v.video_GroupId=vg.vgrp_id AND vg.Vgrp_Status=1 AND vg.Vgrp_Deleted=0
LEFT JOIN
m_tv tv ON tv.videoid=v.video_id AND DATEADD(d,DATEDIFF(d,0,tv.eventdate),0)=DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
LEFT JOIN
m_channels c ON v.video_channelid=c.chan_id AND c.chan_Id=16 AND c.Chan_Status=1 AND c.Chan_Deleted=0
WHERE
v.video_netid = 82
AND v.Video_Approved=1
AND v.video_Status=1
AND v.Video_Deleted=0
ORDER BY
CONVERT(CHAR(8), tv.BeginTime, 108) ASC
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources