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

Question

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.

http://sqlfiddle.com/#!9/b1cd8a

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.

Answer 1

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.

Answer 2

Your foreign key declaration has an error in it, it should state which primary key it is referencing:

FOREIGN KEY (`id`) REFERENCES RetailOutlet (`id`)

This is the full SQL I am using:

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

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` VARCHAR(200) NOT NULL,
  `id` int(6) unsigned NOT NULL,
  PRIMARY KEY (`sid`),
  FOREIGN KEY (`id`) REFERENCES RetailOutlet (`id`)
);

INSERT INTO `SalesMan` (`sid`, `sname`, `manager`, `id`) VALUES
  ('1', 'john', 'fingao', '2'),
  ('2', 'bekc', 'dilda', '3'),
  ('3', 'aaa', 'elda', '4'),
  ('4', 'erjan', 'rrrrokcks', '1');

EDIT:

And your select query is also not going to work with that COUNT function in the where clause.

 SELECT * 
 FROM SalesMan s 
 INNER JOIN RetailOutlet r ON s.id = r.id
 WHERE r.Location NOT LIKE 'New York' 
 ORDER BY s.sname desc

EDIT 2:

Because the OP has a typo in it:

 SELECT * 
 FROM SalesMan s 
 INNER JOIN RetailOutlet r ON s.id = r.id
 WHERE r.Location NOT LIKE 'newyork' 
 ORDER BY s.sname desc