CODEWITHSUNDEEP
sql
Ramona Daniela
Ramona Daniela

Reputation: 69

Join tables with different times

I am very(very) new to programming and I want to join two tables. If I am doing the queries separately it works but I am having problem when joining them. Here's what I did so far:

SELECT DISTINCT(MCH_CODE) as Machine, COUNT(SELL) AS Sells
FROM SELLS
WHERE STATUS = 'SOLD' AND SUBCODE = 'T'  
AND TIME >='2017-09-14 07:00:00' and STORE_CODE ='A101'
GROUP BY MCH_CODE

The second one:

SELECT DISTINCT(MCH_CODE) as Machine,  COUNT(BARCODES) AS Scanned
FROM SCANNED_TABLE
WHERE TIME_STAMP >= '2017-09-14 07:00:00' AND STORE_CODE ='A101' 
GROUP BY MCH_CODE

And this is what I got so far:

SELECT DISTINCT (dc.MCH_CODE) as Machine, COUNT(dc.SELL) AS Sells, COUNT(BARCODES) AS Scanned
FROM SELLS AS dc
INNER JOIN SCANNED_TABLE AS tr
ON dc.STORE_CODE = tr.STORE_CODE
WHERE dc.STATUS = 'SOLD' AND dc.SUBCODE = 'T'  
AND dc.TIME >='2017-09-14 07:00:00' and dc.STORE_CODE ='A101'
GROUP BY dc.MCH_CODE

I should get something like Machine Sells Scanned but when I join the tables the values are not the same. Is it because the times? How can properly join the tables?

Upvotes: 0

Views: 57

Answers (2)

ssn
ssn

Reputation: 509

Try joining the two statements together, rather than creating a combined join.

Try this:

SELECT
    *
FROM 
(
SELECT
    MCH_CODE AS Machine
    ,STORE_CODE
    ,COUNT(SELL) AS Sells
FROM
    SELLS
WHERE 
    STATUS = 'SOLD'
    AND SUBCODE = 'T'
    AND TIME >= '2017-09-14 07:00:00'
    AND STORE_CODE = 'A101'
GROUP BY
    MCH_CODE
) AS A
INNER JOIN (
SELECT
    MCH_CODE AS Machine
    ,STORE_CODE
    ,COUNT(BARCODES) AS Scanned
FROM
    SCANNED_TABLE
WHERE 
    TIME_STAMP >= '2017-09-14 07:00:00'
    AND STORE_CODE = 'A101'
GROUP BY
    MCH_CODE
) AS B ON A.STORE_CODE = B.STORE_CODE AND A.Machine = B.Machine

Upvotes: 0

StanislavL
StanislavL

Reputation: 57411

You already have all you need and can join them as subqueries

SELECT t1.Machine, t1.Sells, t2.Scanned
FROM
    (SELECT MCH_CODE as Machine, COUNT(SELL) AS Sells
     FROM SELLS
     WHERE STATUS = 'SOLD' AND SUBCODE = 'T'  
     AND TIME >='2017-09-14 07:00:00' and STORE_CODE ='A101'
     GROUP BY MCH_CODE) t1 
    INNER JOIN
    (SELECT MCH_CODE as Machine,  COUNT(BARCODES) AS Scanned
     FROM SCANNED_TABLE
     WHERE TIME_STAMP >= '2017-09-14 07:00:00' AND STORE_CODE ='A101' 
     GROUP BY MCH_CODE) t2 ON t1.Machine=t2.Machine

Upvotes: 1

Related Questions