Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

What is wrong with this query?

Status
Not open for further replies.
Davidnyh

Davidnyh

Technical User
Jun 3, 2003
13
0
0
BE
What is wrong with this query?
This query only need to select the record where the id is 23.
The problem occurs with this line: AND B.CARDNUMBER <> CARDNUMBER.
But it doesn't give me a record at all.
If I leave this code out of the query it gives me the records 23 and 29.
But I only need the records where the cardnumbers are not equal.

SELECT CARDNUMBER
FROM CUSTOMER_HISTORY
WHERE CAST(DATE_CREATED AS DATE) = (SELECT MAX(CAST(A.DATE_CREATED AS DATE))
FROM CUSTOMER_HISTORY A
WHERE A.CUSTOMER_HIST_ID = CUSTOMER_HIST_ID
AND CAST(A.DATE_CREATED AS DATE) <'2007-08-27'
)
AND CUSTOMER_HIST_ID IN (SELECT B.CUSTOMER_HIST_ID
FROM CUSTOMER_HIST B
WHERE CAST(B.DATE_CREATED AS DATE) = '2007-08-27'
AND B.CARDNUMBER IS NOT NULL
AND B.CARDNUMBER <> CARDNUMBER
)

ID, CUSTOMER_HIST_ID, CARDNUMBER, DATE_CREATED
23, 41, 3, 26/08/2007 11:58:05
24, 41, 4, 27/08/2007 11:58:10
25, 42, NULL, 27/08/2007 11:58:10
26, 42, 5, 27/08/2007 11:58:16
27, 43, NULL, 27/08/2007 11:58:16
28, 43, 6, 27/08/2007 11:58:20
29, 44, 6, 26/08/2007 11:58:20
30, 44, 6, 27/08/2007 11:58:42

Sorry for my bad English.

Thanks in advance

Nyh
 
Sort by date Sort by votes
The question should be 'what is wrong with this subject'? (your post's' subject). People tend to skip over posts with vague titles like yours.

Other posting tips are here: faq222-2244


As for your question, I think that what you want is to change the offending line to this:

Code: 
AND B.CARDNUMBER <> [b]A.[/b]CARDNUMBER

You can also get rid of the B.CARDNUMBER IS NOT NULL part because the other where clause will automagically exclude nulls for you.

If this is not what you are after, please post the result you would like to see from the sample data you posted.

Hope this helps,

Alex


