CODEWITHSUNDEEP
mysqlsql
Mawg
Mawg

Reputation: 40140

Why is this WHERE clause not working with Join?

I am quite new to SQL and this is rather a complex command :

SELECT users.name, 
       from_unixtime(activity.time_stamp, "%D %b %Y  %l:%i:%S"), 
      activity.activity, 
      activity.rfid_tag, 
      chemicals.description 
FROM activity 
JOIN chemicals ON chemicals.bar_code = activity.bar_code 
JOIN users ON users.badge_number=activity.badge_number 
WHERE  (activity="login") 
    OR (activity="logout") 
   OR (activity LIKE "Admin:%") 
   OR (activity="tag") 
   OR (activity="untag") 
   OR (activity LIKE "check out%") 
   OR (activity LIKE "pour%") 
   OR (activity="check out (unscheduled)") 
   OR (activity="not poured after checking out") 
   OR (activity LIKE "invalid:%") 
ORDER BY time_stamp DESC

When I run it, it returns only the rows which match the last OR of the WHERE (723 from 867). For instance, I do not see any rows where activity="login", but SELECT * FROM activity WHERE activity="login" does return some rows.

What am I doing wrong? (and if that should be "INNER JOIN")?

Update

I should have said this was working (with the complex WHERE) before I added the Join (and I am a total SQL beginner).

Upvotes: 1

Views: 148

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Please study the difference between INNER JOIN and LEFT JOIN.

From looking at your query and crystal ball-gazing based on the names and correlating to real world entities, here's what I can see:

When your activity is "pour", it may involve some chemical, which will join successfully on barcode. When it is "login", I doubt you'll get a successful (INNER) JOIN against the chemical table. As someone has mentioned, you really want to turn them into LEFT JOINs, e.g.

SELECT users.name, 
       from_unixtime(activity.time_stamp, "%D %b %Y  %l:%i:%S"), 
      activity.activity, 
      activity.rfid_tag, 
      chemicals.description 
FROM activity 
LEFT JOIN chemicals ON chemicals.bar_code = activity.bar_code 
LEFT JOIN users ON users.badge_number=activity.badge_number 
WHERE  (activity="login") 
   OR (activity="logout") 
   OR (activity LIKE "Admin:%") 
   OR (activity="tag") 
   OR (activity="untag") 
   OR (activity LIKE "check out%") 
   OR (activity LIKE "pour%") 
   OR (activity="check out (unscheduled)") 
   OR (activity="not poured after checking out") 
   OR (activity LIKE "invalid:%") 
ORDER BY time_stamp DESC

Upvotes: 4

Related Questions