SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Popoi: Jul 23, 2000

What you're getting is essentially all of approvals joined to approval_type joined to nothing. If you had other contracts with approvals in your listing, they'd be showing too, because you're not limiting which approvals you're looking at.

This will give you the list of approvals for a given contract:

code:

select at.type, at.approval_type_id ati, a.approval_date
from dmb_control.approval_types at 
left outer join dmb_control.approvals a 
      on at.approval_type_id=a.approval_type_id 
where a.contract_id = ?

This is basically what you had but without the (unnecessary?) join to contract.

If you want the approvals that are missing:

code:

select at.type, at.approval_type_id
from dmb_control.approval_types at 
where at.id not in 
(select approval_type_id from dmb_control.approvals where contract_id = ?)

(This may be the wrong syntax, but it should get the point across)

# ¿ Apr 28, 2009 23:23

Adbot: ADBOT LOVES YOU

# ¿ Jun 3, 2024 16:33

Popoi: Jul 23, 2000

I'm not familiar with the syntax, but my guess would be that the OR is overriding the earlier logic for the joins due to the way the logical operators are processed. Add some fields from sub2 and sub3 to the query to see if they are indeed identical rows, or if they're caused by bad joins.

If that turns out to be the case, all you should have to do is enclose the last two statements in parentheses so they're evaluated as X AND (Y OR Z) instead of (X AND Y) OR Z.

# ¿ May 21, 2009 19:44

Popoi: Jul 23, 2000

Fruit Smoothies posted:

Quick MySQL question.

I have a table with two timestamp restrictions which represent midnight (GMT) on certain days. The idea is to find out if a series of dates fall within these values.

For the sake of examples, I will use "days" rather than timestamps
code:
-------------------------------------------------
|	ID	|	start	|	Stop	|
-------------------------------------------------
|	1	|	1	|	6	|
-------------------------------------------------
|	2	|	5	|	5	|
-------------------------------------------------
|	3	|	11	|	13	|
-------------------------------------------------
|	4	|	7	|	8	|
-------------------------------------------------
|	5	|	1	|	30	|
-------------------------------------------------
In this example, row 1 corresponds to a restriction that runs from day 1 to day 6.

So imagine I want to find out which restrictions apply to an event starting on day 5, and ending on 10.

The basic logic with a higher-level language (such as PHP) is to simply loop through each day[5-10] and each restriction[1-5] and see if (start <= day >= stop)

Is there a better way with SQL that I am missing?

If I understand this right and you just need a list of which restrictions will apply at some point, and not when, you don't have to loop through every day, you can just check to see if the ranges intersect by checking whether the start or end date of either set of dates falls within the other.

So 4 conditions to check in the where clause:
Event Start <= Restriction Start <= Event End
Event Start <= Restriction End <= Event End
Restriction Start <= Event Start <= Restriction End
Restriction Start <= Event End <= Restriction End

I'm not sure if it's more efficient than how you'd do it in another language, but it's a way it can be done.

# ¿ Aug 10, 2011 21:42

Popoi: Jul 23, 2000

Yeah, you're looking for at least one of those to be true, so you'd use ORs.

# ¿ Aug 11, 2011 00:11

Popoi: Jul 23, 2000

Jethro posted:

Assuming that the starts and ends are ordered correctly, there are only two conditions that you need to check:
eventStart<=restrictionEnd AND eventEnd>=restrictionStart

I guess if you want to take the easy and sensible way out, sure.

# ¿ Aug 11, 2011 16:45

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'