How to use a result from a SQL select to get records in another table?

Question

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

Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, May 27 '21 at 19:07
This is a faq. Before considering posting please read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. Please in code questions give a [mre]. — philipxy, May 27 '21 at 19:08
This post is a duplicate & the link tells you about a misconception you have in the code you give & why you are missing nulls. It doesn't say precisely how to change your code, but you don't clearly say what you want your query to return. (Including what you are trying to say in your prose & code for "From this result I need the following".) When you get results you don't expect, chop code to the first subexpression that returns what you don't expect & give a [mre] including what you expected & why, with justification referencing authoritative documentation. Good luck. — philipxy, May 28 '21 at 01:25

score 2 · Accepted Answer · answered May 27 '21 at 19:21

2

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

answered May 27 '21 at 19:21

Ashley Harms

36
1
1

Thanks for trying to help, but I already tried it and I get an SQL error with Column not found on i.invnum and i.amount and if I remove it then I get all customer since there is no where for only get the customer that belong to routeno 1,2,3 – Kim HJ May 27 '21 at 20:44
Sorry, my fault I didn't see that you had the same columns in Join Select statement, it works thanks. That what I was missing all the time that what happens when you look at the same problem for eight hours. Thanks again. – Kim HJ May 27 '21 at 20:53
I Found that Left Join Card have to be at the end before order by, if not I only get customer that have card on file. – Kim HJ May 27 '21 at 22:44

How to use a result from a SQL select to get records in another table?

1 Answers1