0

Does anyone sees any error in this?
I tried everything, google it and still getting the same results, even when i replace the $uid with the number of the id still the same.
I have two columns friend_one and friend_two and i want to show results where neither column has the $uid
SELECT * FROM users U, friends F WHERE NOT (F.friend_two = '$uid' OR F.friend_one = '$uid') GROUP BY U.uid ORDER BY U.uid DESC

4
Contributors
18
Replies
62
Views
3 Years
Discussion Span
Last Post by SimonIoa
0

How about this?

SELECT * FROM users U, friends F WHERE '$uid' NOT IN(F.friend_two, F.friend_one) GROUP BY U.uid ORDER BY U.uid DESC
0

sorry for the delay Still the same diafol... And the thing is that it returns me ($uid) as well as others that it doesnt suppose to

0

To get correct result
1) I think you need to join clause to join users U, friends F
2) need to put where f.friend_two <> '$uid' and F.friend_one <> '$uid'

0
SELECT * FROM users U LEFT JOIN friends F ON U.uid=F.friend_one WHERE NOT (F.friend_two = '$uid' OR F.friend_one = '$uid') GROUP BY U.uid ORDER BY U.uid DESC

Did the join it doesn't appear me ($uid) anymore but still appears my friends. I am asking to return data that the F.friend_one or F.friend_two isnt $uid that means people that aren't friends with me ($uid)

0

I guess problem is in your join

LEFT JOIN friends F ON U.uid=F.friend_one

You already joing uid with freind one , with that query you willl never receive result.

can you post both sample table script and sample data here or at http://sqlfiddle.com/

Edited by urtrivedi

0

You mean this right?
SELECT * FROM users U friends F ON U.uid=F.friend_one WHERE f.friend_two <> '$uid' AND F.friend_one <> '$uid' GROUP BY U.uid

0

sorry this
SELECT * FROM users U, friends F WHERE f.friend_two <> '$uid' AND F.friend_one <> '$uid' GROUP BY U.uid

without the ON

0

With this returns me as well...
SELECT * FROM users U, friends F WHERE f.friend_two <> '$uid' AND F.friend_one <> '$uid' GROUP BY U.uid ORDER BY U.uid DESC

i tried everything

0
-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: May 24, 2014 at 01:06 PM
-- Server version: 5.6.17
-- PHP Version: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `wall`
--

-- --------------------------------------------------------


-- Table structure for table `friends`
--

