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
 SQL query: select only fields with desired variety

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¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS

KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦BMW
NORTHERN 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¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS

WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM 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, CARS
FROM CAR_SELLERS
GROUP BY SHOP, CARS
HAVING COUNT(CARS) = 1

But 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 advance

PS 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, CARS
FROM CAR_SELLERS
GROUP BY SHOP, CARS
HAVING COUNT(CARS) = 1[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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 =1
The results of the query I was looking for would look like this:

¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS

WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT


in the way that all the shops with only one variety are displayed togheter with the type of car they sell.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-27 : 09:44:24
[code]
SELECT C.SHOP, C.CAR
FROM
(
SELECT SHOP
FROM CAR_SELLERS
GROUP BY SHOP
HAVING COUNT(CARS) = 1
) D
INNER JOIN CAR_SELLERS C ON D.SHOP = C.SHOP
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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¦¦¦¦¦¦¦¦¦¦¦¦Type

KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦Diesel
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW¦¦¦¦¦¦¦¦¦¦¦Petrol
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW¦¦¦¦¦¦¦¦¦¦¦Diesel
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦Petrol
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦Petrol
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW¦¦¦¦¦¦¦¦¦¦¦Petrol
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦Diesel
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦Petrol
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦Diesel
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦Petrol
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦BMW¦¦¦¦¦¦¦¦¦¦¦¦Petrol
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦Petrol

I 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¦¦¦¦¦¦¦¦Diesel
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes¦¦¦¦¦¦¦¦Petrol
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦Diesel
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT¦¦¦¦¦¦¦¦¦¦¦Petrol

because West Hampstead Cars sells only Mercedes and Camdem Motors only Fiat.

Thanks again
G
Go to Top of Page

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
) D
INNER JOIN CAR_SELLERS C ON D.SHOP = C.SHOP[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -