self Join [Help Required]

Question

Post reply

self Join [Help Required]

arslan_a2z

Valued Member

Points: 59
More actions
October 11, 2011 at 5:17 am

#249659

Hi, i need help regarding self join... i have a table 'table1' which contains columns:
1) id--unique auto generated
2) resellerid--foreign key
3) book_code
4) price
5) discount
all fields type is numeric...i want to get all those book_code,price which have same price for all or specific resellersid... i tried this query but it gave me wrong results:
SELECT c1.book_code,c1.price FROM table1 as c1 INNER JOIN table1 as c2 ON c1.book_code=c2.book_code WHERE c1.price=c2.price
i will be much thankful if u can resolve this problem by doing it any way...

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think you want to enhance the join so items in the self join don't find themselves;

this is just slightly modified from yours:

SELECT c1.book_code,

c1.price

FROM table1 AS c1

INNER JOIN table1 AS c2

ON c1.book_code = c2.book_code

AND c1.id <> c2.id

WHERE c1.price = c2.price

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

arslan_a2z Valued Member Points: 59 More actions · Answer 2

thanx for reply...i tried it for two resellers but it didn't successful, i have two resellerid ('10','15') both hav a book_code='40100', for resellerid=10 its price is 100, for resellerid=15 its price is 0, it shouldn't come in result but its showing...as under

book_code price

30100 0

30200 0

40100 0

40100 100

1000100 85

1000200 180

2000100 45

2000200 85

moreover max book_code for any resellerid are 60, so result rows must be less than 60 for any two resellers, but it shows over 60....

Lowell SSC Guru Points: 323518 More actions · Answer 3

?

With mySampleData (id,book_code,price)

AS

(

SELECT 1,30100,0 UNION ALL

SELECT 2,30200,0 UNION ALL

SELECT 3,40100,0 UNION ALL

SELECT 4,40100,100 UNION ALL

SELECT 5,1000100,85 UNION ALL

SELECT 6,1000200,180 UNION ALL

SELECT 7,2000100,45 UNION ALL

SELECT 8,2000200,85

)

SELECT c1.book_code,

c1.price

FROM mySampleData AS c1

INNER JOIN mySampleData AS c2

ON c1.book_code = c2.book_code

AND c1.id <> c2.id

WHERE c1.price <> c2.price

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

VSP SSC-Addicted Points: 467 More actions · Answer 4

Try this one..

SELECT *

FROM table1 T1

INNER JOIN (SELECT price

FROM table1 T

GROUP BY price

HAVING count(*) > NO_OF_BOOKS_WITH_THE SAME_PRICE

) BP on T1.KEY = BP.KEY AND T1.price = BP.price

KEY is the key columns in table1, to improve the performance of the query. Assuming the price column is not part of the key.

use NO_OF_BOOKS_WITH_THE SAME_PRICE to specify the number of books that have the same price

For example, If you want to see atleast 2 books with the same price, replace

NO_OF_BOOKS_WITH_THE SAME_PRICE = 1

Use the following query if you want the books with the same price for a specific seller..

SELECT *

FROM table1 T1

INNER JOIN (SELECT price

FROM table1 T

GROUP BY price

HAVING count(*) > NO_OF_BOOKS_WITH_THE SAME_PRICE

) BP on T1.KEY = BP.KEY AND T1.price = BP.price

WHERE T1.reseller_id = BP.reseller_id

AND T1.reseller_id = 'NAME OF THE SELLER'

hope this helps

VSP SSC-Addicted Points: 467 More actions · Answer 5

small correction to my previous reponse. remove T1.reseller_id = BP.reseller_id from the join condition in the last query..

Sorry for the confusion..

Try this one..

SELECT *

FROM table1 T1

INNER JOIN (SELECT price

FROM table1 T

GROUP BY price

HAVING count(*) > NO_OF_BOOKS_WITH_THE SAME_PRICE

) BP on T1.KEY = BP.KEY AND T1.price = BP.price

KEY is the key columns in table1, to improve the performance of the query. Assuming the price column is not part of the key.

use NO_OF_BOOKS_WITH_THE SAME_PRICE to specify the number of books that have the same price

For example, If you want to see atleast 2 books with the same price, replace

