'How to select all records with foreign key?

I have tables that have been created like so:

CREATE TABLE `d_account` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `account_name` varchar(128) CHARACTER SET utf8 NOT NULL,
  `user_id` smallint(5) NOT NULL,
  `type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_account_type` (`type_id`),
  CONSTRAINT `FK_type` FOREIGN KEY (`type_id`) REFERENCES `d_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `d_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`)
);

How would I select all records that exist in both tables below using the foreign key where the d_type name equals ‘large’?

sqlmarkdownmdxjs


Solution 1:[1]

That's basically just a JOIN like this:

SELECT * FROM d_types t 
JOIN d_account a ON t.id = a.type_id
WHERE t.name = 'large';

The * after select should be replaced by the columns you want to select.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jonas Metzler

Related Questions