Query Using 4 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

SQL Server 2000 Forums

SQL Server Development (2000)

Query Using 4 tables?

Author

Topic

wreckster
Starting Member

3 Posts

Posted - 2002-01-19 : 05:08:32

I am having problems trying to write a query which covers 4 tables as follows:-

table tblCar
field1 carID
field2 carNumber
field3 ownerID

table tblOwner
field1 ownerID
field2 Name

table tblRace
field1 RaceID
field2 RaceLocation
field3 Racedate

table tblRaceResult
field1 RaceID
field2 CarID
field3 RacePosition

The data I am trying to retrieve is owners names where they raced in a certain race ie RaceID = 9 but have not won any races ie where RaceID > 1

Thanks in Advance

sica
Posting Yak Master

143 Posts

Posted - 2002-01-19 : 05:48:28

If you post your query ,there is surely someone who could help you!!!

Sica

Nazim
A custom title

1408 Posts

Posted - 2002-01-19 : 07:33:20

select o.name from tblOwners o
inner join
table tblCar c
on o.ownerid=c.ownerid
inner join tblRaceResult R
on c.carid=r.carid
where r.raceid=9

if you want to look for a location too inner join the other table too

HTH

----------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your Good is

wreckster
Starting Member

3 Posts

Posted - 2002-01-19 : 07:40:54

The difficulty I am having is actually where I start with this query.

In plain English, the result I am after is all the tblOwner.Name who competed in tblRace.RaceID = 1 but have not come first tblRaceResult.RacePosition > 1

The query I have so far is

SELECT tblOwner.Name, tblRaceresult.RaceID, tblRaceresult.RacePosition
FROM tblOwner
INNER JOIN tblCar ON tblOwner.ownerID = tblCar.ownerID
INNER JOIN tblRaceresult ON tblCar.CarID = tblRaceresult.CarID
INNER JOIN tblRace ON tblRaceresult.RaceID = tblRace.RaceID
WHERE (tblRaceresult.RaceID = 3) AND (tblRaceresult.RacePosition > 1)

This only gives me the results of who competed in RaceID=3 and did not come first (RacePosition > 1), but I am looking for who competed in RaceID=3 but didnt come first in this or any other race?

Thanks again.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-19 : 08:12:42

Either of these should work. Relative performance will depend on data.
2 correlated subqueries:


SELECT Name
FROM tblOwner
WHERE ownerID NOT IN (
    SELECT ownerID
    FROM tblCar c
    INNER JOIN tblRaceResult r ON c.carID = r.CarID
    WHERE RacePosition = 1
      AND RaceID > 1)
  AND ownerID IN (
    SELECT ownerID
    FROM tblCar c
    INNER JOIN tblRaceResult r ON c.carID = r.CarID
    WHERE RaceID = 9)

Join it all together and keep the ones you want:


SELECT MAX(o.Name) AS Name
FROM tblOwner o
INNER JOIN tblCar c ON o.OwnerID = c.OwnerID
INNER JOIN tblRaceResult r ON c.CarID = r.CarID
GROUP BY o.ownerID
HAVING COUNT(CASE WHEN RaceID = 9 THEN 1 END) > 0
  AND COUNT(CASE WHEN RaceID > 1 AND RacePosition = 1 THEN 1 END) = 0

This assumes tables that look like this:


CREATE TABLE tblOwner (
  ownerID int PRIMARY KEY,
  Name varchar(50) NOT NULL
)

CREATE TABLE tblCar (
  carID int PRIMARY KEY,
  carNumber int NOT NULL,
  driverName varchar(50) NOT NULL,
  ownerID int NOT NULL REFERENCES tblOwner(ownerID)
)

CREATE TABLE tblRace (
  RaceID int PRIMARY KEY,
  RaceLocation varchar(50) NOT NULL,
  Racedate datetime NOT NULL
)

CREATE TABLE tblRaceResult (
  RaceID int NOT NULL REFERENCES tblRace(RaceID),
  CarID int NOT NULL REFERENCES tblCar(carID),
  RacePosition int NOT NULL,
  PRIMARY KEY (RaceID, CarID)
)

There's an extra DriverName column in tblCar. I used test data from 2001 F1 season. This has errors in it: both conceptually -- I've left out any unplaced (DNF) results -- and logically -- (RaceID, RacePosition) is not unique.


