kaash1 — 2010-06-19T20:36:35-04:00 — #1
I need a help with selecting the rows in a table depending on the status in previous table.
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.
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.
kaash1 — 2010-06-21T20:15:55-04:00 — #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:
Column1 Column2 Status
smith john 1
jack smith 0
julia rob 1
Can you please help me building following query:
I want to select all rows from Table 1 WHERE there is smith in column1 OR column2 AND status 1.
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;
- 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.
starlion — 2010-06-20T10:35:05-04:00 — #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
WHERE activity_username IN($user,$friends)
OR activity_object_id IN($user,$friends)
r937 — 2010-06-20T10:37:39-04:00 — #4
this will be my last guess at what you really want
FROM ( SELECT friend_username
WHERE username = 'sami1'
WHERE friend_username = 'sami1'
) AS f
ON user_activities.activity_username = f.username
OR user_activities.activity_object_id = f.username
OR activity_username = 'sami1'
OR activity_object_id = 'sami1'
kaash1 — 2010-06-20T08:12:41-04:00 — #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');
r937 — 2010-06-20T07:03:48-04:00 — #6
not very much, no
did you try the query i gave you?
r937 — 2010-06-19T21:20:20-04:00 — #7
ON table2.column1 = table1.column1
OR table2.column2 = table1.column1
OR table2.column1 = table1.column2
OR table2.column2 = table1.column2
WHERE table1.status = 1
kaash1 — 2010-06-20T09:15:01-04:00 — #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;
spikez — 2010-06-20T06:37:20-04:00 — #9
r937 — 2010-06-20T08:49:17-04:00 — #10
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
kaash1 — 2010-06-20T06:18:57-04:00 — #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.
r937 — 2010-06-20T06:09:56-04:00 — #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
kaash1 — 2010-06-20T05:56:00-04:00 — #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 ?