Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: Seems Soo Typical JOIN

    I HAVE THREE TABLES
    TABLE S(SID, SNAME,ADDRESS)
    SID IS PRIMARY KEY
    TABLE P(PID,PNAME,COLOR)
    PID IS PRIMARY KEY
    TABLE CAT(SID,PID,COST)
    PRIMARY KEY: SID+PID
    REFERENCE KEY : SID REFERENCES S.SID
    : PID REFERENCES P.PID
    I WANT THE SNAME OF SUPPLIERS WHO SUPPLY EVERY PART
    I AM TRYING
    Code:
    SELECT S.SNAME,S.SID,P.PID FROM S INNER JOIN CAT C 
    ON s.sid=c.sid CROSS JOIN P WHERE P.PID=C.PID
    AFTER THE WHERE WHICH CONDITION I HAD TO PUT IS A LITTLE BIT CONFUSED PLZ HELP
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This problem is known as relational division:
    Divided We Stand: The SQL of Relational Division
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    this query is not a division pootle its just a select statement i will elaborate
    I WANT THE SNAME OF SUPPLIERS WHO SUPPLY EVERY PART
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mathukumali View Post
    this query is not a division pootle its just a select statement i will elaborate
    I WANT THE SNAME OF SUPPLIERS WHO SUPPLY EVERY PART
    did you even ~try~ looking at the web page pootle linked to?

    the answer to your problem is included!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Relational division is not mathematical division.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    oops sorry sorry just watched the link and got the result thanks pootle
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  7. #7
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    find the pnames of parts supplied by london supplier and by no one else

    what it means any suggestions will be very helpful thanks in advance
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mathukumali View Post
    what it means
    it means like this --

    some parts are supplied by more than one supplier, okay so far?

    okay, now, some suppliers are located in london, yes?

    now, consider the suppliers in london, and consider all the parts they supply... now eliminate the parts that are also supplied by some other supplier who is not in london, and what you're left with is the parts that are supplied by a london supplier and by no one else
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    i was trying like
    Code:
    SELECT P.PNAME,S.ADDRESS FROM P JOIN CAT C 
    ON P.PID=C.PID INNER JOIN S ON S.SID=C.SID WHERE S.ADDRESS='london' and
    can you plz help me to add the condition
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  10. #10
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    and some thing like
    Code:
    select P.PNAME,S.ADDRESS, 
    CASE WHEN S.ADDRESS='LONDON' AND NOT IN ('').....FROM P JOIN CAT C 
    ON P.PID=C.PID INNER JOIN S ON S.SID=C.SID WHERE S.ADDRESS='london'
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's an idea

    for each product, find its supplier...

    then GROUP BY product HAVING MAX(suppliercity)='London' AND MIN(suppliercity)='London'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    thanks rudy for your help group by is enough na why do u said abt the max & min condition
    Last edited by mathukumali; 11-02-10 at 02:47. Reason: ..
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

Posting Permissions

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