NO_OF_BOOKS_WITH_THE SAME_PRICE = 1

Use the following query if you want the books with the same price for a specific seller..

SELECT *

FROM table1 T1

INNER JOIN (SELECT price

FROM table1 T

GROUP BY price

HAVING count(*) > NO_OF_BOOKS_WITH_THE SAME_PRICE

) BP on T1.KEY = BP.KEY AND T1.price = BP.price

WHERE T1.reseller_id = 'NAME OF THE SELLER'

hope this helps

Jason Tontz SSCommitted Points: 1957 More actions · Answer 6

arslantalib (10/11/2011)
thanx for reply...i tried it for two resellers but it didn't successful, i have two resellerid ('10','15') both hav a book_code='40100', for resellerid=10 its price is 100, for resellerid=15 its price is 0, it shouldn't come in result but its showing...as under
book_code price
30100 0
30200 0
40100 0
40100 100
1000100 85
1000200 180
2000100 45
2000200 85
moreover max book_code for any resellerid are 60, so result rows must be less than 60 for any two resellers, but it shows over 60....

Your query appears to be forcing the results to return WHERE THE PRICES ARE THE SAME (c1.price=c2.price)

I think you want :

SELECT c1.book_code,c1.price FROM table1 as c1 INNER JOIN table1 as c2 ON c1.book_code=c2.book_code WHERE C1.Resellerid IN ('15',10)

P.S.

Your query is also not pulling any info from "c2". You might question if you need a self join.

VSP SSC-Addicted Points: 467 More actions · Answer 7

I may have misunderstood your question.

Could you please post the following, if you can

1. Test data you used for testing

2. Actual Output from the query I sent you

3. Expected Output for the given test data.

karenoqfox Grasshopper Points: 17 More actions · Answer 8

Nice post!

Thnaks to sharing with us.

iphone 5 release date[/url]

arslan_a2z Valued Member Points: 59 More actions · Answer 9

thanks for your response, problem is still not solved with my actual data,

can you please give your email, so i can send you my actual data...

VSP (10/12/2011)
I may have misunderstood your question.
Could you please post the following, if you can
1. Test data you used for testing
2. Actual Output from the query I sent you
3. Expected Output for the given test data.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

arslantalib (10/27/2011)
thanks for your response, problem is still not solved with my actual data,
can you please give your email, so i can send you my actual data...
VSP (10/12/2011)
I may have misunderstood your question.
Could you please post the following, if you can
1. Test data you used for testing
2. Actual Output from the query I sent you
3. Expected Output for the given test data.

Please post sample data here, you don't need to post much - a dozen rows would be plenty. It would also help to provide the CREATE TABLE script.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

arslan_a2z Valued Member Points: 59 More actions · Answer 11

With mySampleData (id,resellerid,book_code,price,price_fixed,dis_perc,block_by)

AS

