I am an infrequent SQL query writer. My knowledge of the language is pretty limited, but I'm pretty good at understanding the kinds of things it can do, and since I am an experienced programmer, I can specify my needs in fairly accurate technical terms, they just don't happen to be in the SQL programming language. That's where ChatGPT is very useful! #
I wrote out my query in pseudocode, ie English --#
I have a database with three tables, feeds and subscriptions and users. If a user subscribes to a feed there is a record in the subscriptions table connecting the two. I want a query that tells me which feeds have 0 subscribers.#
In the old days before ChatGPT I'd write a braintrust query, put it on my blog and the odds were pretty good that in 24 hours I'd have the answer, and would feel grateful and proud. That of course would be after putting in a few minutes trying to find the answer using Google and StackExchange. #
Now I just feed it to ChatGPT and it translates it for me, to good SQL code, and in ten minutes I have the query and the answer:#
SELECT feeds.feedurl, feeds.title FROM feeds LEFT JOIN subscriptions ON feeds.feedurl = subscriptions.feedurl GROUP BY feeds.feedurl, feeds.title HAVING COUNT(subscriptions.feedurl) = 0;#
I edited it a bit because it assumed field names where I used slightly different names. And I never said that fields had URLs that identify them, and we must use that, not the unique integer ID that each row also has. #
It worked. Turns out there are 872 feeds in feedland.org's database that have no subscribers. I'm looking for ways to make the feed reading part of FeedLand perform better, and time spent reading feeds no one is watching could be a place for savings. #
ChatGPT, you have made me a more confident and creative programmer. #
You know those obnoxious sites that pop up dialogs when they think you're about to leave, asking you to subscribe to their email newsletter? Well that won't do for Scripting News readers who are a discerning lot, very loyal, but that wouldn't last long if I did rude stuff like that. So here I am at the bottom of the page quietly encouraging you to sign up for the nightly email. It's got everything from the previous day on Scripting, plus the contents of the linkblog and who knows what else we'll get in there. People really love it. I wish I had done it sooner. And every email has an unsub link so if you want to get out, you can, easily -- no questions asked, and no follow-ups. Go ahead and do it, you won't be sorry! :-)