'SQL - select rows that their child rows's column contains specific string

For example:

RouteID    StopName 
1           stop_1 
1           stop_2
1           stop_3 
2           stop_1 
2           stop_2 
3           stop_4 
4           stop_5

I want to select the route that it has a stop named 'stop_1', I expect the results as follows:

RouteID    StopName 
 1           stop_1 
 1           stop_2
 1           stop_3 
 2           stop_1 
 2           stop_2

EDIT

How about the RouteID is from the table Route and StopName is from the table Stop? Actually, the above table is their relation table.

sql


Solution 1:[1]

You can use an inner query that selects the routes for that:

select r.RouteID, s.StopName from route r
inner join stop s on r.StopID = s.StopID
where RouteID in
  (select t1.RouteID from route t1
  where exists (select * from stop s2 where t1.StopID = s2.StopID and s2.StopName = 'stop_1'))
order by r.RouteID, s.StopName

SQL Fiddle demo

Solution 2:[2]

New Answer for your edit

Again, assuming routes table is named Routes and your relation table is named RouteStops.

SELECT * FROM Routes r
JOIN RouteStops rs ON rs.RouteID = r.RouteID
WHERE rs.StopName = 'stop_1'

Old Answer:

For the sake of example, I'm going to assume your table name is Routes

SELECT * FROM Routes r
JOIN Routes r2 ON r.RouteID = r2.RouteID
WHERE r2.StopName = 'stop_1'

I'm basically joining the table with itself whenever a route contains stop_1 and then listing all of that routes entries.

Solution 3:[3]

New Answer:

Supposing that from the expected results you mean that you want to get the rows that shares the same RouteID with stop_1, I will do the following:

SELECT * FROM YOUR_TABLE_NAME
WHERE RouteID IN 
(
SELECT

RouteID

FROM YOUR_TABLE_NAME

WHERE StopName = 'stop_1'
)

Old answer: IGNORE, KEPT JUST DUE TO COMMENTS

In oracle syntax, you can use the Sub String function. it takes the first input the string you want to work with, second the starting position, the last one is the length, so 1----------- is 12 characters long, the statement will be the following

substr(your_column, 12, LENGTH(your_column) - 12)

now this logic can be utilized as following:

SELECT
Your desired values to get

FROM
(
SELECT

substr(your_column, 1, 1) AS ID, substr(your_column, 12, LENGTH(your_column) - 12) AS VALUE

FROM YOUR TABLE
)

Solution 4:[4]

Try This

SELECT ROUTID 
FROM Routes R
JOIN Stop S ON R.RouteID = S.RouteID
WHERE S.StopName IN ('stop_1 ','stop_2','stop_3')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3
Solution 4 Vignesh Kumar A

Related Questions