Join Tables that are NOT related

Question

Join Tables that are NOT related

moeessack123

Right there with Babe

Points: 764
More actions
March 22, 2016 at 2:10 am

#326000

Hi All
I was tasked with writing a report for my director
The report would be goods received vs goods sold for the month from a specific supplier
The problem i'm having is that there are NO tables related for sales and goods received.
What are the ways forward? What can i do to achieve this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

moeessack123 (3/22/2016)
Hi All
I was tasked with writing a report for my director
The report would be goods received vs goods sold for the month from a specific supplier
The problem i'm having is that there are NO tables related for sales and goods received.
What are the ways forward? What can i do to achieve this?

I understand your frustration having been in a similar situation on numerous occasions. Having said that, the scope of the report should get you most of the way there. You should already have the supplier for both goods received and sold, and the product id. Your only remaining metric is surely time, and that's the month. What's missing?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

moeessack123 Right there with Babe Points: 764 More actions · Answer 2

I have most of the info.i would've liked to run the report once.Looks like ill have to run the goods sold and then received and then filter out what i dont need

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

Tables do not have to be related in order to join them. SQL Server will happily join everything as long as you tell it how it identify a match.

Not sure what type of report your manager requests (could be by vendor/customer if many companies act in both roles, could be by region, could be by SKU - I am going to assume the latter).

I assume you have the query to get the sales per SKU for the current month, and the query to get the receipts per SKU for the current month. You can join those two together with an ON clause that defines a match. (If both use the same codes for the SKU, this is simple; if they use different lists you might need to use a translation table or do the join on item name or some other column or combination of columns instead).

If there may be items in one list that are not in the other, then you will need a FULL OUTER JOIN, and you should use COALESCE in the SELECT list to convert the NULL values to zero.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

moeessack123 Right there with Babe Points: 764 More actions · Answer 4

Thanks.I have sorted this out.after scouring through tables i found some keys that matched and worked from there.Took me quite a while but i sorted out