By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `RetailOutlet` (
`id` int(6) unsigned NOT NULL,
`year` int(4) unsigned NOT NULL,
`location` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `RetailOutlet` (`id`, `year`, `location`) VALUES
('1', '1994', 'oregon'),
('2', '1990', 'amazon'),
('3', '2004', 'california'),
('4', '1997', 'newyork');
Records: 4 Duplicates: 0 Warnings: 0
CREATE TABLE IF NOT EXISTS `SalesMan` (
`sid` int(6) unsigned NOT NULL,
`sname` varchar(200) NOT NULL,
`manager` VARCHAR(200) NOT NULL,
`id` int(6) unsigned NOT NULL,
PRIMARY KEY (`sid`),
FOREIGN KEY (`id`) REFERENCES RetailOutlet (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `SalesMan` (`sid`, `sname`, `manager`, `id`) VALUES
('1', 'john', 'fingao', '2'),
('2', 'bekc', 'dilda', '3'),
('3', 'aaa', 'elda', '4'),
('4', 'erjan', 'rrrrokcks', '1'),
('5', 'john', 'fingao', '1'),
('6', 'benny', 'fingao', '1'),
('7', 'silvia', 'fingao', '1'),
('8', 'peter', 'dilda', '2'),
('9', 'karen', 'dilda', '2');
Records: 9 Duplicates: 0 Warnings: 0
SELECT *
FROM RetailOutlet r
INNER JOIN SalesMan s ON s.id = r.id
WHERE r.Location NOT LIKE 'newyork'
ORDER BY s.sname DESC
| id | year | location | sid | sname | manager | id |
|---|---|---|---|---|---|---|
| 1 | 1994 | oregon | 7 | silvia | fingao | 1 |
| 2 | 1990 | amazon | 8 | peter | dilda | 2 |
| 2 | 1990 | amazon | 9 | karen | dilda | 2 |
| 1 | 1994 | oregon | 5 | john | fingao | 1 |
| 2 | 1990 | amazon | 1 | john | fingao | 2 |
| 1 | 1994 | oregon | 4 | erjan | rrrrokcks | 1 |
| 1 | 1994 | oregon | 6 | benny | fingao | 1 |
| 3 | 2004 | california | 2 | bekc | dilda | 3 |
SELECT *, count(s.id)
FROM RetailOutlet r
INNER JOIN SalesMan s ON s.id = r.id
WHERE r.Location NOT LIKE 'newyork'
GROUP BY s.id
ORDER BY s.sname DESC
| id | year | location | sid | sname | manager | id | count(s.id) |
|---|---|---|---|---|---|---|---|
| 2 | 1990 | amazon | 1 | john | fingao | 2 | 3 |
| 1 | 1994 | oregon | 4 | erjan | rrrrokcks | 1 | 4 |
| 3 | 2004 | california | 2 | bekc | dilda | 3 | 1 |
SELECT *
FROM RetailOutlet r
INNER JOIN SalesMan s ON s.id = r.id
WHERE r.Location NOT LIKE 'newyork'
GROUP BY s.id
HAVING count(s.id) = 1
ORDER by s.sname DESC
| id | year | location | sid | sname | manager | id |
|---|---|---|---|---|---|---|
| 3 | 2004 | california | 2 | bekc | dilda | 3 |