Replace values in an sql query according to results of a nested query

Question

I have two tables : request and location

Sample data for request

request id | requestor | locations
     1     |   ankur   |   2,5
     2     |   akshay  |   1  
     3     |   avneet  |   3,4
     4     |   priya   |   4

Sample data for locations

loc_id     |  loc_name |
     1     |  gondor   
     2     |  rohan
     3     |  mordor   
     4     |  bree   
     5     |  shire

I'd like to find the request_id for a particular location. If I do this with location_id, I am getting correct results.

select request_id from request where locations like "%,3%" or locations like "%3,%";

This query gives me the requests raised for location id = 3

How can I achieve this for loc_name instead? Replacing the digit in the "like" part of the query with

select loc_id from locations where loc_name = "mordor"

Any help with this would be very helpful. Thanks.

peterm · Accepted Answer · 2014-01-29 07:14:56Z

You can use FIND_IN_SET()

SELECT *
  FROM request r JOIN locations l
    ON FIND_IN_SET(loc_id, locations) > 0
 WHERE loc_name = 'mordor'

Here is SQLFiddle demo

But you better normalize your data by introducing a many-to-many table that may look like

CREATE TABLE request_location
(
  request_id INT NOT NULL,
  loc_id INT NOT NULL,
  PRIMARY KEY (request_id, loc_id),
  FOREIGN KEY (request_id) REFERENCES request (request_id),
  FOREIGN KEY (loc_id) REFERENCES locations (loc_id)
);

This will pay off big time in a long run enabling you to maintain and query your data normally.

Your query then may look like

SELECT *
  FROM request_location rl JOIN request r 
    ON rl.request_id = r.request_id JOIN locations l
    ON rl.loc_id = l.loc_id
 WHERE l.loc_name = 'mordor'

or even

SELECT rl.request_id
  FROM request_location rl JOIN locations l
    ON rl.loc_id = l.loc_id
 WHERE l.loc_name = 'mordor';

if you need to return only request_id

Here is SQLFiddle demo

silly · Accepted Answer · 2014-01-29 07:00:02Z

1

solution if locations is a varchar field!

join your tables with a like string (concat your r.locations for a starting and ending comma)

code is untested:

SELECT
    r.request_id
FROM location l
INNER JOIN request r
    ON CONCAT(',', r.locations, ',') LIKE CONCAT('%,',l.loc_id,',%')
WHERE l.loc_name = 'mordor'

answered Jan 29, 2014 at 7:00

silly

7,8972 gold badges27 silver badges37 bronze badges

Comments

Vignesh Kumar A · Accepted Answer · 2014-01-29 07:06:37Z

0

Try This

SELECT  *
FROM    request 
        CROSS JOIN
        (
        SELECT  1 AS loc
        UNION ALL
        SELECT  2 AS loc
        UNION ALL
        SELECT  3 AS loc
        UNION ALL
        SELECT  4 AS loc
        UNION ALL
        SELECT  5 AS loc
        ) q
JOIN    Location
ON      loc_id  = CAST(NULLIF(SUBSTRING_INDEX(locations, ',', -loc), SUBSTRING_INDEX(locations, ',', 1 - loc)) AS UNSIGNED)

answered Jan 29, 2014 at 7:06

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Collectives™ on Stack Overflow

Replace values in an sql query according to results of a nested query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related