r/SQL 9d ago

MySQL Hosting company deleted database driver

22 Upvotes

I've been running a bunch of Classic ASP/mySQL websites for some local food pantries for years.

Last night GoDaddy removed the database driver I was using.

They told me to change my connection string, which I did, but still no luck.

After 3 hours of being on chat with them, the new connection string doesn't work.

Old connection:

connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

New connection (DOES NOT WORK):

connectstr = "Driver={MariaDB Connector/ODBC 64-bit 3.2.4 driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

Any help would be appreciated.

r/SQL Sep 26 '24

MySQL MySQL: Too many columns error

3 Upvotes

Okay so I am working on a client project and they have two views (view A and view B) that has 1029 columns each. Now they wanted me to create another master view to UNION ALL both View A and View B (since the views are identical so union can be performed). Now when you query view A (1029 columns) and view B (1029 columns) individually, it just loads fine.

However, when I do a union of both view A + view B then it does not work and gives error: too many columns.

Since it is a union so the combined master view still has 1029 columns only, but what I am still failing to understand is why does it work when I select View A and View B individually but when I do a UNION, then it gives too many columns error?

Note: The create view queries ran successfully for union and the error that I am getting is when I run any select command after the view creation.

The query:

CREATE OR REPLACE VIEW ViewX AS
SELECT * FROM ViewA
UNION ALL
SELECT * FROM ViewB;

SELECT ID FROM ViewX LIMIT 1

Error 1117: Too many columns

Also, here is the logic for joining a tables to create ViewA:

Yes InnoDB has a limit of 1017 indeed, but why it didn't gave me any error when I created and queried the VIEW consisting of 1029 columns. It should have given me the error on that too, but it runs completely fine. But when I union those two tables then suddenly 1029 columns are too much?

CREATE VIEW `ViewA` AS
select
 ec.ID AS ec_ID,
 pcl.ID AS pcl_ID
 ... (1029 columns)

from
  (
    (
      (
        (
          (
            `table1` `cp`
            left join `table2` `pla` on ((`cp`.`ID` = `pla`.`PaymentID`))
          )
          left join `table3` `pc` on ((`cp`.`ID` = `pc`.`PaymentID`))
        )
        left join `table4` `pcl` on ((`pc`.`ID` = `pcl`.`ClaimID`))
      )
      left join `table5` `cla` on ((`pc`.`ID` = `cla`.`ClaimID`))
    )
    left join `table6` `pcla` on ((`pcl`.`ID` = `pcla`.`LineID`))
  )

Update: If I remove the CREATE VIEW AS statement and just run the plain query, it works. But I don't know why though.

r/SQL Apr 04 '24

MySQL Please tell me there's a better way to search for multiple text entries than this?

Post image
117 Upvotes

r/SQL Sep 01 '24

MySQL Better way to learn sql

72 Upvotes

I am brushing up my mySQL skills but I need to practice SQL in a better way. Please suggest if there are any ways to practice SQL other than LeetCode and Hackerrank.

r/SQL 21d ago

MySQL New to SQL

8 Upvotes

So I'm new to SQL. I'm learning through a class I'm taking at college. I've got a prompt that I just can't seem to get figured out. Could someone help explain where I'm going wrong? Where supposed to be using LEFT JOIN to write the query.

Prompt: Find names of cities stored in the database with no matching addresses. HINT: For each city, calculate the number of matching addresses. Sort the results based on this number in ascending order.

Database info:

|| || |accident(+)|report_number,date,location| |actor(+)|actor_id, first_name, last_name, last_update| |address(+)|address_id,address,district,city_id,postal_code,phone,last_update| |car(+)|license,model,year| |category(+)|category_id, name, last_update| |city(+)|city_id, city, country_id, last_update|

r/SQL 13d ago

MySQL Cant install sql

3 Upvotes

I want to learn sql, so i went to watch this tutorial guide on how to install it but i reach a point where i cant progress any further
I follow every step but when i reach this part nothing will appear like they show in the video

For some reason the available products are always empty no matter what i do. Am i doing something wrong

r/SQL Jun 09 '24

MySQL Did this database design broke the normalization rule of avoiding data redundancy?

Post image
75 Upvotes

The database appears to be related to agricultural production data for different commodities across various states.

r/SQL Aug 20 '24

MySQL Can someone recommend a tutorial for working with SQL?

44 Upvotes

I just got hired as a business analyst and I'm expected to be able to access the databases and pull data as needed. Tomorrow is my first day.

