Repeat AND statement, but remember first result?

[Steve Reshat]

Hi Everyone,

I am having a little trouble with an SQL query, let me try to explain.



This is the query that I am running:

select * from CUSTOMER where CARD in (select CARD from CREDIT_CARD where CARD_TYPE='A')

now this query works fine and returns the Cards I want, but I want to search for 2 or more different Cards on the same customer, but of the SAME card type. The problem is that I want to run the same query again with an AND statement (to look for another different CARD to go with the first one), but I want it to remember the output from the first query, so that it doesn’t use that to satisfy the query.



So, at the moment it would read:

select * from CUSTOMER where CARD in (select CARD from CREDIT_CARD where CARD_TYPE='A')

 

AND 

 

CARD in (select CARD from CREDIT_CARD where CARD_TYPE='A')

But the problem with the above query is that it doesn’t remember the output from the first part, so its basically just running the same query twice.



Is there anyway to remember the output from the first query, so that it doesn’t carry over to the second query?

Thanks alot of looking

[Oralloy]

That's not a clear question.

It looks like your customer table has one entry per customer per card. So a customer who has two cards of type 'A' already shows up twice.

Unless I'm really confused, your query should work just fine without any modification.

Or, am I missing the point?

[amitpatel66]

[code=oracle]
select * from CUSTOMER where CARD in (select CARD from CREDIT_CARD where CARD_TYPE='A')
[/code]


Your select query as above will give you all the customers and the corresponding card they are holding (1 or more) where card_type = 'A'.

So, say in case a customer has three different cards (x,y,z) of card type (A). Then your select query will show three records for a particular customer with card as (X,Y,Z).
I think that is what you are looking for.
Please post back with more insight if your question is not answered.