CDLZCrBv

1Show Users I Haven't Voted On - MySQL
2CREATE TABLE IF NOT EXISTS `users` 
3(`id` int(11) NOT NULL AUTO_INCREMENT,
4`name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
5`email` varchar(200) COLLATE utf8_unicode_ci NOT NULL,PRIMARY KEY (`id`)
6);
7
8CREATE TABLE IF NOT EXISTS `whose_voted` (
9`voter_user_id` int(11) NOT NULL,
10`voted_on_user_id` int(11) NOT NULL,
11PRIMARY KEY (`voter_user_id`,`voted_on_user_id`)
12)
13
14CREATE TABLE IF NOT EXISTS `votes` (
15`id` int(11) NOT NULL AUTO_INCREMENT,
16`yes_count` int(11) NOT NULL,
17`beer_count` int(11) NOT NULL,
18`total_votes` int(11) NOT NULL,
19 PRIMARY KEY (`id`)
20)
21
22CREATE TABLE IF NOT EXISTS `user_votes` (
23`users_id` int(11) NOT NULL,
24`votes_id` int(11) NOT NULL,
25 PRIMARY KEY (`users_id`)
26)
27	
28/* Show the Names of all users that have not been Voted on */
29SELECT users.name
30FROM users
31 LEFT JOIN user_votes ON users.id = users_id
32 LEFT JOIN votes ON votes_id = votes.id
33WHERE votes.id IS NULL
34ORDER BY users.name
35	
36/* Show all the User Names that I have voted on */
37SELECT users.id AS 'User ID', users.name AS 'I've Voted On'
38FROM users
39 INNER JOIN whose_voted ON users.id = voted_on_user_id
40WHERE whose_voted.voter_user_id = 32 /* my User ID */
41	
42/* Show a random users I have Not Voted For as of yet */
43SELECT
44 unot.id, unot.name
45FROM
46 users AS unot
47WHERE NOT EXISTS (
48 /* Exclude the users you have voted on from the total user list */
49 SELECT uhave.id
50 FROM users AS uhave
51  INNER JOIN whose_voted ON uhave.id = voted_on_user_id
52 WHERE unot.id = uhave.id 
53  AND (whose_voted.voter_user_id = 32)
54) 
55AND (unot.id != 32)
56AND (RAND()<(SELECT ((1/COUNT(*))*10) FROM users))
57ORDER BY RAND() 
58LIMIT 1;
59	
60SELECT
61  users.id AS `User ID`, users.name AS 'I've not Voted On'
62FROM
63  users unot
64WHERE NOT EXISTS (
65    /* Exclude the users you have voted on from the total user list */
66    SELECT users.id AS
67    FROM users uhave
68     INNER JOIN whose_voted ON users.id = voted_on_user_id
69    WHERE whose_voted.voter_user_id = 32 /* my User ID */
70      AND unot.id = uhave.id
71)
72	
73SELECT
74  users.id AS `User ID`, 
75  users.name AS 'I've not Voted On'
76FROM
77  users unot
78  LEFT JOIN (
79    SELECT users.id AS
80    FROM users uhave
81     INNER JOIN whose_voted ON users.id = voted_on_user_id
82    WHERE whose_voted.voter_user_id = 32 /* my User ID */
83  ) uhave ON unot.id = uhave.id
84/* NULL in the have voted subquery indicates user hasn't been voted on. */
85WHERE uhave.id IS NULL
86	
87SELECT users.name 
88FROM users
89LEFT JOIN whose_voted ON users.id = voted_on_user_id
90WHERE whose_voted.voter_user_id != 32
91	
92$yourId=7;
93
94SELECT id,users.name 
95FROM users
96where id not in 
97(select distinct(voted_on_user_id) 
98 from whose_voted
99 where voter_user_id =$yourId)