Första sidan | Forum start | Logga in | Bli medlem |


MySQL: Left join, tre tabeller inblandade

Forumet för dej som vill diskutera MySQL, Oracle och andra databaser till UNIX / Linux.

MySQL: Left join, tre tabeller inblandade

Inläggav Hivemind 20 december 2006, 13:23

Hej, jag har tabeller med följande utseende:
Kod: Markera allt
mysql> SELECT * FROM item;
+------+-----------------+------+-------+------+----------+
| key_ | name            | dept | price | qoh  | supplier |
+------+-----------------+------+-------+------+----------+
|   11 | Wash Cloth      |    1 |    75 |  575 |      213 |
|   19 | Bellbottoms     |   43 |   450 |  600 |       33 |
|   21 | ABC Blocks      |    1 |   198 |  405 |      125 |
|   23 | 1 lb Box        |   10 |   215 |  100 |       42 |
|   25 | 2 lb Box, Mix   |   10 |   450 |   75 |       42 |
|   26 | Earrings        |   14 |  1000 |   20 |      199 |
|   43 | Maze            |   49 |   325 |  200 |       89 |
|   52 | Jacket          |   60 |  3295 |  300 |       15 |
|  101 | Slacks          |   63 |  1600 |  325 |       15 |
|  106 | Clock Book      |   49 |   198 |  150 |      125 |
|  107 | The 'Feel' Book |   35 |   225 |  225 |       89 |
|  115 | Gold Ring       |   14 |  4995 |   10 |      199 |
|  118 | Towels, Bath    |   26 |   250 | 1000 |      213 |
|  119 | Squeeze Ball    |   49 |   250 |  400 |       89 |
|  120 | Twin Sheet      |   26 |   800 |  750 |      213 |
|  121 | Queen Sheet     |   26 |  1375 |  600 |      213 |
|  127 | Ski Jumpsuit    |   65 |  4350 |  125 |       15 |
|  165 | Jean            |   65 |   825 |  500 |       33 |
|  258 | Shirt           |   58 |   650 | 1200 |       33 |
|  301 | Boy's Jean Suit |   43 |  1250 |  500 |       33 |
+------+-----------------+------+-------+------+----------+
20 rows in set (0.00 sec)

mysql> SELECT * FROM supplier;
+------+--------------+----------------+
| key_ | name         | city           |
+------+--------------+----------------+
|    5 | Amdahl       | San Diego      |
|   15 | White Stag   | White Plains   |
|   20 | Wormley      | Hickville      |
|   33 | Levi-Strauss | San Francisco  |
|   42 | Whitman's    | Denver         |
|   62 | Data General | Atlanta        |
|   67 | Edger        | Salt Lake City |
|   89 | Fisher-Price | Boston         |
|  122 | White Paper  | Seattle        |
|  125 | Playskool    | Dallas         |
|  199 | Koret        | Los Angeles    |
|  213 | Cannon       | Atlanta        |
|  241 | IBM          | New York       |
|  440 | Spooley      | Paxton         |
|  475 | DEC          | Amherst        |
|  999 | A E Neumann  | Madison        |
+------+--------------+----------------+
16 rows in set (0.00 sec)

mysql> SELECT * FROM sale;
+--------+------+----------+
| debit  | item | quantity |
+--------+------+----------+
| 100581 |  118 |        5 |
| 100581 |  120 |        1 |
| 100582 |   26 |        1 |
| 100586 |  106 |        2 |
| 100586 |  127 |        3 |
| 100592 |  258 |        1 |
| 100593 |   23 |        2 |
| 100594 |   52 |        1 |
+--------+------+----------+
8 rows in set (0.00 sec)


Om man vill skapa en view som innehåller suppliers som levererat items som har sålts kan man ju göra
Kod: Markera allt
mysql> CREATE VIEW AuxView(supplier, item, quantity) AS SELECT supplier.name, item.name, sale.quantity FROM supplier
, item, sale WHERE supplier.key_ = item.supplier AND sale.item = item.key_;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM AuxView;
+--------------+--------------+----------+
| supplier     | item         | quantity |
+--------------+--------------+----------+
| Cannon       | Towels, Bath |        5 |
| Cannon       | Twin Sheet   |        1 |
| Koret        | Earrings     |        1 |
| Playskool    | Clock Book   |        2 |
| White Stag   | Ski Jumpsuit |        3 |
| Levi-Strauss | Shirt        |        1 |
| Whitman's    | 1 lb Box     |        2 |
| White Stag   | Jacket       |        1 |
+--------------+--------------+----------+
8 rows in set (0.00 sec)