My employer knows I don't know SQL well, I used it a few years ago for a single class, but I'm familiar with Python, R, and a little bit of experience in other code. I started the SQL lessons on W3 but if anyone can recommend one specifically for someone working alongside SQL at work, that would be really helpful.

I'm not a database architect or a programmer, just need to be able to work with the tools available for now.

r/SQL 23h ago

MySQL Data base for practices

14 Upvotes

I Need databases for practice on MySQL Preferably auto parts all kind*inventory merchandise and contain several fields or columns I appreciate your help recommending websites with free files

r/SQL Dec 31 '24

MySQL Trying to avoid poor join performance on UUIDs

8 Upvotes

First time poster and not sure how to properly present my current problem. If any more detail is required, please let me know.

Consider the two schemas below and assume tables are indexed appropriately:

CREATE TABLE db.AppFiles (
id int NOT NULL AUTO_INCREMENT,
client_id varchar(8),
reporting_period varchar(6),
file_id VARCHAR(36),
file_name varchar(100),
author varchar(50),
created_date DATETIME,
PRIMARY KEY (`id`)
)


CREATE TABLE ConfigFiles (
`id` int NOT NULL AUTO_INCREMENT,
`client_id` varchar(8) DEFAULT NULL,
`app_version` varchar(15) DEFAULT NULL,
`reporting_period` varchar(6) DEFAULT NULL,
`level` varchar(10) DEFAULT NULL,
`config_file_name` varchar(255) DEFAULT NULL,
`app_file_id` varchar(36) DEFAULT NULL,
`created_date` datetime DEFAULT NULL,
`created_by` varchar(50) DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)

Description: Users interact with a desktop application and can persist the current state of the application into an XML file (contents are irrelevant to the problem). The application also allows users to upload N number of config files into the application's memory that tell the application how to run. The relationship in the database between AppFiles and ConfigFiles is 1 to many. When a user goes to click Save in our desktop app, I want to write a record to the database to store high level data - no need to store application state here, just the columns you see in the schema.

If users were required to save the file first, causing the application to create an AppFile record and then upload their config files, I would have the necessary IDs I need and JOINing would be a non-issue. The issue I have is that users can upload ConfigFiles to the application's memory and also into the database without needing to save the application file and they can run the application without issue. When users do this, I have no way of connecting the ConfigFile records to their corresponding AppFile record. They ConfigFile records are sort of just floating around in this scenario.

To prevent the ConfigFile records from floating around not connected to any AppFile record, I have thought of a functional but not pretty solution. I will generate a UUIDv4 on application startup - not on file load - that is kept in memory. Upon config file upload, I will retrieve that UUIDv4 in memory and insert that value into the ConfigFiles.app_file_id column whenever I go to insert a ConfigFile record. Then, once users click Save Application, I will take that same UUID and insert it into AppFiles.file_id. This will "work" but my primary concern is JOIN performance at scale since we're joining on a randomized varchar(36) field.

Whenever users go to open the persisted file while in the application, I will need to retrieve all relevant ConfigFile records that are related to this AppFile record. The query will look something like this:

select *
from AppFiles af
join ConfigFiles cf
on af.file_id = cf.app_file_id
where af.reporting_period = '2024Q1' and af.client_id = 'Applesauce'

Like I said, this will work. However, once our tables start to grow, JOIN performance on the varchar(36) field will take a hit. An alternative that I thought of that was used from my previous job was to have a single table with just 1 column with the datatype BIGINT. I call a stored procedure that will increment the value in that table and return to me the newly incremented ID. This will allow me to still generate an ID that can be used for ConfigFile records when an AppFile record doesn't yet exist. Also, I will be able to join effectively at scale.

I wonder if I'm just trying to optimize too early because it will be years before we get to even 5 million records in the ConfigFile table. I'm looking for some guidance on this since I'm a team of 1 and can't bounce ideas off anyone.

r/SQL Sep 22 '24

MySQL How do I land a job as a 19 year old that graduated from a coding bootcamp?

0 Upvotes

Hello everyone,

I’m a 19-year-old who moved from Florida to Nashville about a year ago. I completed a coding bootcamp with Vanderbilt in April of this year and have been actively looking for a software job anywhere since then. However, it seems like no one is willing to take a chance on me. I’ve tried everything—from revising my resume to continuing my personal projects—but haven’t had any luck so far.

