Noob to Join statements and need some JOIN Query help

I've been struggling with JOIN queries. Below is one that I am having issues with. If I run the below query I get these results:

SELECT
a.orderdate,
a.orderid,
a.store_num,
a.OE1_01 AS OSAT,
b.openend
FROM
XXXXXX.openends b
LEFT JOIN
XXXXXX.responses a
ON
a.pin=b.PIN
AND
a.orderdate BETWEEN -1 AND -1

Results:
orderDate OrderID store_num OSAT Openend
? ? ? ? Have more than 1 employee working the pick-up counter.
? ? ? ? It was fine. No problem whatsoever.
? ? ? ? Could fee coupon codes or gift card rewards.

If I change it to be a RIGHT JOIN like this, here is what I get:

SELECT
a.orderdate,
a.orderid,
a.store_num,
a.OE1_01 AS OSAT,
b.openend
FROM
XXXXXX.openends b
RIGHT JOIN
XXXXXX.responses a
ON
a.pin=b.PIN
AND
a.orderdate BETWEEN -1 AND -1

Results:
orderDate OrderID store_num OSAT Openend
6/4/2016 4,261,603,297,172 5,056 9 ?
10/22/2016 5,661,665,938,290 4,161 10 ?
2/8/2017 3,101,789,293,275 1,009 4 ?

I am needing to get both sets of data together. Any help would be appreciated!

FULL OUTER JOIN?

Unfortunately INNER and OUTER joins also aren't working. The FULL OUTER Join also returned this:

orderDate OrderID store_num OSAT Openend
6/4/2016 4,261,603,297,172 5,056 9 ?
10/22/2016 5,661,665,938,290 4,161 10 ?
2/8/2017 3,101,789,293,275 1,009 4 ?

I wanted to see if anyone had any feedback?

Please provide:

  • table descriptions in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide
1 Like