Basic SQL Help

[THar01]

I know this pertains more to SQL, but the reason I'm posting this question is because of the data export size limitation in Power BI (150K rows max for Excel formats). I have two SQL tables I pull from into a Desktop BI report. Works fine in the report, but our account folks need to regulary export 200K - 300K plus rows of data after filtering the report. The output of the SQL is limited by a WHERE clause on the date_key column in the SALES table. I can get this basic statement to work fine (note: I'm testing the query in SSMS so it may look a bit odd):

SELECT [date_key]
,[prod_id]
,[dept_id]
,[cust_key]
,[sales_f_invoice_number]
,[sales_f_total_sales_amount]
,[sales_f_total_cost_amount]
,[sales_f_gross_margin_amount]
,[INVENTORY_LOC_KEY]
,[comp_f_fiscal_month]
FROM [VADATA].[VWBI].[Sales]
WHERE [date_key] >= '2022-01-01';

The query works as expected and I get a little over 262K rows filtered by the WHERE clause. However, I need to pull a column from another table, the [VADATA].[VWBI].[PRODUCT] table. The sales and product tables have common keys, the dept id column in the PRODUCT table, and the Department ID column in the SALES table. The column I need from the PRODUCT table is 'Category Description'. I know there has to be a JOIN of some kind and perhaps a sub-SELECT statment, but I'm not sure - my sql isn't exactly stellar. I've tried multiple iterations of statements to get this done but am met with various syntax and other errors. 

What I need is for the results of the final query to contain the results from the query listed above, but also add the Category Description column from the PRODUCT table for each related row of data in the SALES table - each sales transaction has a Department ID that corresponds to the Dept ID column in the PRODUCT table. It's kind of like a VLOOKUP in Excel or a DAX LOOKUPVALUE function, just in SQL.  Would appreciate any pointers anyone has, or if there's a more appropriate forum elsewhere online.

Thank you.

[johncolley · Accepted solution]

Hi @THar01 ,

Try something like:

SELECT Sales.[date_key]
,Sales.[prod_id]
,Sales.[dept_id]
,Sales.[cust_key]
,Sales.[sales_f_invoice_number]
,Sales.[sales_f_total_sales_amount]
,Sales.[sales_f_total_cost_amount]
,Sales.[sales_f_gross_margin_amount]
,Sales.[INVENTORY_LOC_KEY]
,Sales.[comp_f_fiscal_month]

,Product.[categorydescription]
FROM [VADATA].[VWBI].[Sales] AS Sales

LEFT JOIN [VADATA].[VWBI].[Product] AS Product

ON Sales.DepartmentID = Product.DeptID

WHERE [date_key] >= '2022-01-01';

Here is a good source of information that will help you with SQL: https://www.w3schools.com/sql/sql_join_left.asp

If this answer solves your problem please mark it as a solution!

[THar01]

At first I was getting various column not found and identifier not bound errors. Couple of things I found:

1. The needed column in the Sales table was shown as Department ID in PBI. The table comes from a view that someone created at some point. SSMS shows the actual column name as dept_id. I suspect the column was renamed in the view.

2. Seems the aliases were causing the 'identifier not bound' errors. So I removed them.

After some tweaking, I arrived at the code below which worked.  

SELECT Sales.[date_key]
,Sales.[prod_id]
,Sales.[dept_id]
,Sales.[cust_key]
,Sales.[sales_f_invoice_number]
,Sales.[sales_f_total_sales_amount]
,Sales.[sales_f_total_cost_amount]
,Sales.[sales_f_gross_margin_amount]
,Sales.[INVENTORY_LOC_KEY]
,Sales.[comp_f_fiscal_month]
,Product.[Category Description]
FROM [VADATA].[VWBI].[Sales]

LEFT JOIN [VADATA].[VWBI].[PRODUCT]

ON [VADATA].[VWBI].[Sales].[dept_id] = [VADATA].[VWBI].[PRODUCT].[Dept ID]

WHERE [date_key] >= '2022-01-01';

While the SQL you suggested didn't quite work (because I had incorrect info), it did point me in the right direction. I think we can call that a win and a solution. Thank you very much.