INSERT INTO tblRace VALUES (1, 'Australia', '20010304')
INSERT INTO tblRace VALUES (2, 'Malaysia', '20010318')
INSERT INTO tblRace VALUES (3, 'Brazil', '20010401')
INSERT INTO tblRace VALUES (4, 'San Marino', '20010415')
INSERT INTO tblRace VALUES (5, 'Spain', '20010429')
INSERT INTO tblRace VALUES (6, 'Austria', '20010513')
INSERT INTO tblRace VALUES (7, 'Monaco', '20010527')
INSERT INTO tblRace VALUES (8, 'Canada', '20010610')
INSERT INTO tblRace VALUES (9, 'Germany', '20010624')
INSERT INTO tblRace VALUES (10, 'France', '20010701')
INSERT INTO tblRace VALUES (11, 'Great Britain', '20010715')
INSERT INTO tblRace VALUES (12, 'Germany', '20010729')
INSERT INTO tblRace VALUES (13, 'Hungary', '20010819')
INSERT INTO tblRace VALUES (14, 'Belgium', '20010902')
INSERT INTO tblRace VALUES (15, 'Italy', '20010916')
INSERT INTO tblRace VALUES (16, 'USA', '20010930')
INSERT INTO tblRace VALUES (17, 'Japan', '20011014')


INSERT INTO tblOwner VALUES (1, 'Ferrari')
INSERT INTO tblOwner VALUES (2, 'McLaren')
INSERT INTO tblOwner VALUES (3, 'Williams')
INSERT INTO tblOwner VALUES (4, 'Sauber')
INSERT INTO tblOwner VALUES (5, 'Jordan')
INSERT INTO tblOwner VALUES (6, 'BAR')
INSERT INTO tblOwner VALUES (7, 'Benetton')
INSERT INTO tblOwner VALUES (8, 'Jaguar')
INSERT INTO tblOwner VALUES (9, 'Prost')
INSERT INTO tblOwner VALUES (10, 'Arrows')
INSERT INTO tblOwner VALUES (11, 'Minardi')


INSERT INTO tblCar VALUES (1, 1, 'Michael Schumacher', 1)
INSERT INTO tblCar VALUES (2, 2, 'Rubens Barrichello', 1)
INSERT INTO tblCar VALUES (3, 3, 'Mika Hakkinen', 2)
INSERT INTO tblCar VALUES (4, 4, 'David Coulthard', 2)
INSERT INTO tblCar VALUES (5, 5, 'Ralf Schumacher', 3)
INSERT INTO tblCar VALUES (6, 6, 'Juan Pablo Montoya', 3)
INSERT INTO tblCar VALUES (7, 7, 'Giancarlo Fisichella', 7)
INSERT INTO tblCar VALUES (8, 8, 'Jenson Button', 7)
INSERT INTO tblCar VALUES (9, 9, 'Jacques Villeneuve', 6)
INSERT INTO tblCar VALUES (10, 10, 'Olivier Panis', 6)
INSERT INTO tblCar VALUES (11, 11, 'Jarno Trulli', 5)
INSERT INTO tblCar VALUES (12, 12, 'Jean Alesi', 5)
INSERT INTO tblCar VALUES (13, 13, 'Jos Verstappen', 10)
INSERT INTO tblCar VALUES (14, 14, 'Enrique Antonio Bernoldi', 10)
INSERT INTO tblCar VALUES (15, 15, 'Nick Heidfeld', 4)
INSERT INTO tblCar VALUES (16, 16, 'Kimi Raikkonen', 4)
INSERT INTO tblCar VALUES (17, 17, 'Eddie Irvine', 8)
INSERT INTO tblCar VALUES (18, 18, 'Luciano Burti', 8)
INSERT INTO tblCar VALUES (19, 19, 'Fernando Alonso', 11)
INSERT INTO tblCar VALUES (20, 20, 'Tarso Marques', 11)
INSERT INTO tblCar VALUES (21, 21, 'Heinz Harald Frentzen', 9)
INSERT INTO tblCar VALUES (22, 22, 'Tomas Enge', 9)