(

SELECT 1,44,20100,0,0,100,43 UNION ALL

SELECT 2,45,20100,0,0,100,0 UNION ALL

SELECT 3,46,20100,0,0,100,43 UNION ALL

SELECT 4,44,20200,0,0,100,43 UNION ALL

SELECT 5,45,20200,0,0,100,0 UNION ALL

SELECT 6,46,20200,0,0,100,43 UNION ALL

SELECT 7,44,30100,50,0,0,43 UNION ALL

SELECT 8,45,30100,50,0,0,43 UNION ALL

SELECT 9,46,30100,50,0,0,43 UNION ALL

SELECT 10,44,30200,0,0,100,43 UNION ALL

SELECT 11,45,30200,0,0,100,0 UNION ALL

SELECT 12,46,30200,75,0,0,43 UNION ALL

SELECT 13,44,40100,100,0,0,43 UNION ALL

SELECT 14,45,40100,0,0,100,0 UNION ALL

SELECT 15,46,40100,100,0,0,43 UNION ALL

SELECT 16,44,1000100,85,0,15,0 UNION ALL

SELECT 17,45,1000100,0,0,100,43 UNION ALL

SELECT 18,46,1000100,85,0,15,0 UNION ALL

SELECT 19,44,1000200,180,0,10,0 UNION ALL

SELECT 20,45,1000200,0,0,100,43 UNION ALL

SELECT 21,46,1000200,180,0,10,0 UNION ALL

SELECT 22,44,2000100,45,0,10,0 UNION ALL

SELECT 23,45,2000100,0,0,100,43 UNION ALL

SELECT 24,46,2000100,45,0,10,0 UNION ALL

SELECT 25,44,2000200,85,0,15,0 UNION ALL

SELECT 26,45,2000200,0,0,100,43 UNION ALL

SELECT 27,46,2000200,85,0,15,0 UNION ALL

SELECT 28,44,2000300,130,0,13,0 UNION ALL

SELECT 29,45,2000300,0,0,100,43 UNION ALL

SELECT 30,46,2000300,130,0,13,0 UNION ALL

SELECT 31,44,3000100,40,0,20,0 UNION ALL

SELECT 32,45,3000100,0,0,100,43 UNION ALL

SELECT 33,46,3000100,40,0,20,0 UNION ALL

SELECT 34,44,3000200,85,0,14,0 UNION ALL

SELECT 35,45,3000200,0,0,100,43 UNION ALL

SELECT 36,46,3000200,85,0,14,0 UNION ALL

SELECT 37,44,3000300,99,0,16,0 UNION ALL

SELECT 38,45,3000300,0,0,100,43 UNION ALL

SELECT 39,46,3000300,99,0,16,0 UNION ALL

SELECT 40,44,3000400,165,0,17,0 UNION ALL

SELECT 41,45,3000400,165,0,17,0 UNION ALL

SELECT 42,46,3000400,165,0,17,0

)

here is my actual data, it is of three resellerid('44','45','46') but can vary according to requirement,

now what i need is 'book_code' and 'price' where 'price' for those 'book_code' are same for all given resellerid,

in this sample data book_code('20100','20200','30100','3000400') have same 'price'

for resellerid('44','45','46') so it should return output as..

book_code,price

20100,0

20200,0

30100,50

3000400,165

thanks in advance for all of you..

Jason Tontz SSCommitted Points: 1957 More actions · Answer 12

Here is the statement I come up with:

SELECT Book_Code, Price FROM mySampleData

GROUP BY Price, Book_Code

Results:

Book_CodePrice

201000

202000

302000

401000

10001000

10002000

20001000

20002000

20003000

30001000

30002000

30003000

300010040

200010045

3010050

3020075

100010085

200020085

300020085

300030099

40100100

2000300130

3000400165

1000200180

VSP SSC-Addicted Points: 467 More actions · Answer 13

The INNERJOIN subquery ensures that you have more than one reseller_ids for the book_code with the same price.

NO_OF_RESELLERS_SELLING_SAME_BOOK_FOR SAME_PRICE is how many resellers are selling the same book for the same price. I think in this case substitute that with a 1.

NAME OF THE SELLER is the name of the book seller you are interested in

Please try this and let me know.

[Code]

SELECT *

FROM table1 T1

INNER JOIN (SELECT price, book_code

FROM (SELECT price, book_code, reseller_id

FROM table1 T

GROUP BY price, book_code, reseller_id

) BP

GROUP BY price, book_code

HAVING count(*) > NO_OF_RESELLERS_SELLING_SAME_BOOK_FOR SAME_PRICE

) T2 on T1.book_code = T2.book_code AND T1.price = T2.price

WHERE T1.reseller_id = 'NAME OF THE SELLER'

[/Code]

arslan_a2z Valued Member Points: 59 More actions · Answer 14

not according to my required result:

20100,0

20200,0

30100,50

3000400,165

i think i might haven't made it clear, what i need is:

book_code,price where book_code of 1 resellerid has same price among all resellerid

in mySampleData only (20100,20200,30100,3000400) have same price for all given resellerid.

Jason Tontz (10/27/2011)
Here is the statement I come up with:
SELECT Book_Code, Price FROM mySampleData
GROUP BY Price, Book_Code
Results:
Book_CodePrice
201000
202000
302000
401000
10001000
10002000
20001000
20002000
20003000
30001000
30002000
30003000
300010040
200010045
3010050
3020075
100010085
200020085
300020085
300030099
40100100
2000300130
3000400165
1000200180