how to create 2 tables with foreign and primary key in sql fiddle?

2 tables are RetailOutlet and SalesMan, both are connected by ROID, ROID is primary, unique for Retail Outlet.

Salesman has primary key SID, but also has foreign key ROID.

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');

CREATE TABLE IF NOT EXISTS `SalesMan` (
  `sid` int(6) unsigned NOT NULL,  
  `sname` varchar(200) NOT NULL,
  `manager` int(4) unsigned NOT NULL,
  `id` int(6) unsigned NOT NULL,
  PRIMARY KEY (`sid`),
  FOREIGN KEY (`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');

My query was to get stores not in New York and where there is only 1 salesman, display in alphabetical order by name.

    SELECT * FROM SalesMan s INNER JOIN RetailOutlet r ON 
s.ROID = r.ROID where count(s.SID) = 1 and 
r.Location NOT LIKE 'New York' order by s.sname desc

I can't figure out what's not working.

Solution

To properly test, I added some salesmen:

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');

Since you're interested in records in RetailOutlet, I changed the order of the join, so that record in the RetailOutlet form the basis of your search. Then I do the usual JOIN and GROUP BY the SalesMan excluding records that don't have exactly one record:

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

This dbfiddle shows the working steps from creating the tables to the final query.

A caveat: It's not normal practice to use id for anything other than for the current table id. Using id for a column that references another table while naming sid to be the local tables actual id will most likely confuse other developers. I suggest you change to:

CREATE TABLE IF NOT EXISTS `SalesMan` (
  `id` int(6) unsigned NOT NULL,  
  `sname` varchar(200) NOT NULL,
  `manager` int(4) unsigned NOT NULL,
  `rid` int(6) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`rid`) REFERENCES RetailOutlet (`id`)
) DEFAULT CHARSET=utf8;

..and change your inserts and queries accordingly.