How to select the row if one column having text field
-
I have a column called bookedhalls, the value will be stored as Balaji-A,Balaji-C, i written select query as follows Select * from function where bookedhalls like '%Balaji-A,Balaji-C,% by using this i will get the row. Suppose if i give '%Balaji-C,Balaji-A,%' i cannot get the row. How to get that row by using Balaji-C,Balaji-A. Anybody knows please reply.
modified on Friday, September 24, 2010 6:17 AM
-
I have a column called bookedhalls, the value will be stored as Balaji-A,Balaji-C, i written select query as follows Select * from function where bookedhalls like '%Balaji-A,Balaji-C,% by using this i will get the row. Suppose if i give '%Balaji-C,Balaji-A,%' i cannot get the row. How to get that row by using Balaji-C,Balaji-A. Anybody knows please reply.
modified on Friday, September 24, 2010 6:17 AM
Your data model is fundamentally wrong, you should almost never be storing multiple values in one column with some delimiter (such as a comma). This is especially true if you need to query based on one of those multiple values. bookedhalls should be a separate table, referenced to the rest of that row using foreign key relationships, this would allow you to select based upon this join.
-
Your data model is fundamentally wrong, you should almost never be storing multiple values in one column with some delimiter (such as a comma). This is especially true if you need to query based on one of those multiple values. bookedhalls should be a separate table, referenced to the rest of that row using foreign key relationships, this would allow you to select based upon this join.
Actually i created table caolled function in that fun_start_date and fun_end_date and booked_halls collms is there. Suppose u conside one date it is booked for 2 floors (balaji-A, Balaji-B). User will select in forms. so i can write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-A,BALAJI-B,%' i will get the row. If there is no row means he can go for inserting. Here is Ok. Suppose user if select like first Balaji_B then Balaji-A in coding i will get string as a BALAJI-B,BALAJI-A. if i write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-B,BALAJI-A,%' i will not get the row . I should get that row. So nobody should not book for that floor. How to do this.
-
Actually i created table caolled function in that fun_start_date and fun_end_date and booked_halls collms is there. Suppose u conside one date it is booked for 2 floors (balaji-A, Balaji-B). User will select in forms. so i can write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-A,BALAJI-B,%' i will get the row. If there is no row means he can go for inserting. Here is Ok. Suppose user if select like first Balaji_B then Balaji-A in coding i will get string as a BALAJI-B,BALAJI-A. if i write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-B,BALAJI-A,%' i will not get the row . I should get that row. So nobody should not book for that floor. How to do this.
-
Actually i created table caolled function in that fun_start_date and fun_end_date and booked_halls collms is there. Suppose u conside one date it is booked for 2 floors (balaji-A, Balaji-B). User will select in forms. so i can write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-A,BALAJI-B,%' i will get the row. If there is no row means he can go for inserting. Here is Ok. Suppose user if select like first Balaji_B then Balaji-A in coding i will get string as a BALAJI-B,BALAJI-A. if i write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-B,BALAJI-A,%' i will not get the row . I should get that row. So nobody should not book for that floor. How to do this.
As pointed out already, your table structure is not a good one, that is why you are finding it tricky to write this query. You should read up about database normalization. If you are really stuck with this data structure and cannot change it then you will need to do something like:
select * from function where booked_halls like '%BALAJI_A%' and booked_halls like '%BALAJI_B%'
But really you should change the data model because what you have at the moment is wrong.
-
Actually i created table caolled function in that fun_start_date and fun_end_date and booked_halls collms is there. Suppose u conside one date it is booked for 2 floors (balaji-A, Balaji-B). User will select in forms. so i can write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-A,BALAJI-B,%' i will get the row. If there is no row means he can go for inserting. Here is Ok. Suppose user if select like first Balaji_B then Balaji-A in coding i will get string as a BALAJI-B,BALAJI-A. if i write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-B,BALAJI-A,%' i will not get the row . I should get that row. So nobody should not book for that floor. How to do this.
Do NOT ignore the advice you have been given. Just because David has given you a work around FIX YOUR DATA STRUCTURE. If you do not do this NOW you will be back here continiously asking how to get at your data.
Never underestimate the power of human stupidity RAH