BF: If you were a database, I would join with you.
Me: Left or right?
BF: Inner.

(there are no words)

My boss typed up a GitLab ticket that looked like it took at least an hour to make. Screenshots, diagrams, how to reproduce the bug, dreams, hopes, desires, dinner recipes, marriage advice, how to diversify a portfolio. Honestly, the whole 9 yards.

The bug fix was changing a SQL Inner Join to a Left Join. 10 seconds.

Watching Netflix alone when gf asks if she can join, or preferrably inner join.

I love this girl to death 😍

Today, for fun, I wrote prime number generation upto 1000 using pure single MySQL query.

No already created tables, no procedures, no variables. Just pure SQL using derived tables.

So does this mean that pure SQL statements do not have the halting problem?

Putting an EXPLAIN over the query I could see how MySQL guessed that the total number of calculations would be 1000*1000 even before executing the query in itself and this is amazing ♥️

I have attached a screenshot of the query and if you are curious, I have also left below the plain text.

PS this was a SQL problem in Hackerrank.

MySQL query:

select group_concat(primeNumber SEPARATOR '&') from
(select numberTable.number as primeNumber from

(select cast((concat(tens, units, hundreds)+1) as UNSIGNED) as number from

(select 0 as units union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) unitsTable,
(select 0 as tens union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) tensTable,
(select 0 as hundreds union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) hundredsTable order by number) numberTable

inner join

(select cast((concat(tens, units, hundreds)+1) as UNSIGNED) as divisor from

(select 0 as units union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) unitsTable,
(select 0 as tens union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) tensTable,
(select 0 as hundreds union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) hundredsTable order by divisor) divisorTable

on (divisorTable.divisor<=numberTable.number and divisorTable.divisor!=1)
where numberTable.number%divisorTable.divisor=0
group by numberTable.number having count(*)<=1 order by numberTable.number) resultTable;

Oh hell yeah. I just spent a good few hours optimizing an ok mySQL query and made it so much better and faster. Inner join ftw!!

When the guy with his master's who's job you were told you weren't qualified for performed the following all in one query:
1. A massive many to many join on a 4 million row table.....to itself on in inner query through a linked server.
2. Decides to try and join this massive inner query (see step 1) to another table on a second many to many join.
3. Writes a function for month. Yes instead of month(literallyadate)

Then this guy emails me to ask if I can optimize it because we yelled at him for trying to insert 216 GB of data into a table (again on a massive many to many joined disaster). We told him if a query was taking more than 40 minutes we needed to see it.

I regret saying that now...should have just bought more space ;)

Why is school such a bullfuck, we've learned SQL basics and I've used SQL a little so I thought I won't have a problem. WELL FUCKING HELL WAS I WRONG. Joining 4 tables together with inner join WTF who the fuck uses that, why the fuck do I need to know this, WHY THE FUCK IN A TEST WHEN WE HAVEN'T LEARNED SUCH BULLSHIT. Well how about adding a foreign key to a table that doesnt need one. Well ok have fun with a key that does absolutely nothing and on top of all those convoluted tasks the texts are a mess, they give unnecessary information with grammar of a 9 year old and the pictures are not even readable. They are fucking hieroglyphs.
Fuck school.
Gonna do it by myself at the end anyway.
Fuck everything.

Well SQL is literally love...

So back to the stories of the gentleman with his master's degree who's job I wasn't qualified for. Hope you all enjoy this gem I found in his stored procedure.

Select distinct *
From(
Select * from a inner join b on a.id=b.id
Union
Select * from a full join b on a.id=b.id
Where b.id is null
)

An inner join unioned to a full join where you exclude null values in right table creates a.....left join you fucking idiot!

Are sql joins a bad practice? :o
I recently did some work on a page for a site ive never worked on cause my boss told me to. So they recently added product detail video urls to a table that has a relationship to the products table. The existing code was querying for the products on that said page and then during the loop that was outputting the products ,there was another query for getting the url for the current iteration/product. Told my coworker that this imo was pretty inefficient way to do it and switched it to a join and did 1 query then output that but his words were "The way it is now maybe ineffecient in your opinion but it works. Also combining inner joins with left or right is not a good practice. If the data is changed upstream the entire query would need to be redone to accommodate the change". Mind you that they query views a lot which are all made from queries that use joins and I'm also pretty sure these views were written by someone who used to be here because these guys are not good at sql or at least that's what there queries show. I'm at the point now where I'm realizing that my boss and this other guy don't give a fuck about efficiency or doing things the right way they just want it "to work". So this coworker changed my query back to the way it was because he said it broke the shopping cart even though that was already broken when I started... What is life? Maybe I'm the stupid one?

I am happy today cause I manage to write a query in which two table have inner join and with third left .. haha...

I mean I was thinking of handling that situation with foreach.. But managed to do it in query by myself :)

Just hoping that query won;t break for different scenarios. But let just be me happy while it last .. I mean my client make some test

I tried to make some SP using the syntax and formatting that visual studio outputs when making a SSRS report. I thought it was nice.

It formats the code in a standard way and transforms stuff like "join" to "INNER JOIN" and "left join" to "LEFT OUTER JOIN ".

When the team reviewed the code they were like WTF?! This syntax is horrible, it can't be understood. You did this?

*Me with my red face*...

I just said. You know what? I am going to go back to the old school syntax if you prefer. I just thought it was better.

Yeah... You really should go back to old school syntax.

---

Keep in mind that the old school syntax is annoying to me... No formatting at all and basic instructions are not in larger upper case.

Anyway, I thought it was nice tbh. I still think it is. And it is definitely better to me in some way.

What bothers me most is that they want to improve their coding. They say they want to be more standard and it seems every time you want to make a change it's not a good idea because "everything is already written that way". And when you don't make a change, "you should have change it"... Well sorry I was just copying the old style.

Anyways , it's not that important. I do get their point. Sometimes.

I wondered what they were doing.. after a while they called me over.. they were trying to inner join 3 tables and struggling.. FFS.

!Rant .. I need some quick help and didnt know where to go.. so fellow ranters please help...

So I have created an sql trigger which is supposed to add a kill to a doctor whenever one of his patients changes state to "killed" . But I dont know how to just get the one row that is updated. As it looks now : The doctor get patientKilled ++ for every patient he ever had before as well... How can I solve this ?

USE [AD17_Hospital]
GO
/****** Object: Trigger [dbo].[PatientKilled] Script Date: 2017-10-21 19:51:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[PatientKilled] ON [dbo].[Patient]
AFTER UPDATE
AS
BEGIN
declare

SET NOCOUNT ON;
UPDATE Doctor set PatientsKilled+=1

FROM Doctor d
INNER JOIN inserted p
ON d.DoctorId= p.DoctorId
where p.PatientState='Killed'
END

Hi people, I need some help.
First, stack , Golang/gorm(ORM)
I need doing a simple sum(amount) , I have a " db.Table("orders").Select("sum(amount) as Amount, merchants.name").Joins("inner join merchants on merchants.id = orders.merchant_id AND merchants.name = ? GROUP BY merchants.name", key).Scan(&total)

total is a struct type .

Are there any smell and I don't see ....?