Slide 15.7: Using the same table more than once

SQL SELECT Examples (Cont.)

Question VI (Using the Same Table More Than Once) Determine all pairs of customers based in the same city.

customer
cid	cname	city	discnt
c001	TipTop	Duluth	10.00
c002	Basics	Dallas	12.00
c003	Allied	Dallas	8.00
c004	ACME	Duluth	8.00
c006	ACME	Kyoto	0.00

⇒

cid	cid
c001	c004
c002	c003

C1:=customer, C2:=customer
(C1×C2) WHERE C1.city = C2.city AND C1.cid < C2.cid
SELECT c1.cid, c2.cid
  FROM customer c1, customer c2
  WHERE c1.city = c2.city AND c1.cid < c2.cid;

Nested-loop access plan pseudo-code is given on the right:

FOR c1 FROM ROW 1 TO LAST OF customer
  FOR c2 FROM ROW 1 TO LAST OF customer
    IF (c1.city = c2.city AND c1.cid < c2.cid)
    THEN PRINT OUT SELECT_LIST VALUES: c1.cid, c2.cid
  END FOR c2
END FOR c1

Question VII (Using the Same Table More Than Once)
Get cid values of customers who order a product for which an order is also placed by agent a06.

Hint: The answer is NOT

☠: SELECT DISTINCT cid
      FROM order3
      WHERE aid = 'a06'
      ORDER BY cid;

which gives a wrong result (b) instead of (a).

order3
orderno	month	cid	aid	pid	qty	dollar
1011	jan	c001	a01	p01	1000	450.00
1012	jan	c001	a01	p01	1000	450.00
1019	feb	c001	a02	p02	400	180.00
1017	feb	c001	a06	p03	600	540.00
1018	feb	c001	a03	p04	600	540.00
1023	mar	c001	a04	p05	500	450.00
1022	mar	c001	a05	p06	400	720.00
1025	apr	c001	a05	p07	800	720.00
1013	jan	c002	a03	p03	1000	880.00
1026	may	c002	a05	p03	800	704.00
1015	jan	c003	a03	p05	1200	1104.00
1014	jan	c003	a03	p05	1200	1104.00
1021	feb	c004	a06	p01	1000	460.00
1016	jan	c006	a01	p01	1000	500.00
1020	feb	c006	a03	p07	600	600.00
1024	mar	c006	a06	p01	800	400.00

⇒

cid
c001
c002
c004
c006

(a)

⇏

cid
c001
c004
c006

(b)

SELECT DISTINCT y.cid
  FROM order3 x, order3 y
  WHERE y.pid = x.pid AND x.aid = 'a06'
  ORDER BY y.cid;

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support