0

I'm total newbie and attempting to extract customer data from 3 tables (Logs, Offers and Enterprise). I'm trying to determine which customers are enterprise clients and which are not by joining Logs table to Enterprise Table. Those that don't have any info in the enterprise table are considered non-Enterprise. There is also add'l info that I'd like to capture from the Offers table.

However, when I join the three tables I only receive Log and Offer information from Enterprise Candidates. The ideal output would capture data from all customers and have the enterprise info to determine enterprise form non-enterprise. I know this is because I'm doing it wrong but would greatly appreciate any help. Should this be some type of inner join to have a more inclusive output? Example format below:

SELECT
logs.id
,logs.customer_id
,logs.fees
,logs.city
,offers.actual_fees
,enterprise.id

FROM
all_logs logs
join 
enterprise_table_ids enterprise
on
enterprise.enterprise_owner_id = logs_customer_id 
JOIN
offers_table offers
on
offers.id = logs_transcation_id 
AND
logs.logs_transcation_id  > 'October 1, 2018'
AND 
offer.action = 'COMPLETED'
Patrick Winter
  • 1
  • 1
  • 2
    Simply use `LEFT JOIN` on the other two tables where unmatched rows will be render as NULL in those columns. `JOIN` without a type is considered an `INNER JOIN` and will only return rows matched in both sets. – Parfait Oct 05 '18 at 16:25
  • Please read & act on [mcve]. Show that your program calculates what you expect it to as it goes through (sub)expressions--including that arguments passed to each function/operator meet its requirements--by saying what that is & showing that it actually does it via incremental output. Part of justifying a MCVE is finding a working example for maximal partial functionality. Ask about the (small) difference between the examples. After you pin down the first part you don't expect it will be a duplicate question. But please don't just dump code that isn't a minimal extension of code that works. – philipxy Oct 06 '18 at 07:16
  • Please tag with your DBMS. Rows without a match in another table--a faq you seem to not understand--has left join, not in, not exists & except/minus idioms. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Oct 06 '18 at 07:21
  • 2
    Possible duplicate of [How to select all records from one table that do not exist in another table?](https://stackoverflow.com/questions/2686254/how-to-select-all-records-from-one-table-that-do-not-exist-in-another-table) – philipxy Oct 06 '18 at 07:25

0 Answers0