CODEWITHSUNDEEP
mysqlsql
user3144542
user3144542

Reputation: 599

Search in 1 column with where and statement

I am trying to search in a column with a where statement. So my database looks like this:

CREATE TABLE test
    (
 zID varchar(20), 
 tID varchar(30)
 );

INSERT INTO test
(zID, tID)
VALUES
('1','1'),
('1','2'),
('2','1'),
('2','2'),
('3','1');

And Then I use the following SQL statement to search:

SELECT * from test where tID = 1 and tID = 2

So when I put this the query should return 1 and 2 but not 3 all of which are zIDs, I have tried using an in but this would return all the zIDs. This is not what I want though, so my question is how can I search with the and?

Instead using in which is kind of like an or instead.

Heres a fiddle

Upvotes: 1

Views: 37

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

The trick with this is to use a COUNT() aggregate which verifies that the total number of DISTINCT tID per zID group is equal to 2 when filtered via an IN() clause.

SELECT zID
FROM test
/* Limit rows to tID 1,2
WHERE tID IN (1,2)
GROUP BY zID
/* Verify that the total DISTINCT number of tID
   per group of zID is 2, that way you know *both*
   values are present.
   Substitute the total number of values in the IN() for the comparison
   against COUNT() (substitute for 2 here) to do this dynamically
*/
HAVING COUNT(DISTINCT tID) = 2

The updated fiddle

The above only returns the zID, but it can be expanded to return all columns using a JOIN or another IN() clause.

/* Get all columns for the zID returned inside */
SELECT * FROM test WHERE zID IN (
  SELECT zID
  FROM test
  WHERE tID IN (1,2)
  GROUP BY zID
  HAVING COUNT(DISTINCT tID) = 2
)

Or with a JOIN

SELECT test.*
FROM
  test
  INNER JOIN (
      SELECT zID
      FROM test
      WHERE tID IN (1,2)
      GROUP BY zID
      HAVING COUNT(DISTINCT tID) = 2
    ) tz ON test.zID = tz.zID

Upvotes: 1

Related Questions