SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimize query syntax

    I have a pair of users, and I want to find is the relationship between users allready exists, so I wrote this query, that I dont like.IT checks if
    (partner1=user1 AND partner2=user2) OR (partner2=user1 AND partner1=user2)


    SELECT n.nid FROM node n INNER JOINcompetition_pair p ON p.nid = n.nid INNER JOIN content_type_competition_pair c ON c.nid = n.nid WHERE n.type='competition_pair' AND ((c.competition_partner1_uid=%d AND c.competition_partner2_uid=%d) OR (c.competition_partner1_uid=%d AND c.competition_partner2_uid=%d)) AND p.active=1

    How can I make this more simple?
    Thanx!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you please do a SHOW CREATE TABLE for your three tables, and explain each one, what it's for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select n.nid 
      from node n 
      join competition_pair p 
        on p.nid = n.nid 
      join content_type_competition_pair c 
        on c.nid = n.nid 
     where n.type = 'competition_pair' 
       and (%d,%d) in ((c.competition_partner1_uid,c.competition_partner2_uid),
                       (c.competition_partner2_uid,c.competition_partner1_uid))
       and p.active = 1

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, It looks better than my code.


Bookmarks

Posting Permissions

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