SQL query help needed...

[AndyCr15]

Ahh, cool, thanks. So I'm using MySQL.

 

[HungryHippos]

My code *should* work even so.

 

[h4rm0ny]

Sure. What are the different types? How do I know what mine is? How do they differ?

Thanks.

You'll probably know because you installed the software or started it up. If you installed MySQL then it's MySQL. If you clicked "SQL Server" icon, then it's SQL Server. And so forth. SQL is a standard language but different databases tend to have small variations between them. Like if it's MySQL you might create an AUTO INCREMENT column. Whereas in Postgres you'll use a SERIAL data type. Also, different versions have odd little gotchas. So in MySQL 5.6 you'll be able to do a GROUP BY on non-distinct rows whereas in 5.7 the same statement will break.

And in addition to the whole question of what will and wont work, is the question of how well or badly something will work. So basically, if you're going to ask for help with a SQL question start it with something like "I'm using MySQL 8" or whatever. It makes it a lot easier for people to help you.

 

[h4rm0ny]

Worse are people that don't confirm if my suggestion helped or not haha!

Yes - those who just disappear are even worse!

So to somewhat offset that I'll provide some feedback on your query.

Try this without the grouping or ordering bits? can add those later.

SELECT
a.id,
a.task,
a.measure
FROM TableA AS a
INNER JOIN TableB AS b
ON b.taskid = a.id
WHERE a.task LIKE '%Search%'
OR a.measure LIKE '%Search%'
OR b.subtask LIKE '%Search%'

The above should work (I'm just eyeballing it), but with two caveats. Firstly, you need a GROUP BY on the end or you could get multiple rows. "I already said that," you're about to reply. But there's more. Simply adding a GROUP BY a.id on the end wont work - you'll get an error. Or rather you wont in MySQL 5.6 and below but you will in 5.7 and above. The reason is because GROUP BY a.id will lead to undefined output because which of the rows from table b gets used for any given a.id = b.taskid is pseudo-random. On static data, the same query should never return a different result from run to run. You would need to add some aggregation functions like MAX(...) to your SELECT statement to make sure it was predictable which row results were selected.

That may be a little complicated in how I put it. Here's a simple rule: If you have fields in the SELECT statement, they must be part of the GROUP BY. Or if not, they must have an aggregate function wrapped around them to handle it.

So:

SELECT customer_id, sale_date FROM mySales GROUP BY customer_id;             //This is bad. You could get any sale_date.
SELECT customer_id, sale_date FROM mySales GROUP BY customer_id, sale_date;  //This is fine.
SELECT customer_id, MAX(sale_date) FROM mySales GROUP BY customer_id;        //This is also fine.

In MySQL 5.7 they made it a formal error. But that doesn't mean that not doing that before then wasn't also usually wrong.

 

[AndyCr15]

Cool. Good info.

 

[AndyCr15]

Try this without the grouping or ordering bits? can add those later.

SELECT
a.id,
a.task,
a.measure
FROM TableA AS a
INNER JOIN TableB AS b
ON b.taskid = a.id
WHERE a.task LIKE '%Search%'
OR a.measure LIKE '%Search%'
OR b.subtask LIKE '%Search%'

I have now finally used this and yes, it works great. Thanks.