join two tables? - 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

General SQL Server Forums

New to SQL Server Programming

join two tables?

Author

Topic

emmy
Starting Member

3 Posts

Posted - 2008-06-21 : 09:14:55

hi everybody
I have a question and need answer as soon as possible. so i hope i get help. How can i join two tables have no common fields? I tried outer join but i get redundant data(repeated values).

Give me a script to solve the following problem . There are no real wrong answers, but the more efficient and bulletproof the query is the better. Assign rooms to classes based on capacity using the Classes and Rooms tables. Rules: each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.

And this is the tables query:

CREATE TABLE Classes (

ClassID int NOT NULL,

Class varchar (50),

Students int NOT NULL, CONSTRAINT PK_Classes PRIMARY KEY CLUSTERED (ClassID

) )

GO

CREATE TABLE Rooms (

Room int NOT NULL,

Capacity int NOT NULL,

CONSTRAINT PK_Rooms PRIMARY KEY CLUSTERED (Room)

)

GO

INSERT INTO Classes (ClassID, Class, Students) VALUES (1, 'Whats New for SQL Server 2008', 65)

INSERT INTO Classes (ClassID, Class, Students) VALUES (2, 'Introduction to Silverlight 1.1', 52)

INSERT INTO Classes (ClassID, Class, Students) VALUES (3, 'XQuery Deep Dive', 35)

INSERT INTO Classes (ClassID, Class, Students) VALUES (4, 'Ranking and Windowning', 35)

INSERT INTO Classes (ClassID, Class, Students) VALUES (5, 'Expression Blend 1.1', 65)

INSERT INTO Classes (ClassID, Class, Students) VALUES (6, 'Database Design Patterns', 75)

INSERT INTO Classes (ClassID, Class, Students) VALUES (7, 'CrossTabs for Dummies', 1)

INSERT INTO Rooms (Room, Capacity) VALUES (221, 30)

INSERT INTO Rooms (Room, Capacity) VALUES (222, 40)

INSERT INTO Rooms (Room, Capacity) VALUES (223, 50)

INSERT INTO Rooms (Room, Capacity) VALUES (224, 60)

INSERT INTO Rooms (Room, Capacity) VALUES (225, 70)

INSERT INTO Rooms (Room, Capacity) VALUES (226, 74)

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-21 : 11:26:22

May be this:-

CREATE TABLE #Session
(
SessionID int IDENTITY(1,1),
ClassID int,
Room int
)

DECLARE @ID int

SELECT @ID=MIN(ClassID)
FROM Classes

WHILE @ID IS NOT NULL
BEGIN

INSERT INT #Session (ClassID,Room)
SELECT c.ClassID, b.Room
FROM Classes c
OUTER APPLY (
             SELECT Room
             FROM
             (
                SELECT ROW_NUMBER() OVER(ORDER BY Capacity) AS Seq,
                    Room
                FROM Rooms r
                LEFT JOIN #Session s
                ON s.Room=r.Room
                WHERE r.Capacity >c.Students
                AND s.Room IS NULL 
              )t
              WHERE t.RowNo=1           
            )b
WHERE c.ClassID=@ID

SELECT @ID=MIN(ClassID)
FROM Classes
WHERE ClassID>@ID
END

SELECT * FROM #Session

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 11:29:18

What have you tried yourself this far?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 11:38:01

Can you split one class into two rooms?

E 12°55'05.25"
N 56°04'39.16"

emmy
Starting Member

3 Posts

Posted - 2008-06-21 : 19:44:05

Hi
thaks very much i tried the query but it didnot work with me
I tried this query and it give me the answer except it returns repeated value that i donot need:
SELECT class ,students,capacity,room

FROM classes
left OUTER JOIN rooms
ON students<capacity

what i need is that:
each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 03:04:27

quote:
Originally posted by emmy

Hi
thaks very much i tried the query but it didnot work with me
I tried this query and it give me the answer except it returns repeated value that i donot need:
SELECT class ,students,capacity,room

FROM classes
left OUTER JOIN rooms
ON students<capacity

what i need is that:
each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.

]
Why the query didnt work? What was the error?
How do you think your query will work if no room cant be used twice?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-22 : 03:49:35

[code]DECLARE @Classes TABLE
(
ClassID INT,
Class VARCHAR(50),
Students INT
)

INSERT @Classes
SELECT 1, 'Whats New for SQL Server 2008', 65 UNION ALL
SELECT 2, 'Introduction to Silverlight 1.1', 52 UNION ALL
SELECT 3, 'XQuery Deep Dive', 35 UNION ALL
SELECT 4, 'Ranking and Windowning', 35 UNION ALL
SELECT 5, 'Expression Blend 1.1', 65 UNION ALL
SELECT 6, 'Database Design Patterns', 75 UNION ALL
SELECT 7, 'CrossTabs for Dummies', 1

