CODEWITHSUNDEEP
mysqlsqlselect
user1104168
user1104168

Reputation: 3

SQL Query to select records based on 2 different values in the same field and a condition

I Created a table like this for indian railways project:

CREATE TABLE IF NOT EXISTS `dennis` (
  `trid` varchar(50) NOT NULL,
  `place` varchar(50) NOT NULL,
  `si` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

then i inserted rows this way :

INSERT INTO `dennis` (`trid`, `place`, `si`) VALUES
('100', 'cochi', '3'),
('300', 'cochi', '1'),
('100', 'mumbai', '1'),
('100', 'bangalore', '2'),
('300', 'bangalore', '2'),
('300', 'mumbai', '3'),
('200', 'hyderabad', '1'),
('400', 'trivandrum', '1'),
('200', 'bangalore', '2'),
('200', 'trivandrum', '3'),
('400', 'bangalore', '2'),
('400', 'hyderabad', '3');

My problem is when i select start station as Bangalore and destination as mumbai, I am getting all the train numbers because bangalore exist for all trid ie trainid but mumbai exist only for 100 and 300.

I need a query that can return only those trid who have both mumbai and bangalore. Also the si ie Serialnumber of bangalore must be lesser than si of mumbai.

i used this query but it seems to return all the record

SELECT DISTINCT trid FROM dennis WHERE place ='mumbai' OR place='bangalore'

Upvotes: 0

Views: 2545

Answers (2)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

try this,

SELECT DISTINCT d1.trid 
FROM dennis d1
     INNER JOIN dennis d2 ON d2.trid=d1.trid
WHERE d1.place = 'bangalore' and d2.place = 'mumbai' AND d1.si < d2.si

hope this answers your question

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

SELECT d1.trid 
FROM dennis d1
INNER JOIN dennis d2 ON d2.trid=d1.trid
WHERE d1.place = 'bangalore' and d2.place = 'mumbai'

Upvotes: 1

Related Questions