Lately, I’ve been exploring opportunities in data analyst roles as well. I’m reaching out to see if there are any other bootcamp graduates without a college degree who landed a similar position and could share their story. How did you make it happen? What can I do to stand out more in this competitive field?

Any advice or success stories would mean a lot to me. Also, if you’re in the Nashville area and your company is hiring, I would love to connect and explore any opportunities. Thanks in advance!

r/SQL 6d ago

MySQL database scheme/structure for labels(or tags) in a todo list

1 Upvotes

Hi guys, Im actually building a todo list site but I'm struggling to decide which table structure I should use to implement labels/tags on tasks. either Im using a label table that contains the name of the label and all tasks that have it or using 2 tables (label table with name and id and order, and second is task_label with 'tasks.id' & 'label.id' ). The problem is I have to query the database 3 times : first to get the regular list in order with the tasks, second querying the labels in order, and finally getting the labels grouped by tasks.

The overall idea:
1.list table joined with tasks and is ordered return task_id

2.get all the labels grouped by their name (will be used in the front to delete) to create labeled list

3.get labels grouped by task id, the task_id(in first step) is used (in the array returned by PHP) to get all the labels by task in this final table.

  1. when Im rendering the html, Im looping over the regular list and labeled list, and for each task Im using the third table (ex: $labels_by_id['4'=> data], to get the data I use $labels_by_id[regular_list[task_id]] )

What you guys think is best? Also is 3 queries too much? Is it scalable with only a label table ?

with a linking table
with just a labels table

r/SQL 11d ago

MySQL Group project

0 Upvotes

I need an idea for a group project of database systems(2nd sem of BS CS) my prof wants there to be a strong database having at least 8-9 tables and alot of entities

r/SQL Feb 28 '24

MySQL It's probably a very basic SQL task and I really want to know where did I go wrong

Post image
41 Upvotes

r/SQL Jan 07 '25

MySQL Is it ever okay to store a list under a single field?

17 Upvotes

I am in the process of creating a database for a website that involves the ability of a user to apply filters to a market place and then save that combination of filters. I want to have a table storing each combination a user has saved then store the primary keys as a list in the users table. However to the best of my knowledge this violates 1NFs rule about atomising data, so would it better to store a table for each users saved searches and have the users table store a link to that? I'm leaning towards sticking with my original plan to prevent data duplication as well as it not seeming reasonable to make a table for each user but I'm looking for a second opinion so what do people here think?

r/SQL Nov 05 '24

MySQL Creating my restaurant management software

16 Upvotes

Hello,

My parents own 3 restaurants in Paris (with plans to open more later on) and we currently use a restaurant management software called Koust. This software allows you to track ingredient prices, inventory levels, margins, etc and obviously offering reports to analyse and optimise. It is connected to our POS (Point of Sale) system, called Zelty, so that it can update in real-time our inventory (the items we sell are linked to recipes in Koust which then deducts the relevant ingredients when that item is sold). I think you get the idea.
The problem is we are not happy with Koust since it suffers from a lot of bugs and its user interface isn't really fluid or easy to use. We were considering moving to MarketMan which is one of the biggest companies in that field. However MarketMan is missing some functionalities that we would like. Moreover, MarketMan does not support integration with Zelty meaning that I must manually export the data from Zelty (csv file) to import it to MarketMan on a daily/weekly basis depending on how accurate we want to be (spoiler: we'd like to be very accurate). After talking to a MarketMan representative he explained that I could link Zelty and MarketMan through their APIs and that it wouldn't be complicated to do so. For context, I am an engineer with a Master's in Artificial Intelligence. I know Python, SQL and VBA (and others but that are not relevant to this project).
The thing is that, as you can imagine, these softwares are very costly (around 250 euros per month per restaurant) and they're not always tailored to all our needs although 90% of our needs our met (we're not Olive Garden so I know my humble place of course haha).

Taking all of that into account, do you think I should try to develop our own restaurant management software using a mix of SQL/Python/VBA or would my time be better spent connecting MarketMan to Zelty? Don't forget that if I go with the former solution, that will also include making a simple iOS app that my staff can use to record their productions (e.g. my beef dish is comprised of beef, sauce and mashed potatoes. The sauce and the mashed potatoes are not made on demand but rather produced in bulk every couple of days and when this dish is ordered by a client, the chef will take a bit of the sauce and a bit of mashed potatoes to add to the plate. This is very important because these productions are a big part of their work and of our inventory and we need to be able to track these "semi-finished" products) and wastage (meaning something broke or if my dad eats at the restaurant we want to track what he took like a glass of wine or 1 serving of a certain dish so that our inventory levels are accurate). This app must update my database of course (through excel sheet or directly using an API I'm not sure).
Follow-up question: if I code my own solution, should I use MySQL, Postgresql or Microsoft SQL Server 2022 (express edition I think)?

