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.
Author |
Topic |
GGAS
Starting Member
3 Posts |
Posted - 2014-03-27 : 07:51:25
|
Hello to everybody. In order to ask my question on a SQL query I will use a simplified version of a table I was struggling with...-----------------------------------------------------------------¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARSKILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦MercedesKILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMWKILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIATBRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦MercedesBRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMWWEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦MercedesCAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIATNORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦BMWNORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦FIAT-----------------------------------------------------------------So my question is: how can I select only SHOPs which have no CARS variety (e.g. a variety =1)?In other words I am looking for a Query that would give me this outcome:¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARSWEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦MercedesCAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT ...because WEST HAMPSTEAD CARS, selling Mercedes only, has a CARS variety equal to 1 as well as Camdem Motors which sells only FIAT.I tried with this query:SELECT DISTINCT SHOP, CARSFROM CAR_SELLERSGROUP BY SHOP, CARSHAVING COUNT(CARS) = 1But it doesn`t work.In addition I also would like to know for example how to create a similar query for a different desired CARS variety (e.g. 2, 3...)waiting for an aswer I thank in advancePS This is my first post on this forum, if someone can also explain me how to do the tabs or attach images in the Post New Topic Message box, I would be very grateful... I wanted to attach a screen shot of the table but I was not able to and so, in order to post my question, I prepared a simplified version of the table plus I had to do the tabs/spaces manually with the symbol (¦) |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-03-27 : 07:55:20
|
[code]SELECT DISTINCT SHOP, CARSFROM CAR_SELLERSGROUP BY SHOP, CARSHAVING COUNT(CARS) = 1[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
GGAS
Starting Member
3 Posts |
Posted - 2014-03-27 : 09:32:54
|
thank you but... I was looking for a query to display both SHOP and CAR columns but select only SHOPs with a variety =1The results of the query I was looking for would look like this: ¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARSWEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦MercedesCAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIATin the way that all the shops with only one variety are displayed togheter with the type of car they sell. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-03-27 : 09:44:24
|
[code]SELECT C.SHOP, C.CARFROM( SELECT SHOP FROM CAR_SELLERS GROUP BY SHOP HAVING COUNT(CARS) = 1) DINNER JOIN CAR_SELLERS C ON D.SHOP = C.SHOP[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
GGAS
Starting Member
3 Posts |
Posted - 2014-03-31 : 05:50:39
|
thank you very much, this is very useful.I would have another question...If I have an extra column with the type of car (Diesel/Petrol), how can I get a similar query (1 variety of Car independently from the type)?¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS¦¦¦¦¦¦¦¦¦¦¦¦TypeKILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦DieselKILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW¦¦¦¦¦¦¦¦¦¦¦PetrolKILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW¦¦¦¦¦¦¦¦¦¦¦DieselKILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦PetrolBRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦PetrolBRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW¦¦¦¦¦¦¦¦¦¦¦PetrolWEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦DieselWEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦PetrolCAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦DieselCAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦PetrolNORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦BMW¦¦¦¦¦¦¦¦¦¦¦¦PetrolNORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦PetrolI mean how can I select the shop which sell only 1 variety of Car (brand) independently if Diesel of Petrol?In other words I am looking for a query with an outcome like this:WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦DieselWEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦PetrolCAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦DieselCAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦Petrolbecause West Hampstead Cars sells only Mercedes and Camdem Motors only Fiat.Thanks againG |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-03-31 : 06:24:04
|
[code]SELECT C.SHOP, C.CAR, C.[TYPE]FROM( SELECT SHOP FROM CAR_SELLERS GROUP BY SHOP HAVING COUNT(CARS) = 1) DINNER JOIN CAR_SELLERS C ON D.SHOP = C.SHOP[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|