fullstack_webdev

Code and notes from Full stack web developer path, LinkedIn Learning.

View on GitHub

4. Relationships

04_01 Understanding JOIN

-- join example tables, left and right
CREATE TABLE left(id INTEGER, description TEXT);
CREATE TABLE right(id INTEGER, description TEXT);


INSERT INTO left VALUES ( 1, 'left 01' );
INSERT INTO left VALUES ( 2, 'left 02' );
INSERT INTO left VALUES ( 3, 'left 03' );
INSERT INTO left VALUES ( 4, 'left 04' );
INSERT INTO left VALUES ( 5, 'left 05' );
INSERT INTO left VALUES ( 6, 'left 06' );
INSERT INTO left VALUES ( 7, 'left 07' );
INSERT INTO left VALUES ( 8, 'left 08' );
INSERT INTO left VALUES ( 9, 'left 09' );

INSERT INTO right VALUES ( 6, 'right 06' );
INSERT INTO right VALUES ( 7, 'right 07' );
INSERT INTO right VALUES ( 8, 'right 08' );
INSERT INTO right VALUES ( 9, 'right 09' );
INSERT INTO right VALUES ( 10, 'right 10' );
INSERT INTO right VALUES ( 11, 'right 11' );
INSERT INTO right VALUES ( 11, 'right 12' );
INSERT INTO right VALUES ( 11, 'right 13' );
INSERT INTO right VALUES ( 11, 'right 14' );

SELECT * FROM left;
SELECT * FROM right;

-- Create a joint query, to join the left and right tables where the id's match, ON clause tells the condition under which the two tables should be joined.
--INNER JOIN returns only the intersection of two, i.e. columns where ID's match
SELECT l.description AS ld, r.description AS rd
    FROM left AS l
    JOIN right as r
    ON l.id = r.id;

--LEFT OUTER JOIN returns the intersection of two, i.e. columns where ID's match l.id = r.id as well as left table contents
SELECT l.description AS ld, r.description AS rd
    FROM left AS l
    LEFT JOIN right as r
    ON l.id = r.id;

--JOIN sale table with item table Below query gives us data from sale and item table.

SELECT s.id AS sale,s.date, i.name,i.description,s.quantity, s.price
    FROM sale AS s
    JOIN item AS i ON s.item_id = i.id;

04_03 Relating Multiple Tables

SELECT c.name AS Cust, c.zip, i.name AS Item, i.description, s.quantity as Qtty, s.price as Price
    FROM sale AS s -- sale table on left
    JOIN item AS i on s.item_id = i.id -- item table on right
    JOIN customer AS c on s.customer_id = c.id -- customer table on right
    ORDER BY Cust, Item
;