SQL Server 2005 Query help!

[limok]

Hi,

I'm trying to create a query whereby I can pick up data based on 2 fields in one table called stock. The scenario is that I have 2 locations, location a and location b and in each location I have a stock of cars. A unique car is based on location and car name.

My query should pick up car names that are only present in both location a and b. I've looked into self join queries, group by queries but can't simply find car names that are only present in both locations.

Thank you for help!

[articuno1au]

If it's a primary key, simply select where count == 2

Should do it for you >.> Depending on what you are trying to achieve there are better answers >.<

The obvious caveat to this idea is that if the car is listed twice in a single location, it'll return true also.

[limok]

If it's a primary key, simply select where count == 2

Should do it for you >.> Depending on what you are trying to achieve there are better answers >.<

The obvious caveat to this idea is that if the car is listed twice in a single location, it'll return true also.

Both fields are not primary keys

[articuno1au]

I don't really like this answer, but it should work in theory..

SELECT *
FROM table AS a, table AS b
Where (a.carname = b.carname and a.location != b.location)
[/CODE]

Depending on your usage, you may need to select distinct. I tested it and it works for me >.<

Sorry, thrashing around in the dark a bit here. Any more info you can provide?

[limok]

Yep, example resultset would be

A Ford
B Ford
B Ford
A Honda
A Honda
B Honda
A Nissan
B Nissan

An example of what shouldn't be output is

A Ford
A Nissan
B Nissan

The Ford here is the only record that isn't in both locations.

[articuno1au]

If I may, does my existing query not meet that requirement?

[limok]

Thanks it has but the result show each record duplicated three times

[articuno1au]

So..

SELECT DISTINCT(a.primarykey), a.carname, a.location[/CODE]

I have no idea how you're using it. You'll need to adapt it a little here >.<

[limok]

I've done a group by car name, location and that has looked like it has sorted it.

Thanks for your help.

[articuno1au]

That will do it also >.<

You're welcome.