INSERT INTO tblRaceResult
  SELECT 1, 1, 1 UNION SELECT 2, 1, 1 UNION SELECT 3, 1, 2
  UNION SELECT 5, 1, 1 UNION SELECT 6, 1, 2 UNION SELECT 7, 1, 1
  UNION SELECT 8, 1, 2 UNION SELECT 9, 1, 1 UNION SELECT 10, 1, 1
  UNION SELECT 11, 1, 2 UNION SELECT 13, 1, 1 UNION SELECT 14, 1, 1
  UNION SELECT 15, 1, 4 UNION SELECT 16, 1, 2 UNION SELECT 1, 2, 3
  UNION SELECT 2, 2, 2 UNION SELECT 4, 2, 3 UNION SELECT 6, 2, 3
  UNION SELECT 7, 2, 2 UNION SELECT 9, 2, 5 UNION SELECT 10, 2, 3
  UNION SELECT 11, 2, 3 UNION SELECT 12, 2, 2 UNION SELECT 13, 2, 2
  UNION SELECT 14, 2, 5 UNION SELECT 15, 2, 2 UNION SELECT 16, 2, 15
  UNION SELECT 2, 3, 6 UNION SELECT 4, 3, 4 UNION SELECT 5, 3, 9
  UNION SELECT 8, 3, 3 UNION SELECT 9, 3, 6 UNION SELECT 11, 3, 1
  UNION SELECT 13, 3, 5 UNION SELECT 14, 3, 4 UNION SELECT 16, 3, 1
  UNION SELECT 1, 4, 2 UNION SELECT 2, 4, 3 UNION SELECT 3, 4, 1
  UNION SELECT 4, 4, 2 UNION SELECT 5, 4, 5 UNION SELECT 6, 4, 1
  UNION SELECT 7, 4, 5 UNION SELECT 9, 4, 3 UNION SELECT 10, 4, 4
  UNION SELECT 13, 4, 3 UNION SELECT 14, 4, 2 UNION SELECT 16, 4, 3
  UNION SELECT 2, 5, 5 UNION SELECT 4, 5, 1 UNION SELECT 8, 5, 1
  UNION SELECT 9, 5, 4 UNION SELECT 10, 5, 2 UNION SELECT 12, 5, 1
  UNION SELECT 13, 5, 4 UNION SELECT 14, 5, 7 UNION SELECT 15, 5, 3
  UNION SELECT 5, 6, 2 UNION SELECT 9, 6, 2 UNION SELECT 11, 6, 4
  UNION SELECT 13, 6, 8 UNION SELECT 15, 6, 1 UNION SELECT 1, 7, 13
  UNION SELECT 3, 7, 6 UNION SELECT 5, 7, 14 UNION SELECT 9, 7, 11
  UNION SELECT 10, 7, 11 UNION SELECT 11, 7, 13 UNION SELECT 12, 7, 4
  UNION SELECT 14, 7, 3 UNION SELECT 15, 7, 10 UNION SELECT 16, 7, 8
  UNION SELECT 1, 8, 14 UNION SELECT 2, 8, 11 UNION SELECT 3, 8, 10
  UNION SELECT 4, 8, 12 UNION SELECT 5, 8, 15 UNION SELECT 7, 8, 7
  UNION SELECT 9, 8, 13 UNION SELECT 10, 8, 16 UNION SELECT 11, 8, 15
  UNION SELECT 12, 8, 5 UNION SELECT 16, 8, 9 UNION SELECT 3, 9, 7
  UNION SELECT 5, 9, 3 UNION SELECT 6, 9, 8 UNION SELECT 7, 9, 4
  UNION SELECT 9, 9, 9 UNION SELECT 11, 9, 8 UNION SELECT 12, 9, 3
  UNION SELECT 13, 9, 9 UNION SELECT 14, 9, 8 UNION SELECT 15, 9, 6
  UNION SELECT 1, 10, 7 UNION SELECT 3, 10, 4 UNION SELECT 4, 10, 8
  UNION SELECT 5, 10, 7 UNION SELECT 6, 10, 5 UNION SELECT 10, 10, 9
  UNION SELECT 12, 10, 7 UNION SELECT 14, 10, 11 UNION SELECT 15, 10, 9
  UNION SELECT 16, 10, 11 UNION SELECT 1, 11, 5 UNION SELECT 2, 11, 4
  UNION SELECT 3, 11, 11 UNION SELECT 4, 11, 6 UNION SELECT 10, 11, 8
  UNION SELECT 11, 11, 7 UNION SELECT 13, 11, 10 UNION SELECT 14, 11, 6
  UNION SELECT 15, 11, 8 UNION SELECT 16, 11, 7 UNION SELECT 2, 12, 8
  UNION SELECT 3, 12, 5 UNION SELECT 4, 12, 5 UNION SELECT 5, 12, 4
  UNION SELECT 8, 12, 11 UNION SELECT 10, 12, 5 UNION SELECT 16, 12, 4
  UNION SELECT 1, 13, 10 UNION SELECT 2, 13, 7 UNION SELECT 5, 13, 12
  UNION SELECT 6, 13, 6 UNION SELECT 7, 13, 8 UNION SELECT 8, 13, 10
  UNION SELECT 10, 13, 13 UNION SELECT 11, 13, 10 UNION SELECT 12, 13, 9
  UNION SELECT 13, 13, 12 UNION SELECT 14, 13, 10 UNION SELECT 4, 14, 10
  UNION SELECT 7, 14, 9 UNION SELECT 11, 14, 14 UNION SELECT 12, 14, 8
  UNION SELECT 14, 14, 12 UNION SELECT 16, 14, 13 UNION SELECT 1, 15, 4
  UNION SELECT 3, 15, 3 UNION SELECT 4, 15, 7 UNION SELECT 5, 15, 6
  UNION SELECT 6, 15, 9 UNION SELECT 10, 15, 6 UNION SELECT 11, 15, 6
  UNION SELECT 13, 15, 6 UNION SELECT 15, 15, 11 UNION SELECT 16, 15, 6
  UNION SELECT 1, 16, 6 UNION SELECT 5, 16, 7 UNION SELECT 6, 16, 4
  UNION SELECT 7, 16, 10 UNION SELECT 8, 16, 4 UNION SELECT 9, 16, 10
  UNION SELECT 10, 16, 7 UNION SELECT 11, 16, 5 UNION SELECT 13, 16, 7
  UNION SELECT 15, 16, 7 UNION SELECT 1, 17, 11 UNION SELECT 6, 17, 7
  UNION SELECT 7, 17, 3 UNION SELECT 9, 17, 7 UNION SELECT 11, 17, 9
  UNION SELECT 16, 17, 5 UNION SELECT 1, 18, 8 UNION SELECT 2, 18, 10
  UNION SELECT 4, 18, 11 UNION SELECT 8, 18, 6 UNION SELECT 9, 18, 8
  UNION SELECT 10, 18, 14 UNION SELECT 11, 18, 12 UNION SELECT 13, 18, 11
  UNION SELECT 15, 18, 5 UNION SELECT 16, 18, 12 UNION SELECT 1, 19, 12
  UNION SELECT 2, 19, 13 UNION SELECT 5, 19, 13 UNION SELECT 7, 19, 6
  UNION SELECT 9, 19, 14 UNION SELECT 10, 19, 17 UNION SELECT 11, 19, 16
  UNION SELECT 15, 19, 13 UNION SELECT 2, 20, 14 UNION SELECT 3, 20, 9
  UNION SELECT 5, 20, 16 UNION SELECT 8, 20, 9 UNION SELECT 10, 20, 10
  UNION SELECT 14, 20, 13 UNION SELECT 1, 21, 9 UNION SELECT 2, 21, 9
  UNION SELECT 3, 21, 8 UNION SELECT 4, 21, 9 UNION SELECT 5, 21, 10
  UNION SELECT 6, 21, 10 UNION SELECT 7, 21, 6 UNION SELECT 8, 21, 5
  UNION SELECT 9, 21, 15 UNION SELECT 10, 21, 12 UNION SELECT 11, 21, 11
  UNION SELECT 12, 21, 6 UNION SELECT 14, 21, 9 UNION SELECT 16, 21, 10
  UNION SELECT 2, 22, 12 UNION SELECT 5, 22, 11 UNION SELECT 6, 22, 11
  UNION SELECT 8, 22, 8 UNION SELECT 9, 22, 12 UNION SELECT 10, 22, 10
  UNION SELECT 15, 22, 12 UNION SELECT 16, 22, 14

