Post reply

SQl Statements

  • December 30, 2010 at 2:50 pm

    #232823

    Hi

    I have two tables One for all the books in the shop and the other for the books sold and I want to know the books that are unsold

    the fields in the two tables are different

    -------------------------------------------

    table 1: which contains all books in the shop

    BookID

    BookName

    BookTitle

    Author

    --------------------------------------------------

    Table 2: which contains the sold books only

    BookID

    Bookname

    BookTitle

    DateSold

    -----------------------------------------------------

    I want to know all the books unsold in the shp! How can I use the SQL query to do so?

    I used

    "SELECT * FROM TABLE1 NOT EXISTS (SELECT * FROM TABLE2)"

    but it gave me Null

  • December 30, 2010 at 2:59 pm

    #1267972

    Have a look at left outer joins...

    (question aside: it's homework, isn't it?)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 30, 2010 at 3:54 pm

    #1267983

    No

    Believe me it is simply a model of my project NOT Homework

  • December 30, 2010 at 4:27 pm

    #1267985

    You work for a bookshop?

    Right idea with the not exists, minor missing detail. See: http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/ for specifics on EXISTS

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 30, 2010 at 4:56 pm

    #1267989

    Zarah:

    Offhand, this statement:

    "SELECT * FROM TABLE1 NOT EXISTS (SELECT * FROM TABLE2)"

    Will return nothing unless there are no records in Table2. Also, this isn't your exact statement since the WHERE clause is missing so it wouldn't have ran. 😛 So, since we're dealing with psuedocode, let's see if I can get you in the right direction.

    You'll need to deal with nulls in Table2 as well depending on complexity, but at a guess, the statement you're looking for would be something like this:

    SELECT * FROM Table1 AS t1 WHERE NOT EXISTS ( SELECT 1 FROM Table2 AS t2 WHERE t1.BookID = t2.BookID)

    As an example, see below. If you want more help with your specific data/structure, check out the first link in my signature.

    create table #T1 (tVal INT)

    create table #T2 (tVal INT)

    INSERT INTO #t1 VALUES ( 1)

    INSERT INTO #t1 VALUES ( 2)

    INSERT INTO #t1 VALUES ( 3)

    INSERT INTO #t1 VALUES ( 4)

    INSERT INTO #t2 VALUES ( 2)

    INSERT INTO #t2 VALUES ( 4)

    SELECT * FROM #t1 AS t1 WHERE NOT EXISTS ( SELECT 1 FROM #t2 AS t2 WHERE t1.tVal = t2.tVal)

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 30, 2010 at 8:55 pm

    #1268000

    Hi Guys

    I do not work for Bookshop!!

    what is the problem with you guy? :crying:

    You are making fun of me :angry:

    Thank you Craig for you explanation

    🙂

    I found another solution using ( NOT IN )

    Thank you all for give my Question some of your time 😉

  • December 30, 2010 at 10:31 pm

    #1268010

    zahrahLand (12/30/2010)

    Hi Guys

    I do not work for Bookshop!!

    what is the problem with you guy? :crying:

    You are making fun of me :angry:

    Thank you Craig for you explanation

    🙂

    I found another solution using ( NOT IN )

    Thank you all for give my Question some of your time 😉

    NOT EXISTS is better than NOT IN Command.

    You can also use LEFT OUTER JOIN, with where condition like: t1.value is not null and t2.value is null

    Thanks

  • December 31, 2010 at 2:44 am

    #1268053

    zahrahLand (12/30/2010)

    I do not work for Bookshop!!

    what is the problem with you guy? :crying:

    You are making fun of me :angry:

    No we're not.

    We asked if this was homework (school project, university project, etc) You said no. So I asked if you work for a book shop (because that's about the only place I can think of that would have this as a real project).

    If this is not a school project and you don't work for a bookshop, what is this? Independent study?

    Did you read the blog post I linked to? Not In will work, but it's far from optimal and breaks when there are nulls involved. If you read that, you'll understand why the exists didn't work and how to make it work.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

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