advance search query result?

[kripa21]

Respected, Greetings I have problem to get search result query result I have database with carMaster,AmenityMST & carAmm which is transactional or derived table. CarMST carCode carNumber 1 MH12-KO1212 2 MH42-jk1234 AmenityMST ammCode ammName 1 mobile charger 2 video coach 3 AC carAmm carCode ammCode 1 1 1 3 2 1 2 2 Now I want, when user check the amenities to search 1 & 2 that time, he should get only 1 result i.e. 2 I used "select carCode from carAmm wjhere ammCode in (1,2)" this sqlquery but it returns me both cars, even car have not 'video coach' Please help me to solve this problem

[Niladri_Biswas]

Your question is very unclear... Explain it properly otherwise, nothing cannot be figured out. One advice, when asking question , frame it in such a way that anybody can understand what you are actually meaning. Remember, it is not you who will be reading the question, rather others who can understand by looking into the problem statement. there are no other 3rd party medium for this communication. :)

Niladri Biswas

[kripa21]

thanx for advice. 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.

[Niladri_Biswas]

If I have clearly understood your problem, you want to display only those cars which have got all the amenities. I created 3 tables as what you specified. Here I am depicting the junction table( tblCarAmnesty ) which is having 2 columns viz. CarCode & AmmCode [ both of type int ] The values are as under CarCode AmmCode 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 4 1 5 2 5 3 6 1 6 2 6 3 As per your requirement, Only car code 1,2 and 6 will only appear. Here is the query

**SELECT C.CARCODE
FROM (
SELECT A.CARCODE,A.AMMCODE
FROM (SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty
WHERE AMMCODE = 1) A

		INNER JOIN 

		(SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty 
		WHERE AMMCODE = 2) B

		ON A.CARCODE = B.CARCODE) C

	INNER JOIN 

(SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty
WHERE AMMCODE = 3) D

ON C.CARCODE = D.CARCODE**

And here is the output CARCODE 1 2 6 Let me know in case of any concerns Hope this helps. :)

Niladri Biswas

modified on Friday, June 26, 2009 12:27 AM

[kripa21]

Respected Niladri, Thanx for the reply. Yes it is working correctly. But, I have N' number of amminities. User can select 1 or more than aminity for search a car, & result should be, all the cars, who have 'ALL SELECTED' amminities only Means, as per your dataentry, If user is searching car, which having amminities 2 & 3, he should get result as '1,2,5,6' If user is searching car, which having amminities 1 & 2, he should get result as '1,2,3,6' If user is searching car, which having amminities 1,2,3, he should get result as '1,2,6' Hope now I can clearely describe the problem

[Niladri_Biswas]

Hi Kripa, sorry for answering late. I was extremely busy with my project work. That's the only reason I answered late to your first question. Anyway, here is the answer For Case I & II If user is searching car, which having amminities 2 & 3, he should get result as '1,2,5,6' If user is searching car, which having amminities 1 & 2, he should get result as '1,2,3,6' Solution 1:

SELECT CarCode
FROM tblCarAmnesty
WHERE AmmCode in (1,2)
GROUP BY CarCode
HAVING COUNT(*) = 2

Solution 2

SELECT A.CarCode
FROM tblCarAmnesty A, tblCarAmnesty B
WHERE A.CarCode = B.CarCode
AND A.AmmCode = 1
AND B.AmmCode = 2

-------------------------------------------------------------------------- For Case III If user is searching car, which having amminities 1,2,3, he should get result as '1,2,6' Solution 1:

SELECT CarCode
FROM tblCarAmnesty
WHERE AmmCode in (1,2,3)
GROUP BY CarCode
HAVING COUNT(*) = 3

Solution 2

SELECT A.CarCode
FROM tblCarAmnesty A, tblCarAmnesty B,tblCarAmnesty C
WHERE
A.CarCode = B.CarCode
AND B.CarCode = C.CarCode

	AND A.AmmCode = 1
	AND B.AmmCode = 2
	AND C.AmmCode = 3

N.B.~ As you can generalized that the count must match the number of AmmCode you are looking for for the Solution I category. So what I suggest is that, make the Count of Ammcode as dynamic(i.e. a variable) as well as the parameter in the IN clause. For Solution II category, I suggest better you make a dynamic SQL Query. Hope this helps. :)

Niladri Biswas

modified on Sunday, June 28, 2009 1:19 AM

[kripa21]

Thanx very very much Really It's working

[Niladri_Biswas]

:)

Niladri Biswas