Combine two SQL Select Statements

Greetings,
I have two sql queries that I need to combine:

(SELECT Article.Date, COUNT(Keywords_Negative.Word) AS Neg FROM Article INNER JOIN Keywords_Negative ON
Article.Description LIKE '%' + Keywords_Negative.Word + '%' WHERE Article.Description LIKE '%Japan%'  
 GROUP BY Date)



(SELECT Article.Date, COUNT(Keywords_Positive.Word) AS Pos FROM Article
INNER JOIN Keywords_Positive ON Article.Description LIKE '%' + Keywords_Positive.Word + '%' WHERE Article.Description LIKE '%Japan%'
GROUP BY Date)

I would like my table to have:

Date            Pos                     Neg
1/1/2012       3                           6
1/2/2012       5                           7

Thanks in advance for the help.
MaxKroyAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
To simplify the number of group by's being done, you could try
SELECT  Date, SUM(Pos), SUM(Neg)
FROM (SELECT Article.Date, 0 as pos, 1 AS Neg 
      FROM Article 
      INNER JOIN Keywords_Negative 
              ON Article.Description LIKE '%' + Keywords_Negative.Word + '%' 
      WHERE Article.Description LIKE '%Japan%'
      UNION ALL
      SELECT Article.Date, 1 AS Pos, 0 as Neg 
      FROM Article 
      INNER JOIN Keywords_Positive 
              ON Article.Description LIKE '%' + Keywords_Positive.Word + '%' 
      WHERE Article.Description LIKE '%Japan%') v
GROUP BY Date

Open in new window

0
 
jchittoda1Commented:
You can use the UNION to join the two sql statements those generate same number & type of column.

SQL1
UNION
SQL2
0
 
lwadwellCommented:
A way to combine would be
SELECT  Date, SUM(Pos), SUM(Neg)
FROM (SELECT Article.Date, null as pos, COUNT(Keywords_Negative.Word) AS Neg 
      FROM Article 
      INNER JOIN Keywords_Negative 
              ON Article.Description LIKE '%' + Keywords_Negative.Word + '%' 
      WHERE Article.Description LIKE '%Japan%'  
      GROUP BY Date
      UNION ALL
      SELECT Article.Date, COUNT(Keywords_Positive.Word) AS Pos, null as Neg 
      FROM Article 
      INNER JOIN Keywords_Positive 
              ON Article.Description LIKE '%' + Keywords_Positive.Word + '%' 
      WHERE Article.Description LIKE '%Japan%' 
      GROUP BY Date) v
GROUP BY Date

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MaxKroyAuthor Commented:
Union doesn't work - just gives me Date and Neg variables.
0
 
jchittoda1Commented:
For more information you can refer
http://www.w3schools.com/sql/sql_union.asp
0
 
MaxKroyAuthor Commented:
UNION WILL NOT WORK FOR THIS.
0
 
MaxKroyAuthor Commented:
Worked perfect Thanks alot!!!!!!!!!!
0
 
lwadwellCommented:
Why do you think UNION will not work?  I deliberately changed the two SQL's to return three columns so that a UNION would work.  Did you try them?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.