DECLARE @Rooms TABLE
(
Room INT,
Capacity INT
)

INSERT @Rooms
SELECT 221, 30 UNION ALL
SELECT 222, 40 UNION ALL
SELECT 223, 50 UNION ALL
SELECT 224, 60 UNION ALL
SELECT 225, 70 UNION ALL
SELECT 226, 74

DECLARE @Session TABLE
(
RowID INT IDENTITY(0, 1),
ClassID INT,
Class VARCHAR(50),
Students INT,
Room INT,
Capacity INT
)

INSERT @Session
(
ClassID,
Class,
Students
)
SELECT ClassID,
Class,
Students
FROM @Classes
ORDER BY Students

DECLARE @RowID INT

SELECT @RowID = MAX(RowID)
FROM @Session

WHILE @RowID >= 0
BEGIN
UPDATE s
SET s.Room = w.Room,
s.Capacity = w.Capacity
FROM @Session AS s
CROSS APPLY (
SELECT TOP 1 r.Room,
r.Capacity
FROM @Rooms AS r
WHERE r.Capacity >= s.Students
AND NOT EXISTS (SELECT * FROM @Session AS x WHERE x.Room = r.Room)
) AS w
WHERE s.RowID = @RowID

SET @RowID = @RowID - 1
END

SELECT ClassID,
Class,
Students,
Room,
Capacity
FROM @Session
ORDER BY ClassID[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 05:10:47

quote:
Originally posted by emmy

Hi
thaks very much i tried the query but it didnot work with me
I tried this query and it give me the answer except it returns repeated value that i donot need:
SELECT class ,students,capacity,room

FROM classes
left OUTER JOIN rooms
ON students<capacity

what i need is that:
each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.

it had a small typo. Probably why it didint work

CREATE TABLE #Session
(
SessionID int IDENTITY(1,1),
ClassID int,
Room int
)

DECLARE @ID int

SELECT @ID=MIN(ClassID)
FROM Classes

WHILE @ID IS NOT NULL
BEGIN

INSERT INTO #Session (ClassID,Room)
SELECT c.ClassID, b.Room
FROM Classes c
OUTER APPLY (
             SELECT Room
             FROM
             (
                SELECT ROW_NUMBER() OVER(ORDER BY Capacity) AS Seq,
                    Room
                FROM Rooms r
                LEFT JOIN #Session s
                ON s.Room=r.Room
                WHERE r.Capacity >c.Students
                AND s.Room IS NULL 
              )t
              WHERE t.RowNo=1           
            )b
WHERE c.ClassID=@ID

SELECT @ID=MIN(ClassID)
FROM Classes
WHERE ClassID>@ID
END

SELECT * FROM #Session

emmy
Starting Member

3 Posts

Posted - 2008-06-22 : 11:04:43

hi everybody

thanks very much
It was a big help for me

i tried the qyery peso and it worked with me
as for the second query vesakh16 i tried it but it give error message:

Msg 209, Level 16, State 1, Line 16
Ambiguous column name 'Room'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'RowNo'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-22 : 11:09:08

[code]
INSERT INTO #Session (ClassID,Room)
SELECT c.ClassID, b.Room
FROM Classes c
OUTER APPLY (
SELECT Room
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY Capacity) AS ~~Seq~~ RowNo,
s.Room
FROM Rooms r
LEFT JOIN #Session s
ON s.Room=r.Room
WHERE r.Capacity >c.Students
AND s.Room IS NULL
)t
WHERE t.RowNo=1
)b
WHERE c.ClassID=@ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 12:50:25

quote:

Originally posted by khtan


INSERT INTO #Session (ClassID,Room)
SELECT c.ClassID, b.Room
FROM Classes c
OUTER APPLY (
             SELECT Room
             FROM
             (
                SELECT ROW_NUMBER() OVER(ORDER BY Capacity) AS Seq RowNo,
                    s.Room
                FROM Rooms r
                LEFT JOIN #Session s
                ON s.Room=r.Room
                WHERE r.Capacity >c.Students
                AND s.Room IS NULL 
              )t
              WHERE t.RowNo=1           
            )b
WHERE c.ClassID=@ID

KH
[spoiler]Time is always against us[/spoiler]

Thanks for the catach Khtan.Thats was a stupid copy paste error

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-22 : 13:10:39

WHERE r.Capacity >= c.Students

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources