February 3, 2014 at 7:24 pm
Hi All I have this code
SELECT f.product_name,f.image_url,f.non_working_price,f.description,f.price,f.deeplink,
m.product_name,m.image_url,m.non_working_price,m.description,m.price,m.deeplink,c.companylogo
from fonehub f,
MobilePhoneCompany m ,
company_logos c
where f.product_name = 'Nokia C7'
and m.product_name = 'Nokia C7'
and c.program_id in (select distinct program_id from company_logos)
and I am so confused where I am going wrong as it returns
product_name ,image_url ,non_working_price ,description ,price ,deeplink ,product_name ,image_url ,non_working_price ,description ,price ,deeplink ,companylogo
Nokia C7 ,http://fonehub.co.uk/Pictures/Product/2/Large/Imag... ,10 ,Nokia C7 ,30.0 ,http://track.webgains.com/click.html?wgcampaignid=... ,Nokia C7 , http://moneyforyourphone.com/productimage/mobile-p... ,3.00 ,29.00,http://track.webgains.com/click.html?wgcampaignid=... ,images/ec.jpg
Nokia C7 ,http://fonehub.co.uk/Pictures/Product/2/Large/Imag... ,10 ,Nokia C7 ,30.0 ,http://track.webgains.com/click.html?wgcampaignid=... ,Nokia C7 ,http://moneyforyourphone.com/productimage/mobile-p... ,3.00 ,29.00 ,
http://track.webgains.com/click.html?wgcampaignid=... ,images/fone.jpg
February 3, 2014 at 7:31 pm
your data and query are so poorly formatted I can't make heads or tails out of either. I do note that you are using non-ANSI joins (bad move - deprecated and will break at some point) plus you didn't actually JOIN the tables from what I saw. You just qualified outputs somewhat.
Anyway, I would look for Cartesian product if you are getting duplicates you don't want/expect.
Oh, and the DISTINCT inside the IN clause - unnecessary.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2014 at 7:38 pm
I am new to sqlserver and that's totally lost me
February 4, 2014 at 7:13 am
Oracle765 (2/3/2014)
I am new to sqlserver and that's totally lost me
New to SQL Server doesn't mean you can't format your code to be readable. Same for the output. Look at the post and tell me if you would want to try to figure out that mess to help someone!! :w00t:
You have Oracle in your UserName. Do you know that platform? If so then you should know what an ANSI-style join is. If not Binoogle is your friend.
Also a Cartesian Product is when you get an increase in rows that you do not want because of duplication of values.
TableA has field a and rows with the values 1, 1 and 2, 2
TableB has field b and rows with the values 1, 1, 2, 2, 2 and 3
If you join TableA with TableB on a and b it would look like this as an ANSI style join:
SELECT a, b
FROM TablaA INNER JOIN TableB on a = b
In this case you would get out the following:
1,1
1,1
1,1
1,1
2,2
2,2
2,2
2,2
2,2
2,2
Two 1 rows in A and two 1 rows in B MULTIPLY times each other to give you FOUR output rows with 1,1. Similarly 2X3 gives you SIX output rows for 2,2. Hopefully this helps. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply