I have two tables namely TABLE1 and TABLE2 I am unable to understand how can I extract information from these two tables depending on a few conditions. Here is what I need basically:

I would like to get certain rows from the first TABLE1 depending on the column value. There is a column named timecreated in TABLE1. I would like to display all the rows in TABLE1 which have been created in the last 30 days (the time is in UNIX timestamp) only when the following conditions are met:

1. COLUMN1='\core\event\course_viewed' OR
2. COLUMN2='\core\event\enrol_instance_created' OR
3. COLUMN3='restore'

Now TABLE2 have columns named: ID, fullname and shortname. I need to display these as well The column ID in TABLE2 is the same value as a column named courseid in TABLE1. I would like to display those rows with fullname and shortname which corresponds to courseid which is named ID in TABLE1.

What have I tried? I tried the following but it seems not to work

SELECT TABLE1.*, TABLE2.* FROM TABLE1, TABLE2
WHERE 
(
TABLE1.COLUMN1 = '\core\event\course_viewed'
OR
TABLE1.COLUMN2 = '\core\event\enrol_instance_created'
OR
TABLE1.COLUMN3 = 'restore'
)
AND
TABLE2.COLUMN1 = TABLE1.COLUMN4

Sample Table 1

Sample Table 2

Desired Outcome table

CodePudding user response：

Two improvements which would make your query work.

• When search for \ you have to escape it i.e. If you want to search app\user, put app\\user in where clause.
• Use INNER JOIN if courseid always exists in table1 otherwise LEFT JOIN, which clears the idea that we're working mainly on table1.

Having said that, following should work in your case.

SELECT TABLE1.*, TABLE2.* 
FROM TABLE1
INNER JOIN TABLE2 ON TABLE2.id = TABLE1.courseid
WHERE TABLE1.timecreated >= UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY)
    AND (
        TABLE1.COLUMN1 = '\\core\\event\\course_viewed'
        OR TABLE1.COLUMN2 = '\\core\\event\\enrol_instance_created'
        OR TABLE1.COLUMN3 = 'restore'
    )