CODEWITHSUNDEEP
mysqlsql
Mercurial
Mercurial

Reputation: 3885

How to search for a tuple in a table in mysql

I have the following table

CREATE  TABLE IF NOT EXISTS `friends` (
  `userid` INT NOT NULL ,
  `friend` INT NOT NULL ,
  `status` INT(1) NOT NULL ,
  INDEX `user_id_fk_idx` (`userid` ASC) ,
  INDEX `friend_id_fk_idx` (`friend` ASC) ,
  PRIMARY KEY (`userid`, `friend`) ,
  CONSTRAINT `user_id_fk`
    FOREIGN KEY (`userid` )
    REFERENCES `network`.`users` (`userid` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `friend_id_fk`
    FOREIGN KEY (`friend` )
    REFERENCES `network`.`users` (`userid` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

When I want to insert a new tuple in this table, I want to check if that tuple already exists or not.

Example: when i insert (1,2,0) in table it must run if and only if (1,2,) or (2,1,) is not present in table. Or if i search for tuple (1,2,) in table it must search for (1,2,) and (2,1,*) and give results back.

Currently i am running two queries for this, can this be combined in one query?

Upvotes: 1

Views: 2529

Answers (2)

dtech
dtech

Reputation: 14060

Say a and b are your userID's, then this will give TRUE if either of the tuples exists, FALSE if not

SELECT COUNT(*) > 0
FROM friends
WHERE
    (friends.userid = a AND friends.friend = b)
OR  (friends.userid = b AND friends.friend = a)

You can also construct a bidirectional relation from your table

SELECT friends.userid AS userid, friends.friend AS friend FROM friends
UNION ALL
SELECT friends.friend AS userid, friends.userid AS friend FROM friends

IF you want to do it all in one INSERT query you'll need to use INSERT ... SELECT combined with WHERE NOT EXISTS in the SELECT part. Working example

INSERT INTO friends (userid, friend)
SELECT a,b FROM dual
WHERE NOT EXISTS (
    SELECT friends.userid
    FROM friends
    WHERE
        (friends.userid = a AND friends.friend = b)
    OR  (friends.userid = b AND friends.friend = a)
)

Upvotes: 0

OneSolitaryNoob
OneSolitaryNoob

Reputation: 5757

One way to do this is to follow the "bi-directional" example here (it's pretty good)

How to implement a bidirectional unique index across multiple columns

Once you've got this in place you can either

  1. SELECT and if nothing there INSERT your new data.

or

  1. INSERT and catch error if one is thrown because the data is already there.

Upvotes: 1

Related Questions