Jump to content



Photo

SQL Server 2005 Query help!

sql server 2005; sql

  • Please log in to reply
9 replies to this topic

#1 limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 03 April 2013 - 10:44

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!


#2 articuno1au

articuno1au

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 20-March 11
  • Location: Brisbane, Australia

Posted 03 April 2013 - 10:51

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.

#3 OP limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 03 April 2013 - 11:06

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

#4 articuno1au

articuno1au

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 20-March 11
  • Location: Brisbane, Australia

Posted 03 April 2013 - 11:17

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)
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?

#5 OP limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 03 April 2013 - 11:24

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.

#6 articuno1au

articuno1au

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 20-March 11
  • Location: Brisbane, Australia

Posted 03 April 2013 - 11:29

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

#7 OP limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 03 April 2013 - 11:43

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

#8 articuno1au

articuno1au

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 20-March 11
  • Location: Brisbane, Australia

Posted 03 April 2013 - 11:46

So..
SELECT DISTINCT(a.primarykey), a.carname, a.location

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

#9 OP limok

limok

    Neowinian

  • Joined: 20-May 04
  • Location: Manchester, UK
  • OS: Windows 8.1
  • Phone: Galaxy S4, iPhone 5

Posted 03 April 2013 - 11:48

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

Thanks for your help.

#10 articuno1au

articuno1au

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 20-March 11
  • Location: Brisbane, Australia

Posted 03 April 2013 - 11:50

That will do it also >.<

You're welcome.