Men nu vill jag ha med leverantörer som levererat varor som *inte* sålts och då måste jag ändra från en (implicit) inner join till en LEFT JOIN, och det får jag inte till syntaxmässigt i MySQL. I Oracle är koden:
Kod: Markera allt
CREATE OR REPLACE VIEW mysupply(supplier, item, quantity) AS
SELECT supplier.name, item.name, sale.quantity
FROM supplier,item,sale
WHERE supplier.key = item.supplier AND item.key = sale.item(+);


Hur översätter man det till MySQL syntax?
Nothing has changed; it's the surrounding bullshit that has grown.
Hivemind
 
Inlägg: 59
Blev medlem: 25 januari 2004, 12:51

Inläggav Hivemind 20 december 2006, 14:40

Det här är en variant, men jag känner mig inte helt tillfreds med den eftersom det är en join i andra selecten också (dvs min view innhåller egentligen inte all information jag vill ha (nämligen supplier.name)), men den är nått att bygga på/utgå ifrån iaf:

Kod: Markera allt
mysql> CREATE VIEW AuxView AS SELECT supplier, item, quantity from item LEFT JOIN sale ON item = key_;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, SUM(quantity) AS "Quantities sold" FROM supplier, auxview WHERE key_ = supplier GROUP BY name;
+--------------+-----------------+
| name         | Quantities sold |
+--------------+-----------------+
| Cannon       |               6 |
| Fisher-Price |            NULL |
| Koret        |               1 |
| Levi-Strauss |               1 |
| Playskool    |               2 |
| White Stag   |               4 |
| Whitman's    |               2 |
+--------------+-----------------+
7 rows in set (0.00 sec)
Nothing has changed; it's the surrounding bullshit that has grown.
Hivemind
 
Inlägg: 59
Blev medlem: 25 januari 2004, 12:51

Inläggav Hivemind 20 december 2006, 15:03

Ah, nu så! :D

Kod: Markera allt
mysql> CREATE VIEW AuxView AS SELECT supplier.name AS "Supplier", item as "Item ID", quantity FROM supplier, item LEFT JOIN sale ON item = key_ WHERE item.supplier = supplier.key_;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM AuxView;
+--------------+---------+----------+
| Supplier     | Item ID | quantity |
+--------------+---------+----------+
| White Stag   |      52 |        1 |
| White Stag   |    NULL |     NULL |
| White Stag   |     127 |        3 |
| Levi-Strauss |    NULL |     NULL |
| Levi-Strauss |    NULL |     NULL |
| Levi-Strauss |     258 |        1 |
| Levi-Strauss |    NULL |     NULL |
| Whitman's    |      23 |        2 |
| Whitman's    |    NULL |     NULL |
| Fisher-Price |    NULL |     NULL |
| Fisher-Price |    NULL |     NULL |
| Fisher-Price |    NULL |     NULL |
| Playskool    |    NULL |     NULL |
| Playskool    |     106 |        2 |
| Koret        |      26 |        1 |
| Koret        |    NULL |     NULL |
| Cannon       |    NULL |     NULL |
| Cannon       |     118 |        5 |
| Cannon       |     120 |        1 |
| Cannon       |    NULL |     NULL |
+--------------+---------+----------+
20 rows in set (0.00 sec)

mysql> SELECT supplier, SUM(quantity) AS "Quantities sold" FROM AuxView GROUP BY supplier;
+--------------+-----------------+
| Supplier     | Quantities sold |
+--------------+-----------------+
| Cannon       |               6 |
| Fisher-Price |            NULL |
| Koret        |               1 |
| Levi-Strauss |               1 |
| Playskool    |               2 |
| White Stag   |               4 |
| Whitman's    |               2 |
+--------------+-----------------+
7 rows in set (0.00 sec)
Nothing has changed; it's the surrounding bullshit that has grown.
Hivemind
 
Inlägg: 59
Blev medlem: 25 januari 2004, 12:51


Återgå till Databas forum

Vilka är online

Användare som besöker denna kategori: Inga registrerade användare och 2 gäster