1:Many Relationship Issue - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

1:Many Relationship Issue

Author

Topic

mtaplits
Starting Member

6 Posts

Posted - 2003-09-28 : 00:53:32

Hi! I'm new here (and pretty new to SQL) and looking for some help with a 1:Many relationship query. Any help is appreciated. Thanks in advance!

I have 3 tables.
Table 1: Towns AS t
TownID, TownName

Table 2: RestaurantInfo AS ri
RestaurantID, RestaurantName

Table 3: RestaurantsTowns AS rt
RestaurantID, TownID

Now, if someone wants to know which restaurants delivery to a specific town TownID=1 and there are 3 restaurants that delivery to TownID, how can I write this query?

So far I have:
SELECT ri.RestaurantID, ri.Name,
t.TownID, t.Town,
rt.RestaurantID, rt.TownID
FROM RestaurantInfo AS ri, RestaurantsTowns AS rt, Towns AS t
WHERE t.TownID = rt.TownID
AND ri.RestaurantID = rt.RestaurantID

I'm getting a mess.

Can anyone assist? Thanks again.

-- Marshall

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-28 : 01:21:51

This works for me:

SELECT RestaurantName
FROM RestaurantInfo ri
INNER JOIN RestaurantsTowns rt ON ri.RestaurantID = rt.RestaurantID
INNER JOIN Towns t ON rt.TownID = t.TownID
WHERE t.TownID = 1

Here is the code that I used to verify(by the way, if you ask a question again, please provide the CREATE TABLE and INSERT INTO statements for us to help, see my code for example):



SET NOCOUNT ON

CREATE TABLE Towns
(
TownID INT IDENTITY(1, 1) NOT NULL,
TownName VARCHAR(50) NOT NULL
)

CREATE TABLE RestaurantInfo
(
RestaurantID INT IDENTITY(1, 1) NOT NULL,
RestaurantName VARCHAR(50) NOT NULL
)

CREATE TABLE RestaurantsTowns
(
RestaurantID INT NOT NULL,
TownID INT NOT NULL
)

INSERT INTO RestaurantInfo (RestaurantName)
SELECT 'Burger King'

INSERT INTO RestaurantInfo (RestaurantName)
SELECT 'In n Out'

INSERT INTO RestaurantInfo (RestaurantName)
SELECT 'Jack in the Box'

INSERT INTO RestaurantInfo (RestaurantName)
SELECT 'Rain Forest Cafe'

INSERT INTO Towns (TownName)
SELECT 'San Diego'

INSERT INTO Towns (TownName)
SELECT 'Las Vegas'

INSERT INTO RestaurantsTowns VALUES(1, 1)
INSERT INTO RestaurantsTowns VALUES(2, 1)
INSERT INTO RestaurantsTowns VALUES(3, 1)
INSERT INTO RestaurantsTowns VALUES(4, 2)

SELECT RestaurantName
FROM RestaurantInfo ri
INNER JOIN RestaurantsTowns rt ON ri.RestaurantID = rt.RestaurantID
INNER JOIN Towns t ON rt.TownID = t.TownID
WHERE t.TownID = 1

DROP TABLE Towns
DROP TABLE RestaurantInfo
DROP TABLE RestaurantsTowns

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-28 : 15:05:14

OK Tara, you got me beat..

You're in Las Vegas...it's 1:30 in the morining...

AND YOU'RE POSTING?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

mtaplits
Starting Member

6 Posts

Posted - 2003-09-28 : 19:29:21

Thank you sooo much! There is so much to learn about joins, that's for sure!

Regards.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-29 : 07:49:53

Tara, we want a pic of the yak in front of the Bellagio.

Voted best SQL forum nickname...."Tutorial-D"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-29 : 10:50:19

I had to work from 9pm til 2am on Saturday night. We do disaster recovery testing in Las Vegas (our disaster recovery site) twice per year, and it occurs on Saturday nights.

Had some downtime during the night, so that's why I posted.

Bellagio is a beautiful hotel.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 10:52:12

quote:
Originally posted by tduggan

I had to work from 9pm til 2am on Saturday night. We do disaster recovery testing in Las Vegas (our disaster recovery site) twice per year, and it occurs on Saturday nights.

Had some downtime during the night, so that's why I posted.

Bellagio is a beautiful hotel.

Tara

How cool is that! I get to go to the sticks in Central PA....

So after 2:00 am....what did you do?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-29 : 11:13:55

I actually went to sleep since I had already lost a bunch of money that day. We usually hang out at the Hard Rock afterwards, but I think most of us just went back to the hotel.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 11:51:19

quote:
Originally posted by tduggan
I actually went to sleep since

Party animal!

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources