kqbC1YCp

1How do I select and match from multiple tables?
2-- Table structure for table `areas`
3CREATE TABLE IF NOT EXISTS `areas` (
4  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
5  `user_id` bigint(20) unsigned NOT NULL,
6  `country` varchar(20) NOT NULL,
7  `city` varchar(20) NOT NULL,
8  PRIMARY KEY (`ID`)
9) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
10
11-- Table structure for table `matches`
12CREATE TABLE IF NOT EXISTS `matches` (
13  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
14  `user_id` bigint(20) unsigned NOT NULL,
15  `view_id` bigint(20) unsigned NOT NULL,
16  `status` enum('h','n') NOT NULL,
17  `exp_date` date NOT NULL,
18  PRIMARY KEY (`ID`)
19) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
20
21-- Table structure for table `users`
22CREATE TABLE IF NOT EXISTS `users` (
23  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
24  `limit_age` varchar(5) NOT NULL DEFAULT '18:30',
25  `limit_gender` varchar(2) DEFAULT NULL,
26  `notifications` int(11) NOT NULL DEFAULT '0',
27  `name` varchar(30) NOT NULL,
28  `email` varchar(40) NOT NULL,
29  `image_big` varchar(120) NOT NULL,
30  `image_small` varchar(120) NOT NULL,
31  `crop_data` int(11) DEFAULT NULL,
32  `visible` tinyint(1) NOT NULL DEFAULT '0',
33  `age` int(11) DEFAULT NULL,
34  `registered_at` datetime NOT NULL,
35  `views` bigint(20) unsigned NOT NULL DEFAULT '0',
36  `hots` bigint(20) unsigned NOT NULL DEFAULT '0',
37  PRIMARY KEY (`ID`)
38) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
39	
40SELECT *
41FROM users a
42INNER JOIN areas ON areas.user_id = a.id
43WHERE a.id NOT IN (SELECT user_id FROM matches)
44AND NOT a.id = '12'
45AND a.limit_age = '18:30'
46AND a.visible = '1'
47AND areas.country = 'sverige'
48AND areas.city = 'gbg'
49	
50SELECT *
51FROM users a
52INNER JOIN areas ON areas.user_id = a.id
53WHERE a.id NOT IN (SELECT user_id FROM matches)
54AND a.visible = '1'
55AND a.limit_age = '18:30'
56AND a.limit_gender = 'f'
57AND areas.country = ?
58AND areas.city = ?;