mysql multiple row values in where condition on a single table

Question

I had unique requirement. I Don't know it's my table structure problem or some thing else, but I need a solution for my output either structure change or query change.

I had a table which are containing a dynamic values of two fields of a single user. A single user can have multiple records of that types.

Example tables below

Table Name: tbl_parameters

parameter_id         parameter_name
1                    Current Location
2                    Booked Location
3                    Room Type

Table Name: tbl_paramter_data

id(auto_increment)    user_id   parameter_id       parameter_value 
1                        10     1                  Hyderabad
2                        10     2                  Delhi
3                        10     3                  Deluxe Room
4                        11     1                  Hyderabad
5                        11     2                  Bombay
6                        11     3                  A/C Room
7                        12     1                  Delhi
8                        12     2                  Bombay
9                        12     3                  Deluxe Room
10                       13     1                  Hyderabad
11                       13     2                  Delhi
12                       13     3                  Single Room

The above is my two tables. Its a Hotel Booking table. A user can book a hotel room any where from India. So, I stored in 2 different table. 1st table will contain what type of parameters I need to store with a parameter_id. The 2nd table contain the parameters values of each user From Where they are booked, which room they booked and remaining data. there are some other parameters also. **The No of parameters are dynamic. There are not a fixed no of parameters, that's the reason I maintained a separate table as tbl_parameters.

My Question is How can I get the data of

Who are the users Booked the Rooms from "Hyderabad" in "Bombay" Hotel?
Who are the users Booked the "Deluxe Rooms" from "Delhi" in "Bombay" Hotel?

How do I write a query to fetch the above data from the existing table(tbl_parameter_data)?

Or Shall I need to change the table structure or the way we are storing?

What you did there is called `EAV` - it stands for Entity-Attribute-Value. It's also an anti-pattern for *relational* data because it's difficult to query *relations*. On the other hand, it's easy to store an arbitrary number of attributes without altering tables. Displaying `EAV` based data in a "proper" table is called pivoting. However, as the data grows, the performance will deteriorate - you have to pivot first, then query. The solution to your problem is hybrid approach and using multiple queries instead of one. You need dynamic data, you also need to query it. — N.B., Apr 29 '15 at 11:28
This is a very common table structure, it's hardly a "unique requirement". — Barmar, Apr 29 '15 at 11:29
We cannot help you properly unless you provide the code, the script that you have tried so far. — Saiyan Prince, Apr 29 '15 at 11:39
Below is the query I used. But producing wrong results. SELECT user_id, COUNT(parameter_id) as cnt FROM tbl_parameter_data WHERE parameter_value IN('Hyderabd', 'Bombay') GROUP BY user_id HAVING cnt=2 — Sudhakar Reddy, Apr 29 '15 at 11:47

Ramon · Answer 1 · 2015-04-29T11:55:49.263

0

I think this will do the trick. (not tested yet) However all depends on how complex your query will get (each paramater needs new joins)

SELECT user_id
from tbl_parameter_data p1 JOIN 
     tbl_parameters pa1 ON p1.parameter_id = pa1.parameter_id
JOIN (tbl_parameter_data p2 JOIN 
     tbl_parameters pa2 ON p2.parameter_id = pa2.parameter_id) 
ON p1.user_id=p2.user_id
WHERE pa1.parameter_name = "Current Location" 
  AND p1.parameter_value = "Hydrabad" 
  AND pa2.parameter_name = "Booked Location" 
  AND p2.parameter_value = "Bombay"

old version:

SELECT user_id
from tbl_parameter_data p1 JOIN 
JOIN tbl_parameter_data p2 ON p1.user_id=p2.user_id
WHERE p1.parameter_id = 1 
   AND p1.parameter_value = "Hydrabad" 
   AND p2.parameter_id = 2 
   AND p2.parameter_value = "Bombay"

edited Apr 29 '15 at 11:55

answered Apr 29 '15 at 11:29

Ramon

1
3

You should join with `tbl_parameters` rather than hard-coding the parameter IDs. – Barmar Apr 29 '15 at 11:31
All depends on the logic behind, if on a website with for example selectboxes, I rather return the id's and use those in the query. But with join should also be possible – Ramon Apr 29 '15 at 11:34
He says in the question _there are not a fixed number of parameters, that's the reason I maintained a separate table as tbl_parameters_. – Barmar Apr 29 '15 at 11:36
Thanks guys for quick response. How can I do tbl_parameters joining Barmar? can you give me an example plz – Sudhakar Reddy Apr 29 '15 at 11:38

mysql multiple row values in where condition on a single table

1 Answers1