[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I will use a better subjet next time.

But I can't use A.CARDNUMBER because that is from another subselect.

And if I change the query to this:

SELECT CARDNUMBER
FROM CUSTOMER_HISTORY
WHERE CAST(DATE_CREATED AS DATE) = (SELECT MAX(CAST(A.DATE_CREATED AS DATE))
FROM CUSTOMER_HISTORY A
WHERE A.CUSTOMER_HIST_ID = CUSTOMER_HIST_ID
AND CAST(A.DATE_CREATED AS DATE) <'2007-08-27'
AND A.CUSTOMER_HIST_ID IN (SELECT B.CUSTOMER_HIST_ID
FROM CUSTOMER_HIST B
WHERE CAST(B.DATE_CREATED AS DATE) = '2007-08-27'
AND B.CARDNUMBER IS NOT NULL
AND B.CARDNUMBER <> A.CARDNUMBER
)

It does the same as the query above.

Nyh
 
Upvote 0 Downvote
So, what CARDNUMBER do you want to compare B.CARDNUMBER to?

If what you posted is not your entire query, I suggest you post that.

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
This is my entire query.

In the first subselect I select all the records where the date are less than the given date.

In the second subselect I select all the records where the cardnumber are not null and the date is equal to the given date. And the cardnumber is different than the cardnumber from the first subselect.

So I need all the records where the date is less than the given date and the cardnumber is different than the cardnumber from the records where the date is equal to the given date and where the customer history id is equal to the records where the date is less than the given date.

Nyh
 
Upvote 0 Downvote
What is the data contained in CUSTOMER_HIST?

Could this just be a typo, and its' supposed to be CUSTOMER_HISTORY?

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
It is a typo. It should be CUSTOMER_HISTORY.

But I typed it wrong here not in my application.

So the problem still exist.

Nyh
 
Upvote 0 Downvote
Your subqueries seem to be mutually exclusive (at least in the sample data). Why don't we go back to square one with the business requirements for this query (in english).

Either of these will return your desired result (card number 3) but I have a feeling that your query has already been kind of mangled. Knowing the requirements for the data set returned will help us figure out what your query should look like.

Code: 
SELECT Z.CARDNUMBER
FROM @CUSTOMER_HISTORY Z
WHERE DATE_CREATED = (SELECT MAX(A.DATE_CREATED)
FROM @CUSTOMER_HISTORY A
WHERE A.CUSTOMER_HIST_ID = CUSTOMER_HIST_ID
AND A.DATE_CREATED <'2007-08-27'
)
AND CUSTOMER_HIST_ID IN (SELECT B.CUSTOMER_HIST_ID 
FROM @CUSTOMER_HISTORY B
WHERE B.DATE_CREATED < '2007-08-27'
)

Code: 
SELECT Z.CARDNUMBER
FROM @CUSTOMER_HISTORY Z
WHERE DATE_CREATED = (SELECT MAX(A.DATE_CREATED)
FROM @CUSTOMER_HISTORY A
WHERE A.CUSTOMER_HIST_ID = CUSTOMER_HIST_ID
AND A.DATE_CREATED <'2007-08-27'
)

I think you had a reason for adding the second subquery, but I can't figure out what it is. As I said, business requirements will help us figure this out.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Both of these queries give me the records with the ID's 23 and 29.

I don't need the record with the ID 29 because the cardnumber is equal to the cardnumber from the record with the ID 30 and where the date is equal to the given date.

Nyh
 
Upvote 0 Downvote
Sorry about that that was a typo when I put in the sample data :-(

Try this query, basically we want to get a list of CARDNUMBER/CUSTOMER_HIST_ID combinations (for the given date) from the table and left joined to this list. We'll then take only the records without a match (on CARDNUMBER & CUSTOMER_HIST_ID) in my result set, and excluded teh NULL card numbers.

Code: 
[COLOR=blue]select[/color] x.CARDNUMBER
[COLOR=blue]from[/color] CUSTOMER_HISTORY x
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] (
	[COLOR=blue]select[/color] CARDNUMBER, CUSTOMER_HIST_ID
	[COLOR=blue]from[/color] CUSTOMER_HISTORY 
	[COLOR=blue]where[/color] DATE_CREATED = [COLOR=red]'20070827'[/color]
	and CARDNUMBER [COLOR=blue]is[/color] not null
) y
[COLOR=blue]on[/color] x.CUSTOMER_HIST_ID = y.CUSTOMER_HIST_ID
and x.CARDNUMBER = y.CARDNUMBER
[COLOR=blue]where[/color] y.CUSTOMER_HIST_ID [COLOR=blue]is[/color] null
and x.CARDNUMBER [COLOR=blue]is[/color] not null

I am using MS SQL 2000 where you cannot cast to a DATE data type, so I can't help you with that part, but it should be easy enough on your platform.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
It works almost great.

But take for example this table:
26 43 5 25/08/2007 00:00:00
27 43 5 26/08/2007 00:00:00
28 43 6 27/08/2007 00:00:00

From these records I only the second one and not the first and the second.

So I need all the records where the date is less than the given date and the cardnumber is different than the cardnumber from the records where the date is equal to the given date.

Nyh
 
Upvote 0 Downvote
So I need all the records where the date is less than the given date and the cardnumber is different than the cardnumber from the records where the date is equal to the given date.
Click to expand...

According to this rule, you would want the first two and not the third. Do you mean the most recent record per CUSTOMER_HIST_ID / CARDNUMBER combination?

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I want to find records where the date is less than the given date.
But the date needs to be the highest than the other records.
Where the customer history id is equal to the customer history id where the date is equal to the given date.

For example:

If I give the date 27/08/2007 in. It needs to find the the third record. Than it needs to check if the customer history id is equal to the customer history id where the date is less than 27/08/2007. It will find two records. But I only need the most recent one. That is record two because the date is less than the given date but higher than the date 25/08/2007 from record one. If it finds one than I need to check if the cardnumbers are equal if it isn't equal I need to select it. In this example cardnumber from record two is not equal to the cardnumber from record three. So cardnumber 3 need to be listed.
 
Upvote 0 Downvote
Ah, Ok.

Try this:

Code: 
select x.CARDNUMBER
from CUSTOMER_HISTORY x
left join (
    select CARDNUMBER, CUSTOMER_HIST_ID
    from CUSTOMER_HISTORY 
    where DATE_CREATED = '20070827'
    and CARDNUMBER is not null
) y
on x.CUSTOMER_HIST_ID = y.CUSTOMER_HIST_ID
and x.CARDNUMBER = y.CARDNUMBER
where y.CUSTOMER_HIST_ID is null
and x.CARDNUMBER is not null
and DATE_CREATED = (select max(DATE_CREATED)
	from CUSTOMER_HISTORY
	where CARDNUMBER = x.CARDNUMBER
		and CUSTOMER_HIST_ID = x.CUSTOMER_HIST_ID
		and DATE_CREATED < '20070827')

If you only need one cardnumber per CUSTOMER_HIST_ID, you can get rid of this:
Code: 
 CARDNUMBER = x.CARDNUMBER
in the final subquery.

Another way you could do this is to simply put select distinct cardnumber, but using the subquery will leave your options open if my understanding is incorrect.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
That's it. It works the way I wanted.

Thank you very much. Sorry that my question was not very clear at the beginning.

Thanks again.

Nyh
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
76
mmiah1982
mmiah1982
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
64
carp
carp
fredong
  • Locked
  • Question
Update upper row data with next row data that was detached partially from import.
Replies
6
Views
90
carp
carp
mikrom
  • Locked
  • Question
from a table with symmetric relation select every pair only once
Replies
0
Views
47
mikrom
mikrom
manbob
  • Locked
  • Question
need a help with sub query getting group ereor, please correct
Replies
2
Views
66
carp
carp

Part and Inventory Search

Sponsor

Back
Top