Need help with special SELECT query

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

Need help with special SELECT query

Author

Topic

zouave
Starting Member

3 Posts

Posted - 2012-01-29 : 00:22:50

I have 2 tables.
table A has 27 bookings with dates and locations
table B has locations with prices (some locations listed several times with different prices).

I am doing the following:

SELECT date, location, price
FROM A INNER JOIN B ON A.locations = B.locations;

Then i see not 27 but 195 lines selected.
Any ideas?
***
After googling for a bit, i found that if i use NATURAL JOIN, then it selects only 27 rows and this is what i want. However there was an annotation to try to avoid using this type of join. Is there an equivalent other type join that i could use?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-29 : 08:20:28

Microsoft SQL Server and its query language (T-SQL) does not support natural joins - you have to explicitly specify the columns/conditions you want to join on.

This forum specializes in Microsoft SQL Server, so if you are using another DBMS, you may get better answers at a more general forum such as dbforums.com or a forum that specializes in your DBMS.

In T-SQL, each row in the left table will be matched against rows in the right table that satisfy the join conditions. So, for example, in the query that you wrote, if there was one row for location = New York in table A and there were two rows in table B for location = New York, you will get two rows in the select. If you want to get only one row for New York, you would need to specify which of the two rows in table B you want to pick up - perhaps based on the lowest price?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:15:43

quote:
Originally posted by zouave

I have 2 tables.
table A has 27 bookings with dates and locations
table B has locations with prices (some locations listed several times with different prices).

I am doing the following:

SELECT date, location, price
FROM A INNER JOIN B ON A.locations = B.locations;

Then i see not 27 but 195 lines selected.
Any ideas?
***
After googling for a bit, i found that if i use NATURAL JOIN, then it selects only 27 rows and this is what i want. However there was an annotation to try to avoid using this type of join. Is there an equivalent other type join that i could use?

in case of locations with multiple prices, whaich record you would be interested in? is there a criteria for that like may be a date or id field to indicate most recent price or something?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zouave
Starting Member

3 Posts

Posted - 2012-01-29 : 14:25:20

quote:
Originally posted by visakh16

quote:
Originally posted by zouave

I have 2 tables.
table A has 27 bookings with dates and locations
table B has locations with prices (some locations listed several times with different prices).

I am doing the following:

SELECT date, location, price
FROM A INNER JOIN B ON A.locations = B.locations;

Then i see not 27 but 195 lines selected.
Any ideas?
***
After googling for a bit, i found that if i use NATURAL JOIN, then it selects only 27 rows and this is what i want. However there was an annotation to try to avoid using this type of join. Is there an equivalent other type join that i could use?

in case of locations with multiple prices, whaich record you would be interested in? is there a criteria for that like may be a date or id field to indicate most recent price or something?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Just the location, SUM(price)
GROUP BY location

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:30:31

hmm...so does that mean you need to sum all prices for location?

then try this


SELECT a.date, a.location, b.price
FROM A a INNER JOIN (SELECT location,SUM(price) AS price FROM B GROUP BY location)b ON A.locations = b.locations;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zouave
Starting Member

3 Posts

Posted - 2012-01-29 : 16:52:59

quote:
Originally posted by visakh16

hmm...so does that mean you need to sum all prices for location?

then try this

SELECT a.date, a.location, b.price
FROM A a INNER JOIN (SELECT location,SUM(price) AS price FROM B GROUP BY location)b ON A.locations = b.locations;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks! That helped!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 17:09:12

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources