StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I select and match from multiple tables?
text
Body
copied!<p>This is my table layout:</p> <pre><code>-- Table structure for table `areas` CREATE TABLE IF NOT EXISTS `areas` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `country` varchar(20) NOT NULL, `city` varchar(20) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- Table structure for table `matches` CREATE TABLE IF NOT EXISTS `matches` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `view_id` bigint(20) unsigned NOT NULL, `status` enum('h','n') NOT NULL, `exp_date` date NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- Table structure for table `users` CREATE TABLE IF NOT EXISTS `users` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `limit_age` varchar(5) NOT NULL DEFAULT '18:30', `limit_gender` varchar(2) DEFAULT NULL, `notifications` int(11) NOT NULL DEFAULT '0', `name` varchar(30) NOT NULL, `email` varchar(40) NOT NULL, `image_big` varchar(120) NOT NULL, `image_small` varchar(120) NOT NULL, `crop_data` int(11) DEFAULT NULL, `visible` tinyint(1) NOT NULL DEFAULT '0', `age` int(11) DEFAULT NULL, `registered_at` datetime NOT NULL, `views` bigint(20) unsigned NOT NULL DEFAULT '0', `hots` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ; </code></pre> <p>I will try to explain this better:</p> <p>I have a given <code>ID</code>. I would like to select one entry from <code>users</code> which is not the <code>ID</code> i have given AND which <code>user_id</code> does not exist in <code>matches</code> AND has <code>visible</code> = 1 AND where any <code>country</code> + <code>city</code> matches the given users <code>country</code> + <code>city</code></p> <p>Is this the correct way to do it (12 is an example of an given ID):</p> <pre><code>SELECT * FROM users a INNER JOIN areas ON areas.user_id = a.id WHERE a.id NOT IN (SELECT user_id FROM matches) AND NOT a.id = '12' AND a.limit_age = '18:30' AND a.visible = '1' AND areas.country = 'sverige' AND areas.city = 'gbg' </code></pre> <p>Sorry for the confusion :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload