Help with this query please - 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

General SQL Server Forums

New to SQL Server Programming

Help with this query please

Author

Topic

dinosaurhead
Starting Member

10 Posts

Posted - 2011-04-01 : 17:53:28

Big hello to you all! I have a scenario and I have to make a query for it. So far I have

SELECT * FROM service, customer WHERE service.serviceid IN (SELECT customer2service.serviceid FROM customer2service)

This shows all of the customers regardless of if they ordered a service or not. The services shown just display the serviceid 1 and the data for that one service. What it should show is this random data

INSERT INTO CUSTOMER2SERVICE VALUES (1, 1, 1);
INSERT INTO CUSTOMER2SERVICE VALUES (2, 2, 1);
INSERT INTO CUSTOMER2SERVICE VALUES (3, 2, 1);
INSERT INTO CUSTOMER2SERVICE VALUES (4, 4, 3);
INSERT INTO CUSTOMER2SERVICE VALUES (5, 5, 3);
INSERT INTO CUSTOMER2SERVICE VALUES (6, 5, 4);
INSERT INTO CUSTOMER2SERVICE VALUES (7, 5, 4);
INSERT INTO CUSTOMER2SERVICE VALUES (8, 8, 9);
INSERT INTO CUSTOMER2SERVICE VALUES (9, 9, 9);
INSERT INTO CUSTOMER2SERVICE VALUES (10, 9, 9);

The scenario
Create a query to show the service#, description and customer details of all the services purchased from each branch. Group your output by centre and order your output by revenue generated – highest first!

I don't necessarily need a direct answer, just a poke in the right direction if you would :)

If you need any more data please ask.

cheers

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-01 : 18:25:51

I'm guessing you have a table to show services purchased?
Looks like there's a lot of relevant info missing

select *
from customer c
join purchase p
on c.customer_id = p.customer_id
join service s
on p.service_id = s.service

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dinosaurhead
Starting Member

10 Posts

Posted - 2011-04-01 : 18:33:46

Thanks, yeh im quite rusty, only just started learning it tbh. Ill have a look at your suggestions :)

dinosaurhead
Starting Member

10 Posts

Posted - 2011-04-01 : 18:38:10

select *
from customer c
join customer2service p
on c.customerid = p.customerid
join service s
on p.serviceid = s.serviceid

looks alot like what i need to mess with. cheers

ill mess until i get it right based on that query :)

dinosaurhead
Starting Member

10 Posts

Posted - 2011-04-01 : 20:16:48

ok

i got


select customer.customerid, customer.fname, customer.lname, customer.address1, customer.datejoin, town.townname, county.countyname, service.servicename, service.cost, branch.branchid
from customer, town, county, customer2service, service, branch
where (customer.townid = town.townid) AND 
(town.countyid = county.countyid) AND 
(customer.customerid = customer2service.customerid) AND 
(service.serviceid = customer2service.serviceid) AND 
(branch.branchid = customer2service.branchid)

Only problem is I can't get branchid to link to the townid as its supposed to (and replace the branchid column with the relevant town name).

I also need to group it by the branchid but it isnt letting me "group by branchid" or "group by branch.branchid" any idea?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources