|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need Help Please...ok i want to say first off i'm really bad at explaining problems so people
can understand them but i will try my hardest. i need to write a query for our apparel department that shows total revenue per retailer. this report will get compared to last years. i use 3 tables to create this query, slcdpm which is our customer information, armast which is the AR Header, and aritem which is all the line items that were invoiced. the tables are connected like this slcdpm.fcustno(customer#) = armast.fcustno and armast.fcinvoice = aritem. fcinvoice. i need all the customers from the slcdpm table whither they have purchased apparel or not and then i need the sum of thier purchases which is the aritem. so the armast is the bridge between the 2. i'm new to sql, self taught and been doing my job for about 8 months now, learning new stuff everyday. i just learned yesterday how to effectively use an outer join, which i was pretty proud of myself. anyway i thought an outer join would work for this but i don't know how to use it when i'm connecting three tables and i need all from slcdpm table and then the total for the ones that have ordered and then nothing/null for ones that haven't ordered anything. if you have any questions because i can't explain feel free to ask or email me at johnnynightr***@hotmail.com. thank you for your time. Without seeing the DDL for the tables involved, the below is only a guess:
SELECT t1.fcustno, ISNULL(SUM(t2.fcprice), 0) AS "Total spent" FROM slcdpm t1 LEFT JOIN ( SELECT a.fcustno, b.fcprice --price column? FROM armast a INNER JOIN aritem b ON a.fcinvoice = b.fcinvoice ) AS t2 ON t1.fcustno = t2.fcustno -- Show quote"JonB" wrote: > ok i want to say first off i'm really bad at explaining problems so people > can understand them but i will try my hardest. i need to write a query for > our apparel department that shows total revenue per retailer. this report > will get compared to last years. i use 3 tables to create this query, slcdpm > which is our customer information, armast which is the AR Header, and aritem > which is all the line items that were invoiced. the tables are connected like > this slcdpm.fcustno(customer#) = armast.fcustno and armast.fcinvoice = aritem. > fcinvoice. i need all the customers from the slcdpm table whither they have > purchased apparel or not and then i need the sum of thier purchases which is > the aritem. so the armast is the bridge between the 2. i'm new to sql, self > taught and been doing my job for about 8 months now, learning new stuff > everyday. i just learned yesterday how to effectively use an outer join, > which i was pretty proud of myself. anyway i thought an outer join would work > for this but i don't know how to use it when i'm connecting three tables and > i need all from slcdpm table and then the total for the ones that have > ordered and then nothing/null for ones that haven't ordered anything. if you > have any questions because i can't explain feel free to ask or email me at > johnnynightr***@hotmail.com. thank you for your time. > Oops! Forgot the GROUP BY
SELECT t1.fcustno, ISNULL(SUM(t2.fcprice), 0) AS "Total spent" FROM slcdpm t1 LEFT JOIN ( SELECT a.fcustno, b.fcprice --price column? FROM armast a INNER JOIN aritem b ON a.fcinvoice = b.fcinvoice ) AS t2 ON t1.fcustno = t2.fcustno GROUP BY t1.fcustno -- Show quote"Mark Williams" wrote: > Without seeing the DDL for the tables involved, the below is only a guess: > > SELECT t1.fcustno, > ISNULL(SUM(t2.fcprice), 0) AS "Total spent" > FROM slcdpm t1 LEFT JOIN > ( > SELECT a.fcustno, b.fcprice --price column? > FROM armast a INNER JOIN aritem b ON a.fcinvoice = b.fcinvoice > ) AS t2 > ON t1.fcustno = t2.fcustno > > > -- > > "JonB" wrote: > > > ok i want to say first off i'm really bad at explaining problems so people > > can understand them but i will try my hardest. i need to write a query for > > our apparel department that shows total revenue per retailer. this report > > will get compared to last years. i use 3 tables to create this query, slcdpm > > which is our customer information, armast which is the AR Header, and aritem > > which is all the line items that were invoiced. the tables are connected like > > this slcdpm.fcustno(customer#) = armast.fcustno and armast.fcinvoice = aritem. > > fcinvoice. i need all the customers from the slcdpm table whither they have > > purchased apparel or not and then i need the sum of thier purchases which is > > the aritem. so the armast is the bridge between the 2. i'm new to sql, self > > taught and been doing my job for about 8 months now, learning new stuff > > everyday. i just learned yesterday how to effectively use an outer join, > > which i was pretty proud of myself. anyway i thought an outer join would work > > for this but i don't know how to use it when i'm connecting three tables and > > i need all from slcdpm table and then the total for the ones that have > > ordered and then nothing/null for ones that haven't ordered anything. if you > > have any questions because i can't explain feel free to ask or email me at > > johnnynightr***@hotmail.com. thank you for your time. > > so i use somewhat of a subquery to join armast and aritem and use that as a
seperate table, and connect slcdpm to that? it makes sense i just wanted to make sure i was following it correctly. thanks for the reply Mark Williams wrote: Show quote >Oops! Forgot the GROUP BY > >SELECT t1.fcustno, >ISNULL(SUM(t2.fcprice), 0) AS "Total spent" >FROM slcdpm t1 LEFT JOIN >( >SELECT a.fcustno, b.fcprice --price column? >FROM armast a INNER JOIN aritem b ON a.fcinvoice = b.fcinvoice >) AS t2 >ON t1.fcustno = t2.fcustno >GROUP BY t1.fcustno > >> Without seeing the DDL for the tables involved, the below is only a guess: it kinda works, but i think the part that doesn't work is something in my
where clause that i can look into. but i am closer thank you. sorry i lied:( i just double checked, i saw it had more records than earlier
today but when i checked i noticed it's because some shops have placed orders today, i guess i was so excited to see it work that i didn't double check, i do that from time to time. JonB wrote: Show quote >it kinda works, but i think the part that doesn't work is something in my >where clause that i can look into. but i am closer thank you. You should create view to make working with multiple tables easier and
to improve performance. Having just started SQL myself, also self-taught on-the-job, I highly reccommend Murchach's SQL for SQL Server - it has a lot of simple examples and clear explanations for basic SQL statements. http://www.murach.com/books/sqls/index.htm Also, if you want an answer that will actually be useful and accurate, as mentioned, post the DDL. http://www.aspfaq.com/etiquette.asp?id=5006 ~mahalie JonB wrote: Show quote > ok i want to say first off i'm really bad at explaining problems so people > can understand them but i will try my hardest. i need to write a query for > our apparel department that shows total revenue per retailer. this report > will get compared to last years. i use 3 tables to create this query, slcdpm > which is our customer information, armast which is the AR Header, and aritem > which is all the line items that were invoiced. the tables are connected like > this slcdpm.fcustno(customer#) = armast.fcustno and armast.fcinvoice = aritem. > fcinvoice. i need all the customers from the slcdpm table whither they have > purchased apparel or not and then i need the sum of thier purchases which is > the aritem. so the armast is the bridge between the 2. i'm new to sql, self > taught and been doing my job for about 8 months now, learning new stuff > everyday. i just learned yesterday how to effectively use an outer join, > which i was pretty proud of myself. anyway i thought an outer join would work > for this but i don't know how to use it when i'm connecting three tables and > i need all from slcdpm table and then the total for the ones that have > ordered and then nothing/null for ones that haven't ordered anything. if you > have any questions because i can't explain feel free to ask or email me at > johnnynightr***@hotmail.com. thank you for your time. i'll look into a view, i totally forgot about that. i used them alot on my
more complex queries, like this one, i guess i over looked it. thanks for your reply. also you like the murach books? i have a vb one and i hated it. it could be cause it was for an online class for school and i didn't seem like the book and class criteria matched up. but i will look into it, i use the sql bible, it's pretty good. mahalie wrote: Show quote >You should create view to make working with multiple tables easier and >to improve performance. Having just started SQL myself, also >self-taught on-the-job, I highly reccommend Murchach's SQL for SQL >Server - it has a lot of simple examples and clear explanations for >basic SQL statements. http://www.murach.com/books/sqls/index.htm > >Also, if you want an answer that will actually be useful and accurate, >as mentioned, post the DDL. http://www.aspfaq.com/etiquette.asp?id=5006 > >~mahalie > >> ok i want to say first off i'm really bad at explaining problems so people >> can understand them but i will try my hardest. i need to write a query for >[quoted text clipped - 14 lines] >> have any questions because i can't explain feel free to ask or email me at >> johnnynightr***@hotmail.com. thank you for your time. I liked Murach's book for SQL because it was really straight forward -
you can look up a statement, type of join, how to create a view, etc and it gives you a pretty basic and clear example, no matter where you are in the book. That is to say, each topic can be read independently so it's a great reference to have on hand. I've recently been reading Wrox's Beginning Transact-SQL and it introduces more advanced concepts and will give me a fuller understanding overall, but each section relies upon knowlede of the last, so it doesn't function well as a reference book. A couple months ago I had to figure out how to port data from one server to another and into the backend of a commercial accounting program - and I had pretty much zero database experience. I read an embarrassingly simplistic Webmonkey tutorial "creating your first database". Murach's book and this list were both indispensable tools for meeting my deadline. Mahalie JonB wrote: Show quote > i'll look into a view, i totally forgot about that. i used them alot on my > more complex queries, like this one, i guess i over looked it. thanks for > your reply. > > also you like the murach books? i have a vb one and i hated it. it could be > cause it was for an online class for school and i didn't seem like the book > and class criteria matched up. but i will look into it, i use the sql bible, > it's pretty good. > > mahalie wrote: > >You should create view to make working with multiple tables easier and > >to improve performance. Having just started SQL myself, also > >self-taught on-the-job, I highly reccommend Murchach's SQL for SQL > >Server - it has a lot of simple examples and clear explanations for > >basic SQL statements. http://www.murach.com/books/sqls/index.htm > > > >Also, if you want an answer that will actually be useful and accurate, > >as mentioned, post the DDL. http://www.aspfaq.com/etiquette.asp?id=5006 > > > >~mahalie > > > >> ok i want to say first off i'm really bad at explaining problems so people > >> can understand them but i will try my hardest. i need to write a query for > >[quoted text clipped - 14 lines] > >> have any questions because i can't explain feel free to ask or email me at > >> johnnynightr***@hotmail.com. thank you for your time. ok i created a view and i get the same results as putting in the subquery for
a table. so i got to thinking and i realized the problem. i'm connecting the 2 with fcustno, customer number, if the retailer hasn't ordered anything yet it won't be in the armast table so it can't pull in that information since slcdpm.fcustno = t2.fcustno is not true. am i right?  |
|||||||||||||||||||||||
Other interesting topics