Request for help with a SELECT query

Databases
1

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

Table1


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 

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

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:


Column1			Column2			   Status
smith			john				1
jack			smith				0
julia			 rob				1

Table 2:


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.

3

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)…

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)
4

this will be my last guess at what you really want

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'
5

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.

-- Table structure for table `user_friends`
--

CREATE TABLE IF NOT EXISTS `user_friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(15) NOT NULL,
  `friend_username` varchar(15) NOT NULL,
  `request_date` int(15) NOT NULL,
  `status` int(1) NOT 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', 1276887090, 1);

CREATE TABLE IF NOT EXISTS `user_activities` (
  `activity_id` int(11) NOT NULL AUTO_INCREMENT,
  `activity_type` varchar(15) NOT NULL,
  `activity_username` varchar(15) NOT NULL,
  `activity_object_id` varchar(15) NOT NULL,
  `activity_object_username` varchar(15) NOT NULL,
  PRIMARY KEY (`activity_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- 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');
6

not very much, no

did you try the query i gave you?

7 
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
8

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; :frowning:

9

Moved to MySQL forum

10 
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
11

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.

12

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

13

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 ?