mysql select data from archive table if not in current table

Question

Not sure if the title explains my situation good enough but I've searched and found that I can do a WHERE NOT EXISTS clause, but I just can't seem to figure out exactly how it needs to be structured. Below is my situation.

I have a table agents, that contains agent login info etc.

I have another table listings, that is linked to the agents table with "agent_id".

Agents have their own logins and can add their listings etc, and the whole system pulls their listings based on who is logged in etc.

I ran into the situation where the super admin now wants to be able to delete an agent, but they want their old listings to remain archived and searchable etc in the database.

I created a table agents_archived, and when the super admin removes them it copies their row in agents to agents_archived and removes it from the agents table.

My issue is that I'm not sure how to have the query select their agent data if it doesn't exist in the current agents table. For instance here is a search query.

SELECT l.*, a.first_name, a.last_name FROM listings l 
LEFT JOIN agents a ON l.agent_id = a.id WHERE l.agent_id = '" . $_GET['qa'] . "' 
ORDER BY FIELD(l.status, 'active','inactive','under-contract','on-hold','title-issue','auction','closed','withdrawn'), l.street_name

I need it to pull the agents columns from the agents_archived table if it doesn't exist in the current.

I hope this makes sense.

Another `LEFT JOIN` to `agents_archived` and then `COALESCE()`. — PM 77-1, Jul 21 '14 at 23:28

score 0 · Accepted Answer · answered Jul 21 '14 at 23:41

I would use the following:

SELECT l.*,
       ifnull(a.first_name,a2.first_name) as first_name,
       ifnull(a.last_name,a2.last_name) as last_name,
       case when a.id is null then 'archived' else 'active' end as agent_status
  FROM listings l
  LEFT JOIN agents a
    ON l.agent_id = a.id
  left join agents_archived a2
    on l.agenct_id = a2.id
 WHERE l.agent_id = 'xyz'

This will show all of the agent's listings regardless of whether they are active or archived, and it will identify whether they are active or archived.

how can we accomplish above scenario on Mongo db – Ali Abbas Oct 02 '17 at 06:11 — Ali Abbas, Oct 02 '17 at 06:11

mysql select data from archive table if not in current table

1 Answers1