SQLTeam.com | Weblogs | Forums

Noob to Join statements and need some JOIN Query help


#1

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!


#2

FULL OUTER JOIN?


#3

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 ?


#4

I wanted to see if anyone had any feedback?


#5

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