CREATE TABLE IF NOT EXISTS `friends` (
  `friend_id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_one` int(11) DEFAULT NULL,
  `friend_two` int(11) DEFAULT NULL,
  `role` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`friend_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=155 ;

--
-- Dumping data for table `friends`
--

INSERT INTO `friends` (`friend_id`, `friend_one`, `friend_two`, `role`) VALUES
(1, 1, 1, 'me'),
(2, 2, 2, 'me'),
(3, 3, 3, 'me'),
(4, 4, 4, 'me'),
(5, 5, 5, 'me'),
(6, 6, 6, 'me'),
(7, 7, 7, 'me'),
(8, 8, 8, 'me'),
(9, 9, 9, 'me'),
(10, 3, 1, 'fri'),
(11, 3, 4, 'fri'),
(12, 3, 9, 'fri'),
(13, 3, 5, 'fri'),
(14, 3, 8, 'fri'),
(15, 3, 6, 'fri'),
(16, 3, 7, 'fri'),
(17, 1, 7, 'fri'),
(18, 1, 6, 'fri'),
(19, 1, 8, 'fri'),
(20, 1, 5, 'fri'),
(21, 1, 9, 'fri'),
(22, 1, 4, 'fri'),
(23, 1, 3, 'fri'),
(24, 2, 1, 'fri'),
(25, 5, 3, 'fri'),
(26, 5, 4, 'fri'),
(27, 5, 9, 'fri'),
(28, 5, 8, 'fri'),
(29, 5, 6, 'fri'),
(30, 5, 7, 'fri'),
(31, 2, 4, 'fri'),
(33, 2, 3, 'fri'),
(34, 2, 7, 'fri'),
(35, 2, 8, 'fri'),
(36, 4, 2, 'fri'),
(37, 4, 3, 'fri'),
(38, 4, 9, 'fri'),
(39, 4, 5, 'fri'),
(40, 2, 9, 'fri'),
(41, 4, 8, 'fri'),
(42, 4, 6, 'fri'),
(43, 4, 7, 'fri'),
(44, 2, 5, 'fri'),
(45, 4, 1, 'fri'),
(46, 2, 6, 'fri'),
(47, 7, 2, 'fri'),
(48, 7, 6, 'fri'),
(49, 7, 1, 'fri'),
(50, 7, 4, 'fri'),
(51, 7, 3, 'fri'),
(52, 7, 8, 'fri'),
(53, 7, 5, 'fri'),
(62, 7, 9, 'fri'),
(63, 9, 1, 'fri'),
(64, 9, 2, 'fri'),
(65, 9, 3, 'fri'),
(66, 9, 4, 'fri'),
(67, 9, 5, 'fri'),
(68, 9, 8, 'fri'),
(69, 9, 6, 'fri'),
(70, 9, 7, 'fri'),
(71, 5, 2, 'fri'),
(72, 5, 1, 'fri'),
(73, 6, 1, 'fri'),
(74, 3, 2, 'fri'),
(75, 6, 2, 'fri'),
(76, 6, 3, 'fri'),
(77, 6, 4, 'fri'),
(78, 6, 9, 'fri'),
(79, 6, 5, 'fri'),
(80, 6, 8, 'fri'),
(81, 6, 7, 'fri'),
(82, 10, 10, 'me'),
(84, 11, 11, 'me'),
(90, 10, 8, 'fri'),
(93, 2, 10, 'fri'),
(94, 12, 12, 'me'),
(96, 12, 1, 'fri'),
(97, 13, 13, 'me'),
(98, 12, 11, 'fri'),
(99, 12, 2, 'fri'),
(100, 12, 3, 'fri'),
(101, 12, 4, 'fri'),
(102, 12, 9, 'fri'),
(103, 12, 5, 'fri'),
(104, 12, 8, 'fri'),
(105, 12, 6, 'fri'),
(106, 12, 7, 'fri'),
(107, 13, 1, 'fri'),
(108, 2, 12, 'fri'),
(109, 2, 13, 'fri'),
(110, 12, 13, 'fri'),
(111, 2, 11, 'fri'),
(114, 1, 10, 'fri'),
(116, 1, 11, 'fri'),
(118, 1, 13, 'fri'),
(121, 1, 2, 'fri'),
(122, 14, 14, 'me'),
(127, 14, 2, 'fri'),
(129, 14, 4, 'fri'),
(130, 14, 7, 'fri'),
(132, 14, 6, 'fri'),
(133, 14, 9, 'fri'),
(142, 14, 1, 'fri'),
(143, 15, 15, 'me'),
(144, 15, 14, 'fri'),
(145, 15, 4, 'fri'),
(146, 15, 2, 'fri'),
(147, 15, 7, 'fri'),
(148, 15, 6, 'fri'),
(149, 15, 9, 'fri'),
(150, 15, 1, 'fri'),
(151, 14, 15, 'fri'),
(152, 16, 16, 'me'),
(153, 16, 14, 'fri'),
(154, 16, 9, 'fri');

-- --------------------------------------------------------

--
-- Table structure for table `messages`
--



--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `profile_pic` varchar(200) DEFAULT NULL,
  `friend_count` int(11) DEFAULT '0',
  `status` int(1) DEFAULT '1',
  `name` varchar(150) DEFAULT NULL,
  `profile_pic_status` int(1) DEFAULT '0',
  `conversation_count` int(11) DEFAULT '0',
  `updates_count` int(11) DEFAULT '0',
  `first_name` varchar(200) DEFAULT NULL,
  `last_name` varchar(200) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `birthday` varchar(20) DEFAULT NULL,
  `location` varchar(200) DEFAULT NULL,
  `hometown` varchar(200) DEFAULT NULL,
  `bio` text,
  `relationship` varchar(30) DEFAULT NULL,
  `timezone` varchar(10) DEFAULT NULL,
  `provider` varchar(10) DEFAULT NULL,
  `provider_id` int(30) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`uid`, `username`, `password`, `email`, `profile_pic`, `friend_count`, `status`, `name`, `profile_pic_status`, `conversation_count`, `updates_count`, `first_name`, `last_name`, `gender`, `birthday`, `location`, `hometown`, `bio`, `relationship`, `timezone`, `provider`, `provider_id`) VALUES
(1, 'srinivas', '63a401a18004e5c6a5b5bd3643fbb1d5', 'srinivas@9lessons.info', '13751131401.jpg', 11, 1, 'Srinivas Tamada', 1, 0, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(2, 'rajesh', '63a401a18004e5c6a5b5bd3643fbb1d5', 'rajesht9i@gmail.com', '13743246892.png', 12, 1, 'Rajesh Tamada', 1, 2, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(3, 'dinesh', '63a401a18004e5c6a5b5bd3643fbb1d5', 'Dinesh@gmail.com', '13743002873.jpg', 8, 1, 'Dinesh Tamada', 1, 1, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(4, 'ravi', '63a401a18004e5c6a5b5bd3643fbb1d5', 'ravi8x@gmail.com', NULL, 8, 1, 'Ravi Tamada', 0, 0, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(5, 'karthik', '63a401a18004e5c6a5b5bd3643fbb1d5', 'karthik@9lessons.info', '13743004075.jpg', 8, 1, 'Karthik ', 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(6, 'arun', '63a401a18004e5c6a5b5bd3643fbb1d5', 'arun@egglabs.in', '13743006356.jpg', 8, 1, 'Arun Sekar ', 1, 0, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(7, 'ramesh', '63a401a18004e5c6a5b5bd3643fbb1d5', 'ramesh.tamada@gmail.com', '13743008297.png', 8, 1, 'Ramesh Tamada', 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(9, 'sathish', '63a401a18004e5c6a5b5bd3643fbb1d5', 'sathish@9lessons.info', '13743012409.png', 8, 1, 'Satish', 1, 0, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(11, 'joker', '63a401a18004e5c6a5b5bd3643fbb1d5', 'joker@9lessons.info', '137432094211.png', 0, 1, 'JOKER', 1, 2, 9, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(12, 'spiderman', '63a401a18004e5c6a5b5bd3643fbb1d5', 'spiderman@gmail.com', '137432125312.jpg', 11, 1, 'Spiderman', 1, 0, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(13, 'superman', '63a401a18004e5c6a5b5bd3643fbb1d5', 'superman@9lessons.info', '137432130613.png', 1, 1, 'Man of Steel', 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(14, 'simon', '0552d63459c7114fe5e2642f841374fe', 'simonioannides@hotmail.com', '137951642614.jpg', 7, 1, 'Simon', 1, 0, 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(15, 'MariosPetridis', 'b7a1c5b12419e9a678fffe1bb322c5fa', 'petridisvm@gmail.com', '139930746315.jpg', 7, 1, 'Marios Petridis', 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(16, 'john', 'e10adc3949ba59abbe56e057f20f883e', 'ipapachristoudis@gmail.com', NULL, 2, 1, NULL, 0, 0, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

-- --------------------------------------------------------
0
SELECT * FROM `users` WHERE 
(
uid  not in (select friend_two from friends where friend_one='$uid' ) 
and
uid not in (select friend_one from friends where friend_two='$uid' ) 
)
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.