August 12, 2010 at 10:46 am
Hello gurus...
I think I have a very simple question if I can get it into words that you can understand.
I have two tables SERVICES AND SALES
The services table has a list of all the services
The Sales Table lists all the sales and what service it was.
I can do a join and get the data on the services they sold.
But I need to return all the services even if the didn't sell anything in that service.
it would have the service name then NULLs for that service
Here is an example:
CREATE TABLE SERVICE
(
[SERVICE] nvarchar(2),
[DESC] nvarchar(20),
ROLL_INTO nvarchar(2)
)
CREATE TABLE Sales
(
[SERVICE] nvarchar(2),
FISCAL_YR CHAR(4),
R_UNITS float,
R_SALES float,
A_UNITS float,
A_SALES float,
I_UNITS float,
I_SALES float
)
INSERT INTO SERVICE VALUES ('01', 'NEW RUST PERSONAL',01)
INSERT INTO SERVICE VALUES ('02', 'Used RUST PERSONAL',02)
INSERT INTO SERVICE VALUES ('03', 'SOUND BARRIER',03)
INSERT INTO SERVICE VALUES ('04', 'BED RAILS',04)
INSERT INTO SERVICE VALUES ('05', 'VIP CAR DETL.',05)
INSERT INTO SERVICE VALUES ('06', 'PRE-CLEAN RUST',06)
INSERT INTO SERVICE VALUES ('07', 'SPRAY-ON BEDLINER',06)
INSERT INTO SERVICE VALUES ('08', 'Other',07)
INSERT INTO Sales VALUES ('01', '2008', 125, 2645, 9876, 4589, 2358, 2998)
INSERT INTO Sales VALUES ('01', '2008', 125, 2635, 9856, 4587, 1358, 1298)
INSERT INTO Sales VALUES ('02', '2008', 1025, 2645, 9853, 4589, 1358, 2198)
INSERT INTO Sales VALUES ('03', '2008', 1205, 2635, 9816, 4589, 1338, 298)
INSERT INTO Sales VALUES ('03', '2008', 125, 2635, 9856, 4589, 1368, 2198)
INSERT INTO Sales VALUES ('08', '2008', 1285, 2635, 9856, 4589, 1958, 298)
When I run this join SELECT Sales.SERVICE, Sales.FISCAL_YR, Sales.R_UNITS, Sales.R_SALES, Sales.A_UNITS, Sales.A_SALES, Sales.I_UNITS, Sales.I_SALES
FROM Sales INNER JOIN
SERVICE ON Sales.SERVICE = SERVICE.SERVICE
I get the following:
01200812526459876458923582998
01200812526359856458713581298
022008102526459853458913582198
03200812052635981645891338298
03200812526359856458913682198
08200812852635985645891958298
But I need it to show as
01200812526459876458923582998
01200812526359856458713581298
022008102526459853458913582198
03200812052635981645891338298
03200812526359856458913682198
08200812852635985645891958298
040000000
050000000
060000000
070000000
Because there were no sales for those services.
Thank you guys!!!
August 12, 2010 at 11:03 am
FROM Sales where Sales.SERVICE = SERVICE.SERVICE or sales.service is null
OR if it different table then
FROM Sales where Sales.SERVICE = SERVICE.SERVICE or SERVICE.service is null
----------
Ashish
August 12, 2010 at 11:17 am
This does not work. I get the same results as
SELECT Sales.SERVICE, Sales.FISCAL_YR, Sales.R_UNITS, Sales.R_SALES, Sales.A_UNITS, Sales.A_SALES, Sales.I_UNITS, Sales.I_SALES
FROM SERVICE INNER JOIN
Sales ON SERVICE.SERVICE = Sales.SERVICE
August 12, 2010 at 11:20 am
SELECT Sales.SERVICE, Sales.FISCAL_YR, Sales.R_UNITS, Sales.R_SALES, Sales.A_UNITS, Sales.A_SALES, Sales.I_UNITS, Sales.I_SALES
FROM Sales RIGHT OUTER JOIN
SERVICE ON Sales.SERVICE = SERVICE.SERVICE
August 12, 2010 at 11:24 am
You'll also need to get SERVICE from the Service table in the select list or it will also be NULL.
SELECT service.SERVICE, Sales.FISCAL_YR, Sales.R_UNITS, Sales.R_SALES, Sales.A_UNITS, Sales.A_SALES, Sales.I_UNITS, Sales.I_SALES
FROM Sales RIGHT OUTER JOIN
SERVICE ON Sales.SERVICE = SERVICE.SERVICE
August 12, 2010 at 11:32 am
Basic joins....
Key is that you need all Services - irrespective of whether or not there was a sale. In such cases, start with the table from which you need all values, and then work your way outward (or towards the LEFT in the LEFT OUTER JOIN).
SELECT SERVICE.Service, Sales.FISCAL_YR, Sales.R_UNITS, Sales.R_SALES, Sales.A_UNITS, Sales.A_SALES, Sales.I_UNITS, Sales.I_SALES
FROM SERVICE
LEFT OUTER JOIN Sales ON Sales.SERVICE = SERVICE.SERVICE
ORDER BY Sales.Fiscal_YR DESC
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
August 12, 2010 at 11:35 am
Awesome! That works..
How can I add a where clause? Once I add it I dont get the results I need.
WHERE Sales.FISCAL_YR = 2008
August 12, 2010 at 11:38 am
stewsterl 80804 (8/12/2010)
Awesome! That works..How can I add a where clause? Once I add it I dont get the results I need.
WHERE Sales.FISCAL_YR = 2008
If you put it into the WHERE clause, you make it another inner join. You need to add it to the JOIN:
SELECT service.SERVICE, Sales.FISCAL_YR, Sales.R_UNITS, Sales.R_SALES, Sales.A_UNITS, Sales.A_SALES, Sales.I_UNITS, Sales.I_SALES
FROM Sales RIGHT OUTER JOIN
SERVICE ON Sales.SERVICE = SERVICE.SERVICE AND Sales.FISCAL_YR = 2008
This will allow it to use the logic in the join and not filter out the places where it is null.
August 12, 2010 at 11:50 am
Re-reading that, I think I should be more specific -- an outer join returns all rows in common and the other rows in the table that is on the outer side (i.e. all of the service rows and all sales rows in common). If you put logic in the WHERE clause, this is reviewed and all of the rows from SERVICE not in Sales are excluded as they fail to meet the criteria. If you put the Sales logic in the join, then it excludes things on the Sales table only.
Note that if you have logic that affects the Service table, that COULD go into the WHERE clause and not cause problems.
August 12, 2010 at 2:43 pm
When I use this query in SQL server 2008 it works just fine...
But, when I use it it access It bombs out and says, "Join expression is not supported". And highlights the following AND Sales.FISCAL_YR = '2008'
How can I add a WHERE clause to satisfy an Access query?
Thank you so much for your help thus far...:-D
August 12, 2010 at 2:46 pm
As far as my helping is concerned, you're on your own in Access. I make it a point to stay as far away as I can. I am pretty sure that this is ANSI SQL I gave you, though, so if Access doesn't support it, well.....
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply