#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    2
    Rep Power
    0

    Struggling with a query


    Hello,
    I am struggling with a query. And wondering if someone could help.



    I have a table of tags (service request tickets)
    and a table of serial numbers

    From my tags I am doing this

    Code:
    Select * from tag 
    where
    tag.created
    BETWEEN '2012-9-01'
    AND '2012-12-29' 
    and
    tag.tagtype <> 'service'
    Now,
    i would like to join with serialnumber (table) where
    (not sure inner/outer or just join)

    tag.serialnumberid is in serialnumber.id
    and serialnumber .modelid = 80

    I am thinking it should be easy.
    Any help would be appreciated
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    if you could please do a SHOW CREATE TABLE for each table please, i can help you with the sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    95
    Rep Power
    3
    assuming you just want the data that there's an entry for in both tables, it should be something like this:
    Select * from tag
    join serialnumber on tag.serialnumberid = serialnumber.id and and serialnumber.modelid = 80
    where
    tag.created BETWEEN '2012-09-01' AND '2012-12-29'
    and tag.tagtype <> 'service'
    if you also want the tags that have no matching serialnumber, you would use Left Join.
    If you also want the serial numbers that have no corresponding tags, you would use Right Join

IMN logo majestic logo threadwatch logo seochat tools logo