I'm having a hard time with creating a Sql that pull data from three tables.
Here are part of the structure of each table:
Customer table:
accnum integer
name string
address string
Routeno string
delno integer
Invoice table:
accnum integer
invnum string
deldate date
amount float
routeno string
vstatus string
Card table:
accnum integer
expdate string
The result I need is all the customer that is on routeno 1, 2 and 3 then I need any invoices that have the delivery date (deldate) and the expdate of any that have a credit card on file. Sorted by delno.
Select c.accnum, c.name, c.address, c.routeno, c.delno from customer c
where c.routeno in ('1','2','3')
From this result I need the following.
Select i.invnum, i amount from invoice i
where i.deldate = '2020-05-27' and (vstatus <> 'V' or vstatus is null)
and i.accnum=c.accnum
and i.routeno in ('1','2','3')
Select e.expdate from Card
where e.accnum=c.accnum
I tried using Join, but then I only get the customer that have invoices I nned them all.
Select c.accnum, c.name, c.address, c.routeno, i.invnum, i.amount, e.expdate from Customer c
left Join Card e on c.accnum=e.accnum
left Join Invoice i on c.accnum=i.accnum
where i.deldate = '2020-05-27' and (vstatus <> 'V' or vstatus is null)
and i.accnum=c.accnum
and i.routeno in ('1','2','3')
order by c.routeno, c.delno
I need a result like this:
accnum name address routeno delno invnum amount expdate
000030 Smith 1 main st 1 1 A123 5.00 12/22
000030 Smith 1 main st 1 1 A125 8.00 12/22
000022 Knox 14 main st 1 2 A124 10.00
000014 Vohs 20 main st 1 3 A119 4.00 11/21
000078 Bow 3 Camp Ave 1 4 A120 3.00
000015 Jordan 4 River rd 2 1 A118 11.00 10/23
000015 Jordan 4 River rd 2 1 A117 15.00 10/23
Thanks for any help. KHJ
The link philipxy provided should point you the right direction. Another way approach this is to create an outer joined subquery with the desired filter criteria on invoice.
SELECT c.accnum,
c.name,
c.address,
c.routeno,
i.invnum,
i.amount,
e.expdate
FROM Customer c
LEFT JOIN Card e ON c.accnum = e.accnum
LEFT JOIN
(SELECT accnum, invnum, amount
FROM Invoice
WHERE deldate = '2020-05-27'
AND (vstatus <> 'V' OR vstatus IS NULL)
AND routeno IN ('1', '2', '3')) i
ON i.accnum = c.accnum
ORDER BY c.routeno, c.delno