sqlite create view from multiple tables

Question

There are 3 Tables:

Table A
aNo | aName | aCity
A1  | Alex  | Ohio
A2  | Nick  | LA
A3  | Sam   | Seatle
A4  | Rick  | Sydney
.
.
.

Table B
bNo | aNo | bType
B1  | A1  | big
B2  | A1  | small
B3  | A1  | small
B1  | A4  | Medium
B2  | A4  | tiny
B3  | A4  | Big

.
.


Table C
aNo | cDate | bNo
A1  | 2011  | B2
A2  | 2006  | B2
A3  | 1993  | B1
A4  | 2018  | B3
A4  | 2013  | B3
A4  | 2002  | B3
.
.

I need to create a view that shows me aName, bType and SUM(TableB.aNo[A1,A4 only])

It should show me something like:

aName | bType | A1 and A4 quantity
Alex  | Big   | 1
Alex  | Medium| Null
Alex  | small | 2
Alex  | tiny  | Null
Rick  | Big   | 1
Rick  | Medium| 1
Rick  | small | Null
Rick  | Tiny  | 1

I tried this:

CREATE VIEW v_A1A4 
AS 
SELECT
    A.aName,
    B.bType,
    sum(C.aNo) AS Rooms_Total   
FROM
    A
INNER JOIN Room ON B.aNo = A.aNo
INNER JOIN C ON C.aNo = A.aNo
  WHERE C.aNo = 'A1' AND C.aNo = 'A4';

SELECT * FROM v_A1A4;

But this shows the new VIEW table. but all values are NULL.

aName | bType | A1 and A4 quantity
NULL  | NULL  | NULL

I need to show the quantity of values A1 and A2.

Waht should I do?

b.Ano is not a number so it is unclear what sum() is supposed to be doing. — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2020 at 19:49
In your sample data Table C contains 3 rows for A4, Rick with bNo = B3. This B3 links to Table B to the row: B3, A4, Big only. Why do you want in the results: Rick | Big | 1, Rick | Medium| 1, Rick | Tiny | 1? — forpas
– forpas, Commented Aug 30, 2020 at 9:00
Hi @GordonLinoff, thank you for noticing. That is a mistake, it should be count() as you later showed in the solution. — EddSoul24
– EddSoul24, Commented Aug 31, 2020 at 10:38
Hi @forpas, this could clarify a little bit the role of each table: Table C is needed because it determines how many times the As were used. E.g. Table C is like booking table for using As which are Zoos from Table B. A1 has cages like B1, B2 and B3. These cages are the type Big, Medium, Small..... So I need to count how many times A1 and A2 are used in Table C. — EddSoul24
– EddSoul24, Commented Aug 31, 2020 at 10:40
Fine, but the numbers in your expected results come all from Table B only. So what is the use of Table C in the results? — forpas
– forpas, Commented Aug 31, 2020 at 10:43

Gordon Linoff · Accepted Answer · 2020-08-29 19:51:10Z

1

If I understand correctly, you just want to fill in the missing values between tables a and b. I'm not sure what table c is supposed to be doing, but it doesn't seem to be needed.

select a.ano, t.type, count(b.ano)
from a cross join
     (select distinct type from b) t left join
     b
     on a.ano = b.ano and b.type = t.type
group by a.ano, t.type;

answered Aug 29, 2020 at 19:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EddSoul24 Over a year ago

Table C is needed because it determines how many times the As were used. E.g. Table C is like booking table for using As which are Zoos from Table B. A1 has cages like B1, B2 and B3. These cages are the type Big, Medium, Small..... So I need to count how many times A1 and A2 are used in Table C.

Collectives™ on Stack Overflow

sqlite create view from multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related