· 7 years ago · Dec 11, 2018, 08:10 AM
1CREATE TABLE IF NOT EXISTS `Master_DS` (
2 `ID` int(11) NOT NULL AUTO_INCREMENT,
3 `deleted` tinyint(1) NOT NULL,
4 `MachineType` varchar(255) NOT NULL,
5 `last_edit` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
6 `CURRENT_STATUS` enum('UP','DOWN','INACTIVE') DEFAULT NULL,
7 `CURRENT_STATUS_TXT` varchar(255) DEFAULT NULL,
8 `starmont_ID` decimal(8,2) DEFAULT NULL,
9 `starmont_Name` varchar(255) DEFAULT NULL,
10 `RTOWN_name` varchar(255) NOT NULL,
11 `REGION_ID` int(10) DEFAULT NULL,
12 `gen_txt` longtext,
13 `POSTCODE` varchar(255) DEFAULT NULL,
14 `week_traffic` int(4) DEFAULT NULL,
15 `month_traffic` int(5) DEFAULT NULL,
16 `LCAT1` int(11) NOT NULL,
17 `LCAT2` int(11) NOT NULL,
18 `LCAT3` int(11) NOT NULL,
19 `Address` varchar(255) NOT NULL,
20 `LP_Phone` varchar(255) NOT NULL,
21 `LP_Contact` varchar(255) NOT NULL,
22 `ScreenType` varchar(255) NOT NULL,
23 `ScreenSerial` varchar(255) NOT NULL,
24 `ScreenID` varchar(255) NOT NULL,
25 `ISPAccount` varchar(255) NOT NULL,
26 `ModemSerial` varchar(255) NOT NULL,
27 `WiFi` varchar(255) NOT NULL,
28 `ScreenLocation` longtext NOT NULL,
29 `BracketType` varchar(255) NOT NULL,
30 PRIMARY KEY (`ID`)
31) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=80 ;
32
33--
34-- Dumping data for table `Master_DS`
35--
36
37INSERT INTO `Master_DS` (`ID`, `deleted`, `MachineType`, `last_edit`, `CURRENT_STATUS`, `CURRENT_STATUS_TXT`, `starmont_ID`, `starmont_Name`, `RTOWN_name`, `REGION_ID`, `gen_txt`, `POSTCODE`, `week_traffic`, `month_traffic`, `LCAT1`, `LCAT2`, `LCAT3`, `Address`, `LP_Phone`, `LP_Contact`, `ScreenType`, `ScreenSerial`, `ScreenID`, `ISPAccount`, `ModemSerial`, `WiFi`, `ScreenLocation`, `BracketType`) VALUES
38(1, 0, 'EEE', '2012-09-13 20:10:36', 'UP', '', 105110.32, '01. ABT: BOWL - Dogwood Bowling', 'Dogwood Bowling & BillIards', 1, '', 'V2S 5G7', 5600, 24260, 4, 13, 0, '13-33550 South Fraser Way, Abbotsford', '', '', '22" portait', '', 'screen CA-BC-ABT-105110.31', 'SHAW - 014-1081-0863', '08LKL7XSDPH4', 'onext / rtnmedia2010', 'Main cash counter', ''),
39(2, 0, '', '2012-09-18 22:56:49', 'DOWN', 'down since August 9, 2012', 105110.37, '02. ABT: CAFE - Wired Monk', 'Wired Monk', 1, '', 'V3G 2C6', 1000, 4330, 1, 2, 0, '100 - 1910 N. Parallel Road, Abbotsford', '', '', '', '', 'CA-BC-ABT-105110.37', '', '', '', '', '');
40
41
42
43
44
45
46CREATE TABLE IF NOT EXISTS `Location_Cat` (
47 `L_ID` int(11) NOT NULL AUTO_INCREMENT,
48 `L_Name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
49 PRIMARY KEY (`L_ID`),
50 KEY `L_ID` (`L_ID`)
51) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=29 ;
52
53--
54-- Dumping data for table `Location_Cat`
55--
56
57INSERT INTO `Location_Cat` (`L_ID`, `L_Name`) VALUES
58(1, 'FOOD'),
59(2, 'CAFE'),
60(3, 'FULL'),
61(4, 'SHOP'),
62(5, 'GYM'),
63(6, 'HEAL'),
64(7, 'PUB'),
65(8, 'QUIK'),
66(9, 'TOUR'),
67(10, 'GROC'),
68(13, 'C-STORE'),
69(12, 'ENT'),
70(14, 'HOTL'),
71(20, 'HOME'),
72(21, 'BAK'),
73(22, 'LIQ'),
74(23, 'SERV'),
75(24, 'FIN'),
76(25, 'ACTV'),
77(26, 'FILM'),
78(27, 'DOC'),
79(28, 'RETAIL');
80
81
82
83
84
85
86so, the Location_Cat table is a lookup table.
87
88the 3 fields in the Master_DS table, LCAT1, LCAT2 and LCAT3 refer to the Location_Cat table.
89
90
91so when i do a SELECT from the Master_DS table, i wish to join the location_cat table 3 times, for LCAT1 LCAT2 and LCAT3. so the returned query from the select on Master_DS table has 3 extra colunms LCAT1_Name, LCAT2_Name and LCAT3_Name which has the L_Name value from the Location_Cat table from:
92
93Master_DS.LCAT1=Location_Cat.L_ID
94Master_DS.LCAT2=Location_Cat.L_ID
95Master_DS.LCAT3=Location_Cat.L_ID
96
97make sense? I could do a separate query of the Location_Cat table to resolve each LCAT1 col to its text name, but thats very very inefficient right..