--
-- Database: `testing`
--

-- --------------------------------------------------------
--
-- Table structure for table `prisType`
--

CREATE TABLE IF NOT EXISTS `prisType` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
--
-- Table structure for table `vareListe`
--

CREATE TABLE IF NOT EXISTS `vareListe` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `navn` varchar(30) NOT NULL,
  `info` varchar(100) NOT NULL,
  `slettet` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
--
-- Table structure for table `varePris`
--

CREATE TABLE IF NOT EXISTS `varePris` (
  `id` int(11) NOT NULL,
  `pris` float NOT NULL,
  `type` int(11) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



################

mysql> INSERT INTO prisType VALUES(NULL, 'Innkjøpspris');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO prisType VALUES(NULL, 'Utleiepris');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO prisType VALUES(NULL, 'Torsdagspris');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM prisType;
+----+---------------+
| id | type          |
+----+---------------+
|  5 | Innkjøpspris |
|  6 | Utleiepris    |
|  7 | Torsdagspris  |
+----+---------------+
3 rows in set (0.00 sec)

################

mysql> INSERT INTO vareListe VALUES(NULL, 'Hansa Pils', 'Brygget i regnet', DEFAULT);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO vareListe VALUES(NULL, 'Ringnes Pils', 'Teite folk', DEFAULT);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO vareListe VALUES(NULL, 'Cola', 'Best!', DEFAULT);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM vareListe;
+----+--------------+------------------+---------+
| id | navn         | info             | slettet |
+----+--------------+------------------+---------+
|  4 | Ringnes Pils | Teite folk       |       0 |
|  3 | Hansa Pils   | Brygget i regnet |       0 |
|  5 | Cola         | Best!            |       0 |
+----+--------------+------------------+---------+
3 rows in set (0.00 sec)

################

mysql> INSERT INTO varePris VALUES('5', '16.00', '7');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO varePris VALUES('5', '8.00', '5');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO varePris VALUES('5', '4.00', '6');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO varePris VALUES('3', '10.00', '6');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO varePris VALUES('3', '20.00', '7');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO varePris VALUES('4', '10.00', '5');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM varePris;
+----+------+------+
| id | pris | type |
+----+------+------+
|  5 |   16 |    7 |
|  5 |    8 |    5 |
|  5 |    4 |    6 |
|  3 |   10 |    6 |
|  3 |   20 |    7 |
|  4 |   10 |    5 |
+----+------+------+
6 rows in set (0.00 sec)

################

mysql> SELECT l.id, l.navn, l.info, p.pris FROM vareListe l
    -> INNER JOIN varePris p ON l.id = p.id
    -> INNER JOIN prisType t ON p.type = t.id
    -> WHERE (l.slettet='0');
+----+--------------+------------------+------+
| id | navn         | info             | pris |
+----+--------------+------------------+------+
|  5 | Cola         | Best!            |   16 |
|  5 | Cola         | Best!            |    8 |
|  5 | Cola         | Best!            |    4 |
|  3 | Hansa Pils   | Brygget i regnet |   10 |
|  3 | Hansa Pils   | Brygget i regnet |   20 |
|  4 | Ringnes Pils | Teite folk       |   10 |
+----+--------------+------------------+------+
6 rows in set (0.00 sec)

mysql> UPDATE vareListe SET slettet='1' WHERE (id='4');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT l.id, l.navn, l.info, p.pris FROM vareListe l
    -> INNER JOIN varePris p ON l.id = p.id
    -> INNER JOIN prisType t ON p.type = t.id
    -> WHERE (l.slettet='0');
+----+------------+------------------+------+
| id | navn       | info             | pris |
+----+------------+------------------+------+
|  5 | Cola       | Best!            |   16 |
|  5 | Cola       | Best!            |    8 |
|  5 | Cola       | Best!            |    4 |
|  3 | Hansa Pils | Brygget i regnet |   10 |
|  3 | Hansa Pils | Brygget i regnet |   20 |
+----+------------+------------------+------+
5 rows in set (0.00 sec)