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'

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

Rabbit Hill posted:

I have a question about MS Access 2016 query design.
How do I look for only partial matches between the "ISBNs" in Table 1 with the "ISBN" in Table 2?

Have you tried something like :

code:

SELECT ....
FROM [JLG Circ history w ISBNs] INNER JOIN [JLG w pricing] 
ON ([JLG Circ history w ISBNs].[ISBNs] Like '*' & [JLG w pricing].[ISBN] & '*')

If that doesn't work, you could try the old "cartesian join and filter" approach, but there's a strong possibility that MS Access performance on this approach will be dire...

code:

SELECT ....
FROM [JLG Circ history w ISBNs], [JLG w pricing] 
WHERE ([JLG Circ history w ISBNs].[ISBNs] Like '*' & [JLG w pricing].[ISBN] & '*')

# ¿ Apr 13, 2021 23:00

Adbot: ADBOT LOVES YOU

# ¿ May 17, 2024 15:19

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

Hi kiwid. If you were using T-SQL I'd probably suggest using PIVOT, but it looks like you're on MySQL. So I'd suggest starting with something like

code:

  SELECT order_id AS id
        ,MIN(IF(type='A','true',NULL)) AS has_type_a
        ,MIN(IF(type='B','true',NULL)) AS has_type_b
        ,MIN(IF(type='C','true',NULL)) AS has_type_c
    FROM instructions
GROUP BY order_id

# ¿ Jun 1, 2021 21:35

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

In the pivot approach, if you add something like:

code:

LEFT JOIN
(
   (SELECT order_id, instruction 
      FROM instructions) AS instr_rows
     PIVOT
   (MIN(instruction) 
    FOR instruction IN ([A],[B],[C])) AS instr_cols
)
ON instr_cols.order_id = orderlog.orderlog_nbr

You should have access to columns instr_cols.[A], instr_cols.[B] and instr_cols.[C] in your main query, which you can test for being null/non-null using a case statement in the same manner as your strip_test column.

# ¿ Jun 2, 2021 07:56

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

If you saved the package the wizard creates, and then skipped running it out of the wizard, you might be able to deploy it to the database. Then you could get the server to run it instead of your laptop. But a VM might be easier to organise.

# ¿ Sep 26, 2021 16:26

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

Hi kiwid. I don't think the designer is capable of coping with any query that contains a CTE. But as long as you're comfortable with working with the SQL directly, there's a more direct command you can use. Right-click the view, select "Script view as" -> "CREATE TO" -> "New query editor", and you'll get a script that will include the proper definition of the view.

# ¿ Oct 13, 2022 08:13

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

What is ljt.recid = t.sourcerecid doing in the first join? Is it excluding the row which has a non-blank invoice value?

# ¿ Nov 21, 2022 09:32

Adbot: ADBOT LOVES YOU

# ¿ May 17, 2024 15:19

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

kiwid posted:

It's from an SQL trace generated by software. So, probably an ORM.

You might be right, perhaps those nulls should have been column names. This is some really lovely legacy software we're talking about.

If it turned out to be handwritten, I might have guessed it was to make it easier to comment out any combination of filters without leaving an invalid WHERE clause

pre:

		NULL IS NULL --AND v.contract_nbr = 0

But since it's autogenerated, I'd guess that where you've ended up with "v.contract_nbr = 0", the system has a variable length list of filters which can be turned on or off. Starting with NULL IS NULL means that you don't have to restructure the WHERE clause when all the filters are off/list is length zero. It also means that you can prepend all of the clauses with " AND " when you're building the query, rather than remembering to special case the first clause off the list. I mean, it all smells like an expedient thing done in some decidedly hacky code, but in that context it makes sense.

# ¿ Feb 2, 2024 14:52

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