PHP/MySQL Query use of AND

Question

I have the following query that works great except it is too restrictive:

SELECT sr.AssignedTech
     , i.image 
  FROM ScheduleRequest sr
     , images i
 WHERE JobStatus != 0 
   AND ScheduleDateExact IS NOT NULL 
   AND ID IS NOT NULL 
   AND RecordType != 2 
   AND JobStatus != 1 
   AND sr.AssignedTech = i.empno

If there is no match between ScheduleRequest.AssignedTech=images.empno then the record is not selected.

How do I display records even where this specific condition is not met? I tried to change it to OR but that did not work.

create a second query that's the opposite of the conditions? — treyBake
– treyBake, Commented Jun 7, 2018 at 14:05
We stopped writing queries this way ca. 1995. Use explicit JOIN syntax instead. Then switch from your existing INNER JOIN to a [LEFT] OUTER JOIN, and move all the conditions on the joined table from the WHERE clause to the ON clause. Job done. — Strawberry
– Strawberry, Commented Jun 7, 2018 at 14:08

michaelze · Accepted Answer · 2018-06-07 14:59:43Z

1

You can use a left join in order to get all the AssignedTech records including their image if they have one:

SELECT sr.AssignedTech
     , i.image 
  FROM ScheduleRequest sr
LEFT JOIN images i on (sr.AssignedTech = i.empno)
 WHERE JobStatus != 0 
   AND ScheduleDateExact IS NOT NULL 
   AND ID IS NOT NULL 
   AND RecordType != 2 
   AND JobStatus != 1

answered Jun 7, 2018 at 14:59

michaelze

914 bronze badges

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

Comments

Tharun Vempati · Accepted Answer · 2018-06-07 14:13:43Z

0

Instead of using an Inner join, may be you could use outer joins to fetch the data . if you need to fetch the whole data from the left table and only the rows that satisfy the condition from the right table, you could use left outer join. and vice versa from the right outer join. If you need everything from both the tables you could use full outer join. Hope this information is helpful. :)

answered Jun 7, 2018 at 14:13

Tharun Vempati

378 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL Query use of AND

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