Help with this query what am i doing wrong

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with this query what am i doing wrong

Author

Topic

ic3man
Starting Member

6 Posts

Posted - 2012-05-23 : 15:48:33

SELECT Book.Writer,Book.Name from Sales,Book WHERE YEAR(Sales.Dateofsale) = YEAR(GETDATE())
GROUP BY Book.Writer,Book.name

I'm supposed to pull out writers name and his book only if that book has been sold in the current year.. and the way i did it it outputs all the writers and their books.. what is wrong with that code? am i supposed to use JOIN command as well? and if so can someone help me thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 16:03:03

the problem with your query is you've not specified relationship on which tables have to be joined. So its effectively doing a cartesian product with where condition.

you should ideally modify it like this


SELECT Writer,Name from Book 
WHERE EXISTS(SELECT 1 FROM Sales
             WHERE YEAR(Dateofsale) = YEAR(GETDATE())
             AND <relatedcolumninsales> = Book.<relatedcolumninbook>
             )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ic3man
Starting Member

6 Posts

Posted - 2012-05-23 : 16:08:44

Works. Thanks :) Much love ! :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 16:12:34

quote:
Originally posted by ic3man

Works. Thanks :) Much love ! :D

Actually it only outputs 7 results even if i have 9 of them in records.. let me see if i can find the issue

might be that other 2 has no Sales at all or Sales present might be outside current year

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ic3man
Starting Member

6 Posts

Posted - 2012-05-23 : 16:14:28

Yeah, nevermind the last addition.. i'm an idiot.. :) the 2 extra sales are for the same book.. :) thanks again mate ;)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 16:19:20

quote:
Originally posted by ic3man

Yeah, nevermind the last addition.. i'm an idiot.. :) the 2 extra sales are for the same book.. :) thanks again mate ;)

no problem
it happens to all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ic3man
Starting Member

6 Posts

Posted - 2012-05-23 : 16:22:31

Mate im sorry if im being annoying with all the questions.. but can i contact you somehow.. i've done 2/3 parts for my college exam, and i have no clue how to do the third one.. it's basic stuff for you i'm sure :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 16:07:29

quote:
Originally posted by ic3man

Mate im sorry if im being annoying with all the questions.. but can i contact you somehow.. i've done 2/3 parts for my college exam, and i have no clue how to do the third one.. it's basic stuff for you i'm sure :D

you want someone to take exam for you?
What will you benefit from that?
Try to learn and apply yourself without which you wont learn anything. You can always ask us for help if you've any doubt. But please dont expect someone else to take exam in your behalf!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources