join within the same table

[vcllvc]

how do I do a join query within a same table?
I have a customer table which has "custID", "category" and "price" fields.
I need to query the table to return two different category's price base on the same custID.

I did a inner join but if I have 4 custid, it return 16 rows to me.

any thought, please.

 

[gmmastros]

Are you looking for something like this...

Select A.CustID, A.Price As Price1, B.Price As Price2
From   TableName As A
       Inner Join TableName As B
         On A.CustId = B.CustID
Where  A.Category = 'A Value'
       And B.Category = 'B Value'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vcllvc]

yes, something like this. This is what I have:

select a.custID, a.price, b.custID, b.price,
from
tblCustDetail as a
inner join
tblCustDetail as b
on
a.custID = b.custID
where a.category='cat1' and a.custID='12345'
and b.category='cat2' and b.custID='12345'

but for custID:12345, I should have 4 rows but after the query I got 4x4 rows which is 16rows. Its result look like a left outer join. I don't know why.

anyone can help, please.

 

[gmmastros]

That query should work. Is there more to it. I notice there is a comma after b.price.

If not, double and triple check your data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

often if I get unexpected results, I pull the data from all the fields in the joined tables just to see how they are relating (for set of qestionable records of course not the whole query against everything, that could take forever!)

Usually looking at the data that way I can see why my query is going wrong.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief