SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    request for help with a SELECT query

    I need a help with selecting the rows in a table depending on the status in previous table.

    Table1

    PHP Code:
    Column1     Column2        Status
    Smith       Alex                1
    Smith       Mark                0
    John        Smith               1 

    I have second table with 2 columns. I want to select only those rows which have ANY of the user of table1 in column1 or column2 with status 1.

    Table 2
    PHP Code:
    Column1        Column2        
    Smith          Anderson
    Martin         Walker
    Alex            Scott 
    For example, Table1, the first row has status 1. Now i want to SELECT the rows from table2, which have "smith" OR "alex" in Column1 OR Column2 (Either Smith, or Alex). So, from Table2 it should select Row1 and Row3.

    Do I have to join the tables? Wont that be slow?
    Can I perform SELECT with EXISTS query?

    Any help will be highly appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT table2.column1
         , table2.column2
      FROM table1
    INNER
      JOIN table2
        ON table2.column1 = table1.column1
        OR table2.column2 = table1.column1
        OR table2.column1 = table1.column2
        OR table2.column2 = table1.column2
     WHERE table1.status = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot for your reply. I just wonder if this will be slower in the tables with the huge records? (for example if there are like a million rows? )
    Would this be more effective way or EXISTS or IN ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kaash1 View Post
    Would this be more effective way or EXISTS or IN ?
    depends on what you're actually trying to do

    i really don't like working with generic table and column names, i have no idea what's actually going on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply again.
    The Table1 consists of the relation between 2 users. To check if they both are have relationship.
    Table 2 consists of activities of each user. I want to get the activities from Table 2 for a specific user, which exists in Table1 in any of the column. I hope it makes some sense.
    For example, I'm 'smith'; and I have relationship with Alex and John (as shown in Table1). Now I want to get all the records(activities) of Alex and John from the Table 2.

    I hope it makes sense. Thanks for your time and I look forward for your advice.
    Thanks for your time and I look for your advice.

  6. #6
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Moved to MySQL forum
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kaash1 View Post
    I hope it makes sense.
    not very much, no

    did you try the query i gave you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is structure of my tables, with the data. First user_friends have 1 row, which which has username, sami1 and friend_username 'test1', and status 1.

    What I want to do is:
    I want to display all the activities from user_activities table, where username is sami1 or object_username is sami1, or username is test1 or object_username is test1.
    Thanks again for your help.



    PHP Code:
    -- Table structure for table `user_friends`
    --

    CREATE TABLE IF NOT EXISTS `user_friends` (
      `
    idint(11NOT NULL AUTO_INCREMENT,
      `
    usernamevarchar(15NOT NULL,
      `
    friend_usernamevarchar(15NOT NULL,
      `
    request_dateint(15NOT NULL,
      `
    statusint(1NOT NULL DEFAULT '0',
      
    PRIMARY KEY (`id`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

    --
    -- 
    Dumping data for table `user_friends`
    --

    INSERT INTO `user_friends` (`id`, `username`, `friend_username`, `request_date`, `status`) VALUES
    (9'sami1''test1'12768870901);

    CREATE TABLE IF NOT EXISTS `user_activities` (
      `
    activity_idint(11NOT NULL AUTO_INCREMENT,
      `
    activity_typevarchar(15NOT NULL,
      `
    activity_usernamevarchar(15NOT NULL,
      `
    activity_object_idvarchar(15NOT NULL,
      `
    activity_object_usernamevarchar(15NOT NULL,
      
    PRIMARY KEY (`activity_id`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=;

    --
    -- 
    Dumping data for table `user_activities`
    --

    INSERT INTO `user_activities` (`activity_id`, `activity_type`, `activity_username`, `activity_object_id`, `activity_object_username`) VALUES
    (5'friend''sen2''''sami1'),
    (
    4'friend''test1''''sami1'),
    (
    6'answer''sami1''17'''),
    (
    7'friend''Jade''''Russel'); 

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kaash1 View Post
    I want to display all the activities from user_activities table, where username is sami1 or object_username is sami1, or username is test1 or object_username is test1.
    Code:
    SELECT activity_id
         , activity_type
         , activity_username
         , activity_object_id
         , activity_object_username
      FROM user_activities
     WHERE activity_username = 'sami1'
        OR activity_object_id = 'sami1'
        OR activity_username = 'test1'
        OR activity_object_id = 'test1'
    
    activity_id activity_type activity_username activity_object_id activity_object_username
         4      friend        test1                                sami1
         6      answer        sami1             17
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, If was not clear with my earlier post. First I want to check user_friends table, if a specific user has any friend. (for example test1 has any friend, in the above table, it has a friend named sami1).
    Now I want to select the friends activities from the table user_activities. (In the above example it would be sami1).
    My problem is that, im not understanding, how can i first check in the user_friends, select the users from there and then selecting those users activities from user_activties table;

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Ignoring the fact for the minute that your table design probably needs a bit of streamlining..

    I'm sure rudy's going to disagree with me here (and do it 50 times better), but I get the feeling this would be better accomplished with 2 queries, using an intermediary language (such as PHP) to smash the results from the first together. Otherwise you're going to have to run the subquery 4 times (Correction: twice)...

    Code:
    SELECT friend_username FROM user_friends WHERE username = $user;
    
    //back in the language, concatenate all the results together with "," inbetween them...which i've set as $friends
    
    SELECT activity_id
         , activity_type
         , activity_username
         , activity_object_id
         , activity_object_username
      FROM user_activities
     WHERE activity_username IN($user,$friends)
        OR activity_object_id IN($user,$friends)

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    this will be my last guess at what you really want
    Code:
    SELECT activity_id
         , activity_type
         , activity_username
         , activity_object_id
         , activity_object_username
      FROM ( SELECT friend_username
               FROM user_friends
              WHERE username = 'sami1'
             UNION ALL
             SELECT username
               FROM user_friends
              WHERE friend_username = 'sami1'
           ) AS f
    INNER
      JOIN user_activities
        ON user_activities.activity_username = f.username
        OR user_activities.activity_object_id = f.username
        OR activity_username = 'sami1'
        OR activity_object_id = 'sami1'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting from 2 Tables?

    I need help with the following query, i will be so thankful if anyone can help me please.

    I have two tables in database:

    Table1:
    Code:
    Column1			Column2			   Status
    smith			john				1
    jack			smith				0
    julia			 rob				1
    Table 2:
    Code:
    Column1			Column2
    thomas			  lewis
    scott			  smith
    john                     evans
    lopez                    john
    Can you please help me building following query:
    --------------------------
    1. I want to select all rows from Table 1 WHERE there is smith in column1 OR column2 AND status 1.

    2. IF there is smith in column 1, then take the value from column 2 (on same row), and if there is smith in column2, then select value in column 1 of the row.
    (For example, from above Table1, it should select first row and then as smith is in column1, we take the column2 value, which is john;

    3. Then select those rows from Table 2 WHICH contains that value(john) in column1 or column2 of table 2, (which we got by selecting from Table 1.) So in above table 2, it should select last 2 rows.
    ----------------------------


Tags for this Thread

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
  •