Results 1 to 7 of 7

Thread: Join Help...

  1. #1
    Join Date
    Dec 2003
    Posts
    31

    Unanswered: Join Help...

    Here is an example query:
    SELECT * FROM userfield, userevent WHERE userfield.field6 = 'US - New York' AND userfield.userid = userevent.userid AND startstamp >= 1101352857 AND startstamp <= 1103944857 AND eventtype LIKE '%12%' OR eventtype LIKE '%14%'

    As you can see I'm using 2 tables. I need to take any users which userfield.field6 matches 'US - New York' and then from there I need to search the userevent tables where the rest of the information matches. So basically just get all the userids where it matches US - New York then take all those matching userids in the userevent table and find the matching coorespoding info.

    Should be pretty simple I just have no idea how joins work. Any explainations would be greatful!

    Yes I've done research also, I cannot seem to get it working!

    Thanks in advance!
    vBulletin Hacking God - Portfolio
    FireFoxForum.com - Where people talk about FireFox.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    parentheses problem

    you have

    ... WHERE a AND b AND c AND d AND e OR f

    which, because ANDs take precedence over ORs, is evaluated as

    ... WHERE ( a AND b AND c AND d AND e ) OR f

    whereas i'm certain what you want is

    ... WHERE a AND b AND c AND d AND ( e OR f )

    so just code the parentheses and all will be well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Posts
    31
    That's not working... Here is even a simplier query:
    SELECT * FROM userfield, userevent WHERE userfield.field6 = 'US - New York' AND userfield.userid = userevent.userid AND startstamp >= 1101357953 AND startstamp <= 1103949953

    I need to get all the userids matching userfield.field6 THEN I need to only retrieve the information where the userids match and the stamps are inbetween the info
    vBulletin Hacking God - Portfolio
    FireFoxForum.com - Where people talk about FireFox.

  4. #4
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    hai whsox.

    I don't see anything wrong in the query. I suppose this should give u what u want. This query will return all those rows in which all the four conditions are satisfied.

    BTW could u explain us how ur saying that this is not working...
    Sudar

    --
    My Blog

  5. #5
    Join Date
    Dec 2003
    Posts
    31
    This query returns 4 rows:
    SELECT *
    FROM userfield, userevent
    WHERE userfield.field6 = 'US - New York'

    Correct!

    This query returns returns 0 rows:
    SELECT * FROM userfield, userevent WHERE userfield.field6 = 'US - New York' AND userfield.userid = userevent.userid

    I would think it would work the same way right?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "get all the userids matching userfield.field6" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    hai whsoz,

    I think you have a misunderstaning abt joins.


    Assume there a 2 tables table1 with n rows and table2 with m rows
    when us say

    select * from table1, table2

    it will return all rows from both tables (n*m)rows

    and
    select * from table1,table2 where table1.col1 = "some value"

    will return all rows from table1 which satisfy the condition multiplied by all rows from table2

    and
    select * from table1.table2 where table1.col1 = "some value" and table1.col2 = table2.col2

    will return only rows from table1 which satify the condition multiplied by rows from table2 which satisfy the condition

    so surely u will have less no of rows for the third query when compared with the second query...

    so i think there is no problem with the query..(i suppose)

    ruddy correct me if i am wrong somewhere....
    Last edited by Sudar; 11-25-04 at 01:53. Reason: Formating
    Sudar

    --
    My Blog

Posting Permissions

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