?
Solved

Combine two SQL Select Statements

Posted on 2012-09-10
8
Medium Priority
?
670 Views
Last Modified: 2012-09-10
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.
0
Comment
Question by:MaxKroy
  • 3
  • 3
  • 2
8 Comments
 
LVL 1

Expert Comment

by:jchittoda1
ID: 38385452
You can use the UNION to join the two sql statements those generate same number & type of column.

SQL1
UNION
SQL2
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38385458
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
 

Author Comment

by:MaxKroy
ID: 38385459
Union doesn't work - just gives me Date and Neg variables.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 1

Expert Comment

by:jchittoda1
ID: 38385467
For more information you can refer
http://www.w3schools.com/sql/sql_union.asp
0
 

Author Comment

by:MaxKroy
ID: 38385468
UNION WILL NOT WORK FOR THIS.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38385470
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
 

Author Closing Comment

by:MaxKroy
ID: 38385481
Worked perfect Thanks alot!!!!!!!!!!
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38385487
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question