I need help with a SQL Query. SQL beginner here! [closed]

Question

I'm totally new to SQL and I've inherited a customer's server (the company that managed it no longer exists). They need me to run a SQL Query that shows their inventory sold for pots.

Specifically, how many of each item sold by each price code during a specific period of time. The guy who pulled this info used a program called Crystal Reports, but he said these reports could be run via SQL Query. This is what they were able to get from the guy (he created the database) before he retired:

Items sold contained in: ARInvoiceDetails & ARInvoiceHdr

ARInvoiceDetails is the main table.

ARInvoiceHdr contains invoice date.

ARInvoiceDetails contains item sold + price code (price code contained in ARPriceLevels).

Item description is obtained from ICItems.

Size is obtained from ICItemSizes (ICValidSizes).

Note: categories and subcategories are also contained in ICItems using ICCategories & ICSubCategories

ARInvoiceDetails contains:

Selling Price (ARDetSellingPrice) - my note: I don't see this one, but I do see a ARDetUnitPrice.

Price Code (ARDetPriceLevelID).

Quantity Sold (ARDetQty).

---

My issue(s) is that I can't figure out how to pull the data from both tables. I've done a little research into join clauses, but don't there need to be at least one column in common between the tables? I've been analyzing the output from Queries that show the column names from both tables. There are NO common column names. There is, however, a column in each table that has the same data but different column names.

ARInvoiceDetails - column name: ArDetPriceLevelID (price code)

ARInvoiceHdr - column name: ArInvPriceLevelID (price code)

According to the person that requested the report, price code will tell them the size and color of pots. Any help would be appreciated! I have access to the server and can answer any questions if this isn't enough information. I can't stress this enough - I'm totally new to SQL and my level of SQL knowledge is poor at best. I'm using Microsoft SQL Server Management Studio version 10.50.4042.0

Thanks!

Answer 1

The columns in common, do not have to have the same name, they just need to have the same data.

I've done a little research into join clauses, but don't there need to be at least one column in common between the tables? I've been analyzing the output from Queries that show the column names from both tables. There are NO common column names. There is, however, a column in each table that has the same data but different column names.

The easiest way to get started in SSMS (SQL Server Management Studio) is to use the it to get the basics of your query. Then fine tune it, be editing the code, it makes for you.

Go to the table, right click on the table name, select option "Select top 1000 rows. this will create the beginning of your query, and you can edit it until it gets you what you want. If this is a production system you might want to limit to the top 25 until you get your query working well.

SELECT TOP (25) [ARInvoiceDetails].[ArDetPriceLevelID]
, [ARInvoiceHdr].[ArInvPriceLevelID]
  FROM [ARInvoiceDetails]
  LEFT JOIN [ARInvoiceHdr]
  ON [ARInvoiceDetails].[ArDetPriceLevelID] = [ARInvoiceHdr].[ArInvPriceLevelID]

Answer 2

It sounds like you're making some headway Tom.

As you've no doubt realised from your work and the answers provided, joins in SQL are for matching on common data between tables. SQL server doesn't actually prevent you from joining on totally unrelated data, the only thing it enforces is that the type of data is the same between columns you are matching on. No doubt, you can appreciate that you could retrieve absolute nonsense data if you join incorrectly. So it's important to check your results against other sources and/or writing queries that allow you to check the results.

James' answer provides a query to get you started. But as you've mentioned, you would not have had any exposure to joins. This coverage of the types of joins from w3schools is a great foundation and it also allows you to check your understanding by providing some tests for you:

https://www.w3schools.com/sql/sql_join.asp

Answer 3

In my opinion, it's better to explore more about your data. How about joining the tables with these keys :

ARInvoiceDetails - column name: ArDetPriceLevelID (price code)

ARInvoiceHdr - column name: ArInvPriceLevelID (price code)

Then compare it with report from Crytal Report. Let us know if there are any additional info.