SQL command problem RSS

• • Reply
  

  mahdijalilva...

  Member

  66 Points

  100 Posts

  SQL command problem

  Feb 23, 2012 08:08 AM|LINK

  Is there any problem with this command?

  It shows nothing!

  SELECT Subjects.subject_name AS subject, Subjects.s_date AS date, COUNT(Texts.s_id) AS count
  FROM Subjects INNER JOIN
  Texts ON Subjects.s_id = Texts.s_id
  GROUP BY Subjects.subject_name, Subjects.s_date, Texts.s_id

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: SQL command problem

  Feb 23, 2012 08:12 AM|LINK

  SELECT Subjects.subject_name AS subject, Subjects.s_date AS date,
  COUNT(Texts.s_id) AS count
  FROM Subjects
  INNER JOIN Texts ON Subjects.s_id = Texts.s_id
  GROUP BY Subjects.subject_name, Subjects.s_date
  --, Texts.s_id --remove this

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  mahdijalilva...

  Member

  66 Points

  100 Posts

  Re: SQL command problem

  Feb 23, 2012 08:16 AM|LINK

  No difference.

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: SQL command problem

  Feb 23, 2012 08:26 AM|LINK

  mahdijalilvand

  No difference.

  Than use Left join instead of inner join..

  SELECT Subjects.subject_name AS subject, Subjects.s_date AS date,
  ISNULL(COUNT(Texts.s_id),0) AS count
  FROM Subjects
  LEFT JOIN Texts ON Subjects.s_id = Texts.s_id
  GROUP BY Subjects.subject_name, Subjects.s_date

  Yogesh Bhadauriya
  .Net All About
  
  • Marked as answer by mahdijalilvand on Feb 23, 2012 08:44 AM
• • Reply
  

  arifcse12

  Contributor

  4600 Points

  755 Posts

  Re: SQL command problem

  Feb 23, 2012 08:30 AM|LINK

  Try this,

  select s.id, s.name, COUNT(t.subject_id) as count
  from subjects s
  left join Texts t on t.subject_id = s.id
  group by s.id, s.name order by s.id

  Regards

  MD. Anamul Haque Arif
  
  Please do not forget to click 'Mark as Answer' if this answer helps.
  
• • Reply
  

  vendan

  Participant

  856 Points

  293 Posts

  Re: SQL command problem

  Feb 23, 2012 08:48 AM|LINK

  Hi,

  Try this, It should be working

  SELECT  S.subject_name AS [Name],
          S.s_date AS [Date],
          (SELECT COUNT(T.s_id) FROM Texts T WHERE T.s_id=S.s_id) AS [Count]
  FROM    Subjects S

  Thanks,
  Kalaivendan
  
  Please Mark as Answer if this post helps you!