A tricky SELECT statement 2

[JGirl]

Help!

I dont know if its just been a long day and my brain is fried, or if im trying to do something difficult....

I have a table with customer purchases containing the following columns:
- cust_no
- item_code

A sample regular item code would be like 'ABC', and the other version of this product would have a code like 'XABC' (The prefix is always an X). I need to do a select on the table that will bring back customers and their details that have both versions only (ie. if a customer only has ABC or only has XABC, I dont want ANY of their details for that particular product - ie. I need to check for each customer and each product if the customer has product ... AND product X... ). Is this at all possible?

 

[tlbroadbent]

Select *
From YourTable t
Where (Exists
(Select * From YourTable
Where cust_no = t.cust_no
And item_code = t.item_code)
And Exists
(Select * From YourTable
Where cust_no = t.cust_no
And item_code = 'X' + t.item_code))
Or (Exists
(Select * From YourTable
Where cust_no = t.cust_no
And item_code = t.item_code)
And Exists
(Select * From YourTable
Where cust_no = t.cust_no
And 'X' + item_code = t.item_code)) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[dnoeth]

sel *
from yourtable t1
join yourtable t2
on t1.cust_no = t2.cust_no
and t1.item_code = 'x' || t2.item_code

Dieter

 

[JGirl]

Thankyou both for your replies....I ended up using your SQL tlbroadbent, as I wanted a single list of all customers and product versions, rather than a list containing the two versions of the product in one row. (I should have been more specific!)

I still need one more thing....I want to sort the products in the resulting list according to the common code, ie, if a customer has 4 products: ABC, XABC, XABB and ABB, then I want to sort these as if the 'X' was not at the beginning (but I still need the X to appear in the output display), so they will appear in the following order for the customer:
ABB
XABB
ABC
XABC,

rather than in this order:
ABB
ABC
XABB
XABC

Is this possible?

Cheers
J

 

[tlbroadbent]

If your RDBMS suppports the CASE function, you can use something like the following.

Order By
Case When left(Item_code ,1) = 'X'
Then substring(Item_code,2,len(Item_code)-1)
Else Item_code
End Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.