Edited by - Arnold Fribble on 01/19/2002 08:17:55

Nazim
A custom title

1408 Posts

Posted - 2002-01-19 : 08:48:00

You Rock Arnold!

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your Good is

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-19 : 09:03:29

Don't know about rocking, but I do get drawn into displacement activities far too easily

BTW, I like your new sig, even with the typo. The Nietzsche one you had before (Was mich nicht unbringt, macht mich staerker in the original, I am told) made me uncomfortable.

Nazim
A custom title

1408 Posts

Posted - 2002-01-19 : 09:46:44

Thanx , i think typo's are part of me how much ever i try avoiding it they creep in

.

whts this Nietzsche thingy ?

quote:

BTW, I like your new sig, even with the typo. The Nietzsche one you had before (Was mich nicht unbringt, macht mich staerker in the original, I am told) made me uncomfortable.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-19 : 11:29:48

Friedrich Nietzsche, 1844-1900, German philosopher, misanthrope, and generally grumpy bastard. Big moustache. Had a bit of a downer on God. The joke runs:

"God is dead." - Nietzsche
"Nietzsche is dead." - God

wreckster
Starting Member

3 Posts

Posted - 2002-01-19 : 12:04:54

Thanks for your help!
Problem solved!

Nazim
A custom title

1408 Posts

Posted - 2002-01-20 : 00:01:30

Thank god for Small mercies, i dont have anything common with this Nietzsche.

though my Name raises quite a few eyebrows. but what it means in my local language is "Adminsitrator or Director" . it doesnt have anything to do with history.

quote:

Friedrich Nietzsche, 1844-1900, German philosopher, misanthrope, and generally grumpy bastard. Big moustache. Had a bit of a downer on God.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources