Database

15-Jun-09 21:37

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_Biswas17-Jun-09 20:47

17-Jun-09 20:47

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.

Smile | :)

Niladri Biswas

kripa2117-Jun-09 22:25

Re: advance search query result? [modified]

17-Jun-09 22:25

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_Biswas25-Jun-09 3:02

25-Jun-09 3:02

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.
Smile | :)

Niladri Biswas

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

kripa2125-Jun-09 22:41

Re: advance search query result? [modified]

25-Jun-09 22:41

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_Biswas27-Jun-09 19:11

27-Jun-09 19:11

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.
Smile | :)

Niladri Biswas

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

kripa2128-Jun-09 20:39

28-Jun-09 20:39

Thanx very very much
Really It's working

Niladri_Biswas28-Jun-09 23:24

How to Clear record very fastly ( use alitle script ) in SQL server 2005?

28-Jun-09 23:24

Niladri Biswas

LTMKH15-Jun-09 19:34

LTMKH

15-Jun-09 19:34

when i want to Clear my record in SQL server 2005 i used this code:
USE [Test]
GO
DELETE FROM TblAuditTrial;
DELETE FROM TblAuditTrialGroup;
DELETE FROM TblGrossProfit
DELETE FROM TblMenu
DELETE FROM TblMenuCategory
DELETE FROM TblMenuSubCategory
DELETE FROM TblOrderDetail
DELETE FROM TblOrderStatus
DELETE FROM TblPayment
DELETE FROM TblPaymentDetail
DELETE FROM dbo.TblProject
DELETE FROM dbo.TblRefund
DELETE FROM dbo.TblReport
DELETE FROM dbo.TblReportGroup
DELETE FROM dbo.TblStockOutGross
DELETE FROM dbo.TblUnit
DELETE FROM dbo.TblUser
DELETE FROM dbo.TblUserGroup
DELETE FROM dbo.TblUserGroupPermission
DELETE FROM dbo.TblUtilities
DELETE FROM dbo.TblVoidOrder
DELETE FROM dbo.TblVoidStockIn
DELETE FROM dbo.TblVoidStockOut
DELETE FROM dbo.TlkpNext
DELETE FROM dbo.TlkpPermissionCategory
DELETE FROM dbo.TrelBranchUser
DELETE FROM dbo.TlkpPermission
Go

It's is not easy for me, cos i must drop down alot ..
Can any one give me some easy way? i'm a new Learner, thanks..

[Message Deleted]

kripa2115-Jun-09 22:59

Re: How to Clear record very fastly ( use alitle script ) in SQL server 2005?

15-Jun-09 22:59

[Message Deleted]

LTMKH15-Jun-09 23:57

LTMKH

15-Jun-09 23:57

I'm very thank...but I don't know clearly...Can you give me some sample?

Re: How to Clear record very fastly ( use alitle script ) in SQL server 2005?

Isaac Gordon15-Jun-09 23:45

Isaac Gordon

15-Jun-09 23:45

use this
select distinct 'delete from '+ Table_Name from information_Schema.columns

or
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Re: How to Clear record very fastly ( use alitle script ) in SQL server 2005?

LTMKH16-Jun-09 0:19

LTMKH

16-Jun-09 0:19

Thank you so much ...Now I'm succeed in this code:
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Can you give me some more sample about the first code ? I'm still error with this:
select distinct 'delete from '+ Table_Name from information_Schema.columns

Re: How to Clear record very fastly ( use alitle script ) in SQL server 2005?

Vimalsoft(Pty) Ltd16-Jun-09 22:47

Vimalsoft(Pty) Ltd

16-Jun-09 22:47

Good Morning Loeurt

Truncate is more Faster than Delete

truncate table  dbo.TlkpPermission

Use Truncate not Delete

Hope it Helps

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: How to Clear record very fastly ( use alitle script ) in SQL server 2005?

Niladri_Biswas25-Jun-09 5:32