TSQL - JOIN cancels out my date filter
-
Well today was a crappy day, thinking I can hit and run this small web page and clean it up. I spent hours modeling this in SQL Manager finally getting it to work, and then used it my PHP code, it worked but fell apart. Maybe I have it backwards, and I should call "Project" first, and then join "commission_Summary" The first example works, but when I join the "project" table, it cancels out my "commission_summary.startup_check_date" filter. I get that or think that my JOIN is wrong, or that my JOIN is calling up all records and overriding my date filter. I tried different types of JOINs, but with no success.
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate
FROM commission_summary
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
ORDER BY commission_summary.startup_check_date DESCI tried adding one join at a time, and it worked or produced my desired results using just JOIN. I don't understand why this returns all the records, well I know it's JOIN related, just not sure why. My theory, is that the "Project" JOIN may have a duplicate column name. I wonder if I should of used a wildcard for testing, like SELECT * FROM. This returns all the records
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate,
project.Status,
project.Sales_no,
project.swan_job,
employee.Employee_ID,
employee.FName,
employee.LName,
customer.Customer_no, customer.LName,
customer.FName,
customer.City
FROM commission_summary
JOIN project on commission_summary.project_no = project.project_no
JOIN employee on commission_summary.employee_id = employee.Employee_Id
JOIN customer on customer.Customer_No = project.Customer_no
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
AND project.status = 'construction' OR project.status = 'finished'
ORDER BY commission_summary.startup_check_date DESCI'm confused by how this works, because it produces my desired results, of about 12 records in 2022.
SELECT
commissi -
Well today was a crappy day, thinking I can hit and run this small web page and clean it up. I spent hours modeling this in SQL Manager finally getting it to work, and then used it my PHP code, it worked but fell apart. Maybe I have it backwards, and I should call "Project" first, and then join "commission_Summary" The first example works, but when I join the "project" table, it cancels out my "commission_summary.startup_check_date" filter. I get that or think that my JOIN is wrong, or that my JOIN is calling up all records and overriding my date filter. I tried different types of JOINs, but with no success.
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate
FROM commission_summary
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
ORDER BY commission_summary.startup_check_date DESCI tried adding one join at a time, and it worked or produced my desired results using just JOIN. I don't understand why this returns all the records, well I know it's JOIN related, just not sure why. My theory, is that the "Project" JOIN may have a duplicate column name. I wonder if I should of used a wildcard for testing, like SELECT * FROM. This returns all the records
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate,
project.Status,
project.Sales_no,
project.swan_job,
employee.Employee_ID,
employee.FName,
employee.LName,
customer.Customer_no, customer.LName,
customer.FName,
customer.City
FROM commission_summary
JOIN project on commission_summary.project_no = project.project_no
JOIN employee on commission_summary.employee_id = employee.Employee_Id
JOIN customer on customer.Customer_No = project.Customer_no
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
AND project.status = 'construction' OR project.status = 'finished'
ORDER BY commission_summary.startup_check_date DESCI'm confused by how this works, because it produces my desired results, of about 12 records in 2022.
SELECT
commissiYou have an
ORin there without any parentheses, that might be the problem. Try this:AND (project.status = 'construction' OR project.status = 'finished')
Both sides of the
ORare using the same table column, so you could useINinsteadAND project.status IN ('construction', 'finished')
-
You have an
ORin there without any parentheses, that might be the problem. Try this:AND (project.status = 'construction' OR project.status = 'finished')
Both sides of the
ORare using the same table column, so you could useINinsteadAND project.status IN ('construction', 'finished')
That seems to be it. Wow! I didn't catch that yesterday. Thanks
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
You have an
ORin there without any parentheses, that might be the problem. Try this:AND (project.status = 'construction' OR project.status = 'finished')
Both sides of the
ORare using the same table column, so you could useINinsteadAND project.status IN ('construction', 'finished')