Jump to content

10 posts in this topic

Posted

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!

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

[quote name='articuno1au' timestamp='1364986313' post='595613958']
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.
[/quote]

Both fields are not primary keys

Share this post


Link to post
Share on other sites

Posted

I don't really like this answer, but it should work in theory..
[CODE]
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?
1 person likes this

Share this post


Link to post
Share on other sites

Posted

Yep, example resultset would be

[code]A Ford
B Ford
B Ford
A Honda
A Honda
B Honda
A Nissan
B Nissan[/code]

An example of what shouldn't be output is

[code]A Ford
A Nissan
B Nissan[/code]

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

So.. [CODE]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 >.<

Share this post


Link to post
Share on other sites

Posted

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

Thanks for your help.

Share this post


Link to post
Share on other sites

Posted

That will do it also >.<

You're welcome.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.