SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 30
  1. #1
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    getting rows where similar values in other tables

    i have 3 tables. the first two have 'name' as one of their columns. they also both have 'id' as one of their columns. the third table is the table i need to grab data from. this table also has 'id' as a column name, but doesn't have 'name'. i need to grab all the rows from table 3, where the id number corresponds to the same id number in the other two tables, where these two tables have a certain value for their name column.

    i.e. they had the value dave. and id values for dave were 4, 7, 9. i would want to grab the rows with id's 4, 7 and 9 from the third tabvle. thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    love your nickname

    if the row with dave in table 1 can have id's 4,7,9, then will the rows with dave in table 2 also have exactly those ids, no more, no less?

    if so,
    Code:
    select table3.*
      from table1
    inner
      join table2
        on table1.id
         = table2.id
    inner
      join table3
        on table1.id 
         = table3.id
     where table1.name
         = 'dave'
    if not, then you want
    Code:
    select distinct table3.*
      from table1
    inner
      join table2
        on table1.name
         = table2.name
    inner
      join table3
        on table1.id = table3.id
        or table2.id = table3.id
     where table1.name
         = 'dave'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cheers - i thought of that name myself - the id's for table 1 & 2 are exactly the same. I'll let you know if the query works.

  4. #4
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah - problem - table 2 might or might not contain the same ids as table1.

    any ideas?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, i gave you my ideas for this scenario, under "if not, then you want"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but will this work even if table2 doesn't have any rows containing dave? it doesn't seem to be working, tough the top code worked when they both contained david.

  7. #7
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh it does work - looks like that name was fitting after all.

    cheers rudy.

  8. #8
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no - wait - it isn't working now! argh!

    situation - table1 contains a row with id = 2 and name = david, and table2 conatinas no information. there is a correspnding id = 2 in table 3 - why is this row in table3 not showing?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your original specs seemed to suggest that both table1 and table2 must have the name being searched for:
    where the id number corresponds to the same id number in the other two tables, where these two tables have a certain value for their name column
    now it appears you want table 3 rows if either table 1 or table 2 has a row

    this is a different situation

    the best way to do this is
    Code:
    select table3.*
      from table1
    inner
      join table3
        on table1.id = table3.id
     where table1.name = 'dave'
    union
    select table3.*
      from table2
    inner
      join table3
        on table2.id = table3.id
     where table2.name = 'dave'
    this is the most efficient way of doing it, and note that duplicate table3 rows are eliminated by the UNION

    if you not on version 4.0+ (which supports UNION) you can try:
    Code:
    select distinct table3.*
      from table3
    left outer
      join table1
        on table3.id = table1.id
       and table1.name = 'dave'
    left outer
      join table2
        on table3.id = table2.id
       and table2.name = 'dave' 
     where table1.id is not null
        or table2.id is not null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry bout that - wasn't the best description.

    now it appears you want table 3 rows if either table 1 or table 2 has a row
    let me clarify - they can either have a row with the same name, or they can both have a row with the same name.

  11. #11
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    seems to be working now once again thanks rudy.

  12. #12
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    one more question - if both table1 and table2 contain the same datetime columns, how could i, or is it possible to, order the results by date. the thing is table3 does not have a datetime field.

    Thanks.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, it's possible

    but now you must show me the query that you are actually running, because i've given you 4 of them and i have no idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the query that i'm running is:
    Code:
    	function find_user_posts() {
    		$this->sql[] = "SELECT admin . * FROM message_board INNER JOIN admin ON message_board.id"
    					 . " = admin.id WHERE message_board.name = '" . $this->attributes[user] . "' UNION SELECT admin . *   "
    					 . "FROM time_spent INNER JOIN admin ON time_spent.id = admin.id WHERE time_spent.name = '" . $this->attributes[user] . "'";
    	}
    i.e. the third set of code you showed me.

    Thanks.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, that looks fine

    and which date (that isn't in the admin table) would you like to sort the admin table by?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just so that the most recent dates shows up first.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, let me ask the question a slightly different way

    you are returning only admin rows, and the admin table doesn't have a date field, but you still want to sort the admin rows by some date

    the only option is to attach a date from the message_board and time_spent tables to the admin rows

    so what are the names of the date fields in the message_board and time_spent tables that you would like to sort the admin rows by?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    both the date columns are called 'date'. are you talking about creating a temporary table where it is the admin table plus a date column?

    you are returning only admin rows, and the admin table doesn't have a date field, but you still want to sort the admin rows by some date
    exactly.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    (
    SELECT message_board.`date`
         , admin.* 
      FROM message_board 
    INNER 
      JOIN admin 
        ON message_board.id
         = admin.id 
     WHERE message_board.name = '" . $this->attributes[user] . "' 
    ) 
    UNION 
    (
    SELECT time_spent.`date`
         , admin.*    
      FROM time_spent 
    INNER 
      JOIN admin 
        ON time_spent.id 
         = admin.id 
     WHERE time_spent.name = '" . $this->attributes[user] . "'"
    )
    order
        by 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'll let you know if that works - another really quick one:

    Code:
    select message_board.* from message_board LEFT JOIN admin ON message_board.id = admin.title WHERE admin.title = 'srtg'
    this code is supposed to get every row in message_board that has a title of 'srtg'. the trouble is, there is no title column in message_board - it is in the admin table - why does my code not return any results??

    the tables are linked by id columns.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the tables are linked by id, but you are linking the id to the title!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh yeh

  23. #23
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i used your most recent bit of code, and my results seem to have duplicates. i.e. there are more than 1 results for the same id. is there an way i can eliminate results with the same id. here is a print of my array using the above code:

    Code:
    Array
    (
        [0] => Array
            (
                [0] => Array
                    (
                        [0] => 2004-11-25
                        [1] => 6
                        [2] => 
                        [3] => wefefef
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [1] => Array
                    (
                        [0] => 2004-11-25
                        [1] => 5
                        [2] => 
                        [3] => wef
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [2] => Array
                    (
                        [0] => 2004-11-25
                        [1] => 4
                        [2] => 
                        [3] => ewfwef
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [3] => Array
                    (
                        [0] => 2004-11-25
                        [1] => 3
                        [2] => fwfe
                        [3] => adswe
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [4] => Array
                    (
                        [0] => 2004-11-25
                        [1] => 2
                        [2] => xf
                        [3] => gh
                        [4] => 0
                        [5] => 0
                        [6] => 0
                        [7] => 1
                    )
    
                [5] => Array
                    (
                        [0] => 2004-11-25
                        [1] => 1
                        [2] => ereqr
                        [3] => efeqrfer
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [6] => Array
                    (
                        [0] => 2004-11-25
                        [1] => 7
                        [2] => 
                        [3] => efefe
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [7] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 13
                        [2] => 
                        [3] => jlkklnkn
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [8] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 12
                        [2] => 
                        [3] => ASDAsd
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [9] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 11
                        [2] => 
                        [3] => lih
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [10] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 15
                        [2] => 
                        [3] => vvfqefv
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [11] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 10
                        [2] => 
                        [3] => hlihu
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [12] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 5
                        [2] => 
                        [3] => wef
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [13] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 8
                        [2] => lhgg
                        [3] => uliigu
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [14] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 2
                        [2] => xf
                        [3] => gh
                        [4] => 0
                        [5] => 0
                        [6] => 0
                        [7] => 1
                    )
    
                [15] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 4
                        [2] => 
                        [3] => ewfwef
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [16] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 14
                        [2] => khjgljvglj
                        [3] => sdkjhbdshjgjh
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
                [17] => Array
                    (
                        [0] => 2004-11-29
                        [1] => 1
                        [2] => ereqr
                        [3] => efeqrfer
                        [4] => 0
                        [5] => 1
                        [6] => 0
                        [7] => 0
                    )
    
            )
    
    )
    as you can see, there are some results where [0][#][1] are the same. is there anything that can be done?

    Thanks.
    Last edited by Scheisskopf; Nov 29, 2004 at 10:22.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i don't do arrays, in fact i don't do php at all

    are you having trouble with a mysql query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Evangelist Scheisskopf's Avatar
    Join Date
    Nov 2004
    Location
    Southampton, UK
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i just posted the array to show you the results i get from your above code - as you can see there are some results where the id field, i.e. elements [0][#][1] are the same. i.e. element [0][4][1] and [0][14][1] both have value 2. this means that in my results, i will have to sets of results that have id 2. is there any way i can get the query to return rows with unique ids, i.e. if there are more than 1 row with the same id, can i delete the rest so there is only one row with that id.


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
  •