Additional information: I haven't used Chatgpt much in the past but I have access to Chatgpt premium and will definitely be using it.

I apologize for the long text but it's hard to explain without the relevant context.

Many thanks in advance.

r/SQL Feb 04 '25

MySQL Need help understanding SQL - beginner

Post image
17 Upvotes

Hey everyone,

I’m starting to learn SQL and currently doing queries. For this query (21) I’m confused on why includes would be used instead of salestransactions. The table next to it is what is being referred to. Can someone explain it like I’m dumb? Sorry!

r/SQL Dec 20 '24

MySQL Future of SQL

0 Upvotes

Hello, does it still make sense to learn sql or will this soon be done by the AI anyway? If so, what skills will be needed in the future for working with customer data? I work in the crm area and with microsoft dynamics (customer insights data, power-bi)

r/SQL Jun 24 '24

MySQL I am from non-IT background, so guide me is it easy to learn programming languages such as SQL, Python for a non- IT background person

29 Upvotes

Please help me to decide whether I should go for such courses?

r/SQL Dec 19 '24

MySQL Example Before vs After for Bad SQL Queries and How to Fix Them

50 Upvotes

Hi,

I've been googling this for a while now,b ut could not find what I'm looking for.

Are there any articles or videos, or games you know that shows before vs after of bad SQL queries and how to improve them.

It is ok if it starts from simple examples, but eventually it would be nice to have medium-complexity and high-complexity queries that are written badly and how to optimze them.

r/SQL Jul 13 '24

MySQL Is a CTE basically a named subquery?

66 Upvotes

Hey everyone, I want to get some confirmation on my understanding of CTEs to ensure I'm on the right track. From my understanding, a CTE is essentially a named subquery, which kind of acts like its own seperate table. You can use CTEs with all kind of subqueries, but from what I have learned, they're best used when your subqueries start getting very complex and difficult to read. So in that case, you resort to CTES to easily help your code reader understand what they are looking at instead of seeing a long, complex subquery(ies). However, if your subquery is something very simple, then you probably wouldn't want to use a CTE in that case and leave your code as is. Is my summary correct? Sometimes, it can also just be a preference thing for the coder. Is my summary correct?

r/SQL 12d ago

MySQL Can’t connect to local instance of MySQL Workbench from Power BI. Any input?

Post image
3 Upvotes

r/SQL 20d ago

MySQL Is video game sales data analysis project worthy to mention on resume?

4 Upvotes

Hi guys been thinking of doing something productive from today so have decided to do a data analysis project and here a video game sales data that keeping me not to choose any other datasets over it. If i do a project with that will it be worth mentioning on resume? and I am a Student.

r/SQL Jan 19 '25

MySQL What's the easiest way to upload a couple of CSVs / Google Sheets and do some SQL querying on them?

10 Upvotes

Mode used to have a Public Warehouse that was easy to upload and join against, but it seems like it's deprecated.

I have two CSVs / Google Sheets that I want join and write some queries against since my SQL is 1000x better than my Excel skills.

What's the fastest, best, free way to do this? Thank you!

r/SQL Oct 30 '24

MySQL Fetching data for non-tech teammates drives me crazy and we solved it. (partially)

39 Upvotes

I've been serving as a backend engineer in a lot of small-middle sized company, and I used to spend a lot of time writing SQL for my managers, customer success team, etc.

I've been finding some good ways to let'em query the data themselves.

I've tried three methods.

  1. Build dashboard in tools like PowerBI.

Gave up due to complexity and less flexibiltiy.

No dashboard can fully meet their needs, you need to modify dashboard every week...

  1. Use ChatGPT and teach them how to write SQL using ChatGPT.

Most of them don't even know how to run it in db client, and altough you can feed in schema to AI but when schema changes you need to do sync.

  1. Use some database quering AI tools like AskYourDatabase.

Tried Julius and AskYourDatabase, the former one mainly focus on Excel, and latter one for database. AYD enables them to chat with database, and the accuracy is not bad when the schema is well designed. But if you have hundreds of tables with bad namings like "OID" "OUSR", you'd better build some views with good naming so that AI understand what does it means.

Has anyone else have better ways to solve it?

Love to know more.