MYSQL select if exists (from different table)

Question

I currently have a search function and I need it to return values from a different table (if the value exists)

SELECT crm.lead_id, 
       crm.id, 
       url, 
       contact, 
       info, 
       status_id, 
       added, 
       last_edited, 
       callback_date, 
       user.user, 
       status.status, 
       crm_updates.status_info
  FROM crm, 
       user, 
       status, 
       crm_updates
 WHERE (url LIKE  '%$search%'
      OR contact LIKE  '%$search%'
      OR info LIKE  '%$search%'
      OR status_id LIKE  '%$search%'
      OR added LIKE  '%$search%'
      OR last_edited LIKE  '%$search%'
      OR callback_date LIKE  '%$search%'
      OR user.user LIKE  '%$search%')
  AND crm.lead_id = user.id
  AND status.id = crm.status_id
  AND crm_updates.crm_id = crm.id

I need the status_info from crm_updates and they're currently connected by crm_id (in crm_updates) and id (in crm)

Right now only the values that have a status_info are selected. I need all values to be selected and the status_info included if it exists.

Err · Accepted Answer · 2013-08-23 11:06:27Z

2

SELECT crm.lead_id, crm.id, url, contact, info, status_id, added, last_edited, callback_date, user.user, status.status, crm_updates.status_info
FROM crm
INNER JOIN user
ON crm.lead_id = user.id
INNER JOIN status
ON crm.status_id = status.id
LEFT JOIN crm_updates
ON crm.id = crm_updates.crm_id 
WHERE url LIKE  '%$search%'
OR contact LIKE  '%$search%'
OR info LIKE  '%$search%'
OR status_id LIKE  '%$search%'
OR added LIKE  '%$search%'
OR last_edited LIKE  '%$search%'
OR callback_date LIKE  '%$search%'
OR user.user LIKE  '%$search%'

Use 'LEFT JOIN crm_updates': so if there is no record for crm_updates NULL will be returned.

Tip: Dont put join clauses in the where part of your query

answered Aug 23, 2013 at 11:06

Err

2912 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JParadiso · Accepted Answer · 2013-08-23 11:02:47Z

0

how about changing the line

AND status.id = crm.status_id

to

AND status.id =* crm.status_id

answered Aug 23, 2013 at 11:02

JParadiso

492 bronze badges

Collectives™ on Stack Overflow

MYSQL select if exists (from different table)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related