get records from mysql using and

Question

1.00/5 (1 vote)

See more:

In mysql i have records as

hotelid amenitieid
31534 719
31534 665
31534 603
31534 663
31534 1993
31534 638
31124 719
14744 719
25363 665
12523 603
17458 665

and want hotelid where amenitieid=719 and amenitieid=603 (both are there)
from above database field values

what is mysql query for this

Posted 1-Jan-14 22:20pm

maulikshah1990

Add a Solution

Comments

thatraja 2-Jan-14 4:23am

what have you tried?

maulikshah1990 2-Jan-14 4:24am

I tried as

SELECT *
FROM `amenities`
where amenitieid = '719' AND amenitieid = '603'

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-01-01T22:23:00

Solution 1

Try:

SQL

SELECT * FROM MyTable WHERE amenitieid=719 OR amenitieid=603

[edit]Typo - AND for OR[/edit]

Posted 1-Jan-14 22:23pm

OriginalGriff

Updated 1-Jan-14 22:24pm

v2

Comments

maulikshah1990 2-Jan-14 4:24am

i tried ..and but not working

OriginalGriff 2-Jan-14 4:53am

Show exactly what you tried - copy and paste from your source the relevent fragment with a couple of lines either side for context.

maulikshah1990 2-Jan-14 4:54am

SELECT *
FROM `amenities`
where amenitieid = '719' AND amenitieid = '603'

OriginalGriff 2-Jan-14 5:10am

First remove the quotes around the table name - you don't need them.
Then change AND to OR.
AND requires both conditions to be true, and since they are both working on the same column, it can't be two different values at the same time. OR requires one or the other condition to be true, so it will returns rows where the value matches either value

maulikshah1990 2-Jan-14 5:15am

hotelid amenitieid
31534 719
31534 665
31534 603
31534 663
31534 1993
31534 638
31124 719
14744 719
25363 665
12523 603
17458 665

In the above , i want
hotelid where amenitieid = '719' and hotelid='665' ..and there is record hotelid with both amenities like 31534 ,

pls give mysql query for this..

OriginalGriff 2-Jan-14 5:24am

Ah! That's a different matter!
Try this:
SELECT a.HotelId FROM Amenities a
JOIN Amenities b
ON a.HotelId = b.HotelId AND a.Amenitieid=719 AND b.Amenitieid=603

maulikshah1990 2-Jan-14 5:28am

and what if more hotelid are there and not only 2 as example above ..

OriginalGriff 2-Jan-14 6:16am

The query as given will return all the hotelid values which have both: if you need three amenitieid values to be the same then you need to add another JOIN and and so forth.

maulikshah1990 2-Jan-14 6:20am

but that will take much time to run query everytime for each new added amenitieid dynamically

OriginalGriff 2-Jan-14 6:42am

I haven't tried this in MySQL, but it works in SQL Server:
SELECT HotelId FROM Amenities
WHERE Amenitieid=719 OR Amenitieid=603
GROUP BY HotelId
HAVING COUNT(*) = 2
It should work in MySQL as well...
Obviously you'd have to add OR clauses, and increase the COUNT limit.

CHill60 2-Jan-14 9:13am

5'd ... for patience

Bernhard Hiller · Answer 2 · 2014-01-02T03:10:00

Solution 2

You could add a de-normalized table, having the hotelid and a boolean column for each amenityid, and perform the search in that table.
But then make sure that that table is updated automatically when the data in the original tables change (e.g. using triggers).

Posted 2-Jan-14 3:10am

Bernhard Hiller

Comments

maulikshah1990 3-Jan-14 5:40am

give example for above records i have posted..

get records from mysql using and

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

get records from mysql using and

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us