Inner Join Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inner Join Help

Author

Topic

drtduarte
Starting Member

12 Posts

Posted - 2009-07-16 : 11:47:55

Hello friends,

I'm not very comfortable with inner joins, but I think that's what I need for the following query that I'm trying to build.

I have two tables, FT(invoices) and ST(products).
Each product id "ST.REF" has it's own usual supplier name "ST.SUPPLIER"

I need to have a query to output all sales amount and quantity by supplier.

Any idea for such query?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 11:56:15

The best idea is to give more information.
table structure
example data
wanted output

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 12:59:39

something like


SELECT ST.SUPPLIER,SUM(FT.AmountField),SUM(FT.QTYField)
FROM ST
JOIN FT
ON FT.linkcol=ST.LinkCol
GROUP BY ST.Supplier

linkcol are columns by which two tables are related. also make sure you replace actual fieldnames in query

drtduarte
Starting Member

12 Posts

Posted - 2009-07-17 : 03:50:29

Hello there,

That was a great help...

After all I needed one more join as I wanted to filter the data by date and the date is on a different table than the amount and quantity (date is on table "ft" for invoice header, and amount, quantity on table "fi" for invoice lines).

So here you have my query:

SELECT ST.SUPPLIER,
SUM(FI.AmountField) as Total,
SUM(case when month(ft.fdata)=1 then FI.AmountField else 0 end) as January,
SUM(case when month(ft.fdata)=2 then FI.AmountField else 0 end) as February,
SUM(case when month(ft.fdata)=3 then FI.AmountField else 0 end) as March,
SUM(case when month(ft.fdata)=4 then FI.AmountField else 0 end) as April,
SUM(case when month(ft.fdata)=5 then FI.AmountField else 0 end) as May,
SUM(case when month(ft.fdata)=6 then FI.AmountField else 0 end) as June,
SUM(case when month(ft.fdata)=7 then FI.AmountField else 0 end) as July,
SUM(case when month(ft.fdata)=8 then FI.AmountField else 0 end) as August,
SUM(case when month(ft.fdata)=9 then FI.AmountField else 0 end) as September,
SUM(case when month(ft.fdata)=10 then FI.AmountField else 0 end) as October,
SUM(case when month(ft.fdata)=11 then FI.AmountField else 0 end) as November,
SUM(case when month(ft.fdata)=12 then FI.AmountField else 0 end) as December
FROM ST
JOIN FI
ON FI.REF=ST.REF
JOIN FT
ON ft.ftstamp=fi.ftstamp
where year(ft.fdata)=2009 and (ft.ndoc=1 or ft.ndoc=3 or ft.ndoc=4 or ft.ndoc=8)
GROUP BY ST.SUPPLIER
ORDER BY ST.SUPPLIER DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 14:08:57

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources