CODEWITHSUNDEEP
mysqlsql
lapadets
lapadets

Reputation: 1095

Combine two separate SQL queries into one

I am having some difficulties with probably a not so difficult SQL task..

I have two queries that I'd like to combine together. Both of them work fine separately, but when I try to combine them together I get different errors such as:

Error 116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

At the same time I do not want to use EXIST, but IN instead.

Here are the queries:

First:

  SELECT C.Id, C.Name, BC.Id AS BCID
    FROM Customers AS C
    RIGHT JOIN Bills AS Bc ON C.Id = BC.Bills_Customer
    RIGHT JOIN Months AS M ON Bc.Month_Bills = M.Id
    WHERE C.Argument = 'KP'
    AND YEAR(bm.Datum) = YEAR(CURRENT_TIMESTAMP) AND MONTH(bm.Datum) = MONTH(CURRENT_TIMESTAMP)
    ORDER BY C.Name

Second:

  SELECT DISTINCT Account, LastLogin, Licences_Bills 
    FROM Licences 
    WHERE LastLogin > CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)
    AND LastLogin < CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)
    AND Access = 1 --AND Licences_Bills IN
    ORDER BY Licences_Bills ASC

Results from the two queries look like that:

First:

 +----------+---------+-----------+
 |  Id      | Name    |   BCID    |  
 +----------+---------+-----------+
 |    1     |  John   |    500    |  
 +----------+---------+-----------+
 |    2     |  Max    |    501    |
 +----------+---------+-----------+
 |    5     |  Foo    |    502    |  
 +----------+---------+-----------+
 |    7     |  Bar    |    503    |
 +----------+---------+-----------+

Second:

 +----------+--------------+-------------------+
 |  Account |   LastLogin  |  Licences_Bills   |  
 +----------+--------------+-------------------+
 |    abc   |  07.03.2014  |    500            |  
 +----------+--------------+-------------------+
 |    aac   |  13.03.2014  |    500            |
 +----------+--------------+-------------------+
 |    acb   |  28.03.2014  |    504            |  
 +----------+--------------+-------------------+
 |    bca   |  19.03.2014  |    506            |
 +----------+--------------+-------------------+

Now I'd like to combine these two, so that it displays only rows where the BCID from the first query and the Licences_Bills from the second query match.

I tried with IN and then a nested select, but did not work out. Any thoughts on what I am doing wrong? Clues and/or links to some valuable resources are also appreciated!

EDIT: In the end, I'd like to have the second result list limited to those bills from the first query. This is why I was trying with IN and then nested select. INNER JOIN on the other hand would give me results as the intersection of both tables.

Also, I don't want to see the columns from the first query (as of what happens with join). Just the final result structured as the second query.

Upvotes: 0

Views: 116

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

To get the second result list limited to bills you also find in the first query, you will have to Combine them either with IN or with EXISTS. I don't know why this didn't work for you. Maybe just a typo? The following should work. It simply combines the two statements with IN. So in the inner query I only select BC.ID and removed the order by clause.

SELECT DISTINCT Account, LastLogin, Licences_Bills 
FROM Licences 
WHERE LastLogin > CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)
AND LastLogin < CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)
AND Access = 1 
AND Licences_Bills IN
(
  SELECT BC.Id
  FROM Customers AS C
  RIGHT JOIN Bills AS Bc ON C.Id = BC.Bills_Customer
  RIGHT JOIN Months AS M ON Bc.Month_Bills = M.Id
  WHERE C.Argument = 'KP'
  AND YEAR(bm.Datum) = YEAR(CURRENT_TIMESTAMP) AND MONTH(bm.Datum) = MONTH(CURRENT_TIMESTAMP)
)
ORDER BY Licences_Bills ASC

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

SELECT * FROM

(

SELECT C.Id, C.Name, BC.Id AS BCID
    FROM Customers AS C
    RIGHT JOIN Bills AS Bc ON C.Id = BC.Bills_Customer
    RIGHT JOIN Months AS M ON Bc.Month_Bills = M.Id
    WHERE C.Argument = 'KP'
    AND YEAR(bm.Datum) = YEAR(CURRENT_TIMESTAMP) AND MONTH(bm.Datum) = MONTH(CURRENT_TIMESTAMP)


) as T1
INNER JOIN

(
SELECT DISTINCT Account, LastLogin, Licences_Bills 
    FROM Licences 
    WHERE LastLogin > CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)
    AND LastLogin < CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)
    AND Access = 1

) T2

ON T1.BCID=T2.Licences_Bills

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Try with join

SELECT t1.*,t2.*
FROM
(query 1) t1
JOIN (query 2) t2 
ON(t1.BCID = t2.Licences_Bills)

Upvotes: 1

Related Questions