Multiple WHERE clauses in a Query

0 Kam N. · July 6, 2015
Hi everyone, I'm still fairly new to SQL, I was wondering what is the best way to to have two WHERE clauses in one query? I have a table that has columns time, name,shift. I want to say pick everything from the table between date 1 to take 10 and also want only ones where there shift is 'A' shift.


SELECT 
t_stamp,
name'
shift,
FROM Monthly
WHERE
t_stamp BETWEEN 'Date 1' AND 'Date 10' AND shift = 'A'

Or can i somehow use UNION

SELECT 
t_stamp,
name'
shift,
FROM Monthly
WHERE
t_stamp BETWEEN 'Date 1' AND 'Date 10'
UNION
SELECT 
t_stamp,
name'
shift,
FROM Monthly
WHERE
shift = 'A';

I'd appreciate the help. Thanks in advance 

Post a Reply

Replies

- page 1
Oldest  Newest  Rating
0 mike richardson · July 9, 2015
NP and good luck!! Hit me up if you need anything :)
0 Kam N. · July 8, 2015
I'll look into it. Might even see if that's something that can be done on the PLC side. Appreciate the help Mike! 
0 mike richardson · July 8, 2015
You should just be able to SELECT SUM(PartCycleTime) Then your WHERE time between START AND STOP etc etc
0 Kam N. · July 8, 2015
Perfect, works great!!!

if('{Value}' = 'All')
SELECT 
t_stamp,
name,
shift, 
FROM
Monthly
WHERE
t_stamp BETWEEN '{Day.date}' AND '{Day 1.date}';

Else 
SELECT 
t_stamp,
name,
shift, 
FROM
Monthly
WHERE
shift = '{Value}'  AND (t_stamp BETWEEN '{Day.date}' AND '{Day 1.date}');


0 Kam N. · July 8, 2015
This software is also connected to the 3 main PLCs that all the other PLCs talking to. I'm populating a custom table from the data coming from the PLC so the table is live. It works correctly now Thank you.
Now i have another question, how would you go about getting an average for a column since the data is dynamic and keeps getting added to? The picture should help.
Basically right now I'd like to show a average cycle time since the shift started at 7am this morning. how would you approach that?
Maybe keep up with the SUM of cycle time and divide it by number of parts that's been made so far?

/images/forum/upload/2015-07-08/78cfcf62224bae3b771ece589657ccea.jpg
0 mike richardson · July 8, 2015
Are you trying to also interact with PLC's??
0 mike richardson · July 8, 2015
So why not just do this:

Not sure on the syntax you are using but

If(dropdownbox.text == "All")
{
SELECT t_stamp,
name,
shift,

FROM Monthly
}
Else
{
SELECT
t_stamp,
name,
shift,

FROM Monthly
WHERE
shift = '{Value}' AND (t_stamp BETWEEN '{Day.date}' AND '{Day 1.date}');
}
0 Kam N. · July 8, 2015
I'm using this SCADA software called Ignition. I have made a database that has different columns, and t-stamp, name and shift are one of those . don't really need those that's why I haven't included them. I basically have made a drop down box on the screen that has 4 options "A-B-C-All" and the variable for that is called '{Value}' and also have two other drop down boxes for calendar so when i select a start date and end date and select a shift it queries that database and gives me a table of what i want to see. now the shift column in the database consists of only "A-B-C" those are the only shifts and there isn't such a thing as "All" so I'm trying to figure out how to include that to the query on top that way when i choose "All" it display shift A and B and C all together. does that make sense? the query above works perfectly fine when i select A or B or C, just shows blank for "All" since it looks into the shift column and can't find anything called "All". 
0 mike richardson · July 7, 2015
I need to know more about what you are doing. There are several options you can use. You can use an IF/ Else If In your code or CASE or IF in a stored procedure on your MySQL server.
0 Kam N. · July 7, 2015
wow, stupid me, worked just fine.:D

I gave shift a variable that way i could choose the shift now all left is finding a way to choose everything, so basically the shift column only has either A or B or C in it now everything works find if I pick A,B,C

SELECT 
t_stamp,
name,
shift, 

FROM Monthly
WHERE
shift = '{Value}'  AND (t_stamp BETWEEN '{Day.date}' AND '{Day 1.date}');

So when i choose All, the data base gets confused since there is no "All" option in the shift column. somehow say

 if shift = 'all' 
SELECT t_stamp,
name,
shift, 

FROM Monthly

which basically selects all 3 shifts.
  • 1
  • 2

SQL & Databases

107,177 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator