How do I do that? [modified]

[kripa21]

I have car entry screen, in which user can insert a car details such as number, color, model etc. & amenities (select multiple for 1 car). Now search screen, when user selects aminities from list., I want to show only those cars who have all selected amminities.

modified on Thursday, June 18, 2009 6:25 AM

[Colin Angus Mackay]

kripa ostwal wrote:

I have car entry screen, in which user can insert a car details such as number, color, model etc. & amenities (select multiple for 1 car). Now search screen, when user selects aminities from list., I want to show only those cars who have all selected amminities.

Assuming your question is: How do I do that? You are going to have to show us the data model. We cannot help you construct a query without knowing what the data actually looks like (what the user can do on the front end isn't so useful as it may not match the data model)

[kripa21]

I have database with carMaster,AmenityMST & carAmm. CarMST carCode carNumber color 1 MH12-KO1212 blue 2 MH42-jk1234 red AmenityMST ammCode ammName 1 mobile charger 2 video coach 3 AC carAmm carCode ammCode 1 1 1 3 2 2 2 3 Now I want, when user search car with 'video coach' & 'AC', he should get only 1 car i.e. 2 I used "select carCode from carAmm where ammCode in (2,3)" but it returns me both cars, even car have not 'video coach'

[Colin Angus Mackay]

kripa ostwal wrote:

where ammCode in (2,3)

That is because you are asking for any cars with ammCode of 2 OR 3. Try a join instead between two result sets. First set: SELECT carCode from carAmm WHERE ammCode = 2 Second Set: SELECT carCode from carAmm WHERE ammCode = 3 Something like this:

SELECT c1.carCode
FROM carAmm AS c1
INNER JOIN carAmm AS c2 ON c1.carCode = c2.carCode
WHERE
c1.ammCode = 2
AND
c2.ammCode = 3

[kripa21]

s, bt i wrote store procedure for it coz there will be n number of aminities in future for search criteria. & I passed a string as '2,3' Can I split it in store procedure & create joins? or any other way to do this?

[Colin Angus Mackay]

kripa ostwal wrote:

s,

:confused:

kripa ostwal wrote:

I passed a string as '2,3'

If you are using SQL Server 2008 you can pass tables. That way you have no crazy string manipulation to get the data you need.

kripa ostwal wrote:

Can I split it in store procedure & create joins?

Yes, but the splitting operation is a PITA.

kripa ostwal wrote:

or any other way to do this?

You must already be using dynamic SQL, so just variations on a theme (unless you are using SQL Server 2008)

[kripa21]

i m using sql server 2000 with asp.net