CODEWITHSUNDEEP
sql
user3143433
user3143433

Reputation: 157

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.

Upvotes: 3

Views: 481

Answers (4)

Szymon
Szymon

Reputation: 43023

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

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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')

Upvotes: 0

Anid Monsur
Anid Monsur

Reputation: 4538

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.

Upvotes: 1

sameh.q
sameh.q

Reputation: 1709

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
)

Upvotes: 0

Related Questions