SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Durham, UK
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Left join, limiting results by date

    Hi all,

    I'm trying to find a list of subscribing companies that haven't accessed my site. They pay per document and this is indicated in the cost_centers table. The following sql works perfect..

    Code:
    SELECT companies.company_id, companies.name
    FROM companies LEFT JOIN cost_centres
    USING (company_id)
    WHERE cost_centres.company_id IS NULL
    .. but when I try and find companies that have used the site between certain dates thus..

    Code:
    SELECT companies.company_id, companies.name
    FROM companies LEFT JOIN cost_centres
    USING (company_id)
    WHERE cost_centres.company_id IS NULL
    AND cost_centres.date >= '1086044400'
    AND cost_centres.date <= '1086130799'
    It doesn't return any data I've even tried setting a ridiculous date range of 0 to 99999999 but this doesn't work either. I assume there's a flaw in the logic of my SQL somewhere

    Here's my table structures

    {companies}
    Code:
    CREATE TABLE companies (
      company_id varchar(6) NOT NULL default '',
      name varchar(40) NOT NULL default '',
      max_users int(5) NOT NULL default '0',
      word char(1) NOT NULL default 'n',
      pdf char(1) NOT NULL default 'n',
      html char(1) NOT NULL default 'n',
      PRIMARY KEY  (company_id),
      KEY name (name),
      KEY company_id (company_id)
    ) TYPE=MyISAM;
    {cost_centres}
    Code:
    CREATE TABLE cost_centres (
      company_id varchar(6) NOT NULL default '',
      report_code varchar(12) NOT NULL default '',
      user_id varchar(80) NOT NULL default '',
      project_ref varchar(80) NOT NULL default '',
      cost_centre varchar(80) NOT NULL default '',
      user varchar(80) NOT NULL default '',
      date int(14) NOT NULL default '0'
    ) TYPE=MyISAM;
    Any advice warmly appreciated

    Stew

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT companies.company_id, companies.name
    FROM companies LEFT JOIN cost_centres
    on companies.company_id = cost_centres.company_id
    AND cost_centres.date >= '1086044400'
    AND cost_centres.date <= '1086130799'
    WHERE cost_centres.company_id IS NULL

  3. #3
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Durham, UK
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, works most excellently


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
  •