3

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!

closed as off-topic by Gaius, LowlyDBA, John Eisbrener, mustaccio, Laurenz Albe Oct 16 at 6:17

This question appears to be off-topic. The users who voted to close gave this specific reason:

If this question can be reworded to fit the rules in the help center, please edit the question.

2

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]
  • Thanks for the reply! I've been playing around in SSMS this morning and have some basics down like select top (25). What has me stumped is the join clauses at the moment. – TomR Oct 14 at 18:34
  • @TomR I updated to include a join in my sample code. If I understood your database design correctly this should work as as for you, Just need to add all the columns you want. – James Jenkins Oct 15 at 11:23
  • I will run that today and post any follow-up questions that come up. Thanks! – TomR Oct 15 at 13:43
  • in SSMS when I try to run that query, the "LEFT JOIN" is grayed out. Does that mean that something is missing or it's not working? – TomR Oct 15 at 23:40
1

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

  • 1
    Thank you for the link. I will definitely check it out. – TomR Oct 15 at 13:42
0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.