-1

I have two table . traveller and city

city table :

City_id     City_name
  1           Delhi
  2           Mumbai
  3           Newyork
  4           Sydney

User table :

 traveller_name traveled_city
    harry             1,2
    peter             2,3,4
    john             2,4

How to fetch city traveled by traveller like this ?

  traveller_name       travelled_city
    harry               Delhi , Mumbai
Marriage
  • 3
  • 1
  • Is the value of city in user table comma separated? OR there will be multiple records for different travelled cities by a user? – Jinesh Shah Mar 14 '19 at 11:52
  • 2
    your data appears to be de-normalised. if you find yourself storing more than one item in a single field like you do in traveled_city then it's time for a re-design. You need a child table with a foreign key back to the user table. Maybe you need to study relational database design further before you continue with your project. – ADyson Mar 14 '19 at 11:54
  • this might help: https://stackoverflow.com/questions/39139393/mysql-split-and-join-the-values – apomene Mar 14 '19 at 12:01
  • 1
    I'm with ADyson here. This is a bad datamodel. You are not really using the relational database system, as you are supposed to do. Change your structure to `city(city_id, city_name)`, `user(user_id, user_name)`, `city_user(city_id, user_id)`. – Thorsten Kettner Mar 14 '19 at 12:05
  • @strawberry please give me link – Marriage Mar 14 '19 at 12:41
  • @Marriage look at the yellow box at the top of the page...the link is there already – ADyson Mar 14 '19 at 13:18

2 Answers2

2

Your database schema is not normalized. The first normal form tells that you need to have atomic values in the field. Also considering other normal forms, you need to have separate User table and UserCity table.

If you normalize your table, then your queries will become simpler.

User Table

--------+----------
| UserId UserName  |
--------+----------
|   1   | Harry   |
|   2   | Peter   |
-------------------

UserCity Table (It is Bridge table) 

--------+----------
| UserId CityId |
--------+----------
|   1   | 1     |
|   1   | 2     |
-------------------

Now, you can easily query 

SELECT u.UserName as traveller_name, group_concat(c.cityname,',') as  travelled_city
FROM User as u
JOIN UserCity as uc
ON u.UserId = uc.UserId
JOIN City as c
ON uc.CityId = c.CityId
GROUP BY u.UserName
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
-1

SELECT traveller.traveller_name, GROUP_CONCAT(city.City_name) FROM traveller INNER JOIN city ON city.City_id = traveller.traveled_city_id GROUP BY traveller.traveller_name

Annamaiah
  • 1
  • 1
  • `city.City_id = traveller.traveled_city` will never be true because traveled_city contains multiple IDs...OP's data is de-normalised. The DB design needs fixing before there is a neat solution to this. – ADyson Mar 14 '19 at 12:05
  • @ADyson yes.it giving only one city name – Marriage Mar 14 '19 at 12:27
  • that's why we used group_concat which gives comma separated strings – Annamaiah Mar 15 '19 at 04:12