Results 1 to 2 of 2

Thread: joins & qry

  1. #1
    Join Date
    Nov 2002
    Posts
    22

    Unanswered: joins & qry

    hi guys,

    need some help with sql..
    i have 2 tbls 1-m,
    cust(CId(pk), creditlimit, name) and ord(ordId(pk), Cid(fk), date, totalO)

    want to display name , creditlimit, & Newcredit limit for those customer who had orders more
    than 1000. Newcredit is 20%increase if they had order above 1000.

    I started like this but am getting error!

    select name, cId, aa
    from ord o, cust c
    where o.cid =c.cid and total IN (select sum(total) aa > 1000 from ord)
    group by name, cid

    thnks
    mark

  2. #2
    Join Date
    Oct 2003
    Posts
    71

    Re: joins & qry

    Originally posted by mark00189
    hi guys,

    need some help with sql..
    i have 2 tbls 1-m,
    cust(CId(pk), creditlimit, name) and ord(ordId(pk), Cid(fk), date, totalO)

    want to display name , creditlimit, & Newcredit limit for those customer who had orders more
    than 1000. Newcredit is 20%increase if they had order above 1000.

    I started like this but am getting error!

    select name, cId, aa
    from ord o, cust c
    where o.cid =c.cid and total IN (select sum(total) aa > 1000 from ord)
    group by name, cid

    thnks
    mark

    Try

    select name,CiD,sum(total)
    from ord o, cust c
    where o.cid = c.cid
    group by name,cid
    having sum(total) > 1000


    Hope it helps

    Reema

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •