SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Look Up Tables - Confused About Them

    Below is a sample of my database structure.

    PHP Code:
               Table   Field       Description
              QRF   q_id        Quote ID
              QRF   q_ref        Quote Reference
              RES   r_id        Response ID
              RES   r_s_id        Response Supplier ID
              RES   r_q_id        Quote ID linked to the Response
              SUP   s_id        Supplier ID
              SUP   s_name        Supplier
    's Name
              Q2S   q2s_q_id    Quote ID
              Q2S   q2s_s_id    Supplier ID 
    I need to extract which QRF.q_ref have not had a response to them. The responses are stored in the RES table.

    Would a query like the following work? I'm new to these lookup tables. $s_id will be referenced in the URL.

    PHP Code:
    SELECT Q2S.q2s_q_idQ2S.q2s_s_idQRF.q_idQRF.q_refRES.r_q_id
      FROM Q2S
    QRFRES
      WHERE Q2S
    .q2s_s_id $s_idQRF.q_id Q2S.q2s_r_idQRF.q_id != RES.r_q_id 

  2. #2
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not 100% sure what you are trying to achieve but in your SQL (MYSQL?) WHERE clause you are using commas(,) instead of AND's. != means is not equal to in PHP, IN SQL you should use <>.

    So your query would look something like:
    Code:
     SELECT Q2S.q2s_q_id, Q2S.q2s_s_id, QRF.q_id, QRF.q_ref, RES.r_q_id
      FROM Q2S, QRF, RES
      WHERE Q2S.q2s_s_id = $s_id AND QRF.q_id = Q2S.q2s_r_id AND QRF.q_id <> RES.r_q_id
    Give it a go. try in your phpMyAdmin panel. If it doesn't work it will tell you where you are going wrong.

    Good Luck

  3. #3
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers for that, I will give it a go on my computer at home this evening and post my findings.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spoondevil
    I need to extract which QRF.q_ref have not had a response to them.
    Code:
    select QRF.q_ref
      from QRF
    left outer
      join RES
        on QRF.q_id 
         = RES.r_q_id 
       and RES.r_s_id = $s_id
     where RES.r_q_id is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •