Click here to Skip to main content
14,734,845 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
When I was writing my SQL Statement, I notice the car name appear 9 times not aligning with the race times and TrackLengths I think you would use SQL INNER JOIN But the only common in both is the BarCode so I don't quite fully understand the INNER JOIN. The Goal is when I execute it should display the Description, RaceTime, TrackLength in the three columns.


SELECT Cars.Description,RaceStats.RaceTime, RaceStats.TrackLength
FROM Cars
INNER JOIN RaceStats ON Cars.BarCode = RaceStats.BarCode;

What I have tried:

I tried searching on google about it But, I somewhat understand it but not fully. Also, I tried contacting my instructor on this but no response.
Posted
Updated 28-Nov-20 22:04pm
v2
Comments
PIEBALDconsult 28-Nov-20 22:22pm
   
Double check your data.
Try to write the output on a piece of paper as it should appear.

Looks ok to me when I test like this:
CREATE table Cars (BarCode int, Description varchar(20));
INSERT INTO Cars (BarCode, Description) VALUES (1000,'Alfa'), (1001,'Citroen'), (1002,'Ford');

CREATE table RaceStats (BarCode int, RaceTime float, TrackLength float);
INSERT INTO RaceStats (BarCode, RaceTime, TrackLength) VALUES (1000,0,0), (1001,1,1), (1002,2,2);

SELECT Cars.Description,RaceStats.RaceTime, RaceStats.TrackLength
FROM Cars
INNER JOIN RaceStats ON Cars.BarCode = RaceStats.BarCode;
SQL Server 2017 | db<>fiddle[^]

A more advanced database design could look like this:
CREATE TABLE [Cars] (
  [BarCode] int PRIMARY KEY,
  [Description] varchar(20)
)
GO

CREATE TABLE [RaceStats] (
  [id] int PRIMARY KEY IDENTITY(1, 1),
  [BarCode] int,
  [RaceTime] float,
  [TrackLength] float,
  [RaceDate] datetime
)
GO

ALTER TABLE [RaceStats] ADD FOREIGN KEY ([BarCode]) REFERENCES [Cars] ([BarCode])
GO
Here is a diagram that shows the relation defined by the Foreign Key:
dbdiagram.io - Database Relationship Diagrams Design Tool[^]
   
v2
Comments
honey the codewitch 29-Nov-20 8:30am
   
I'm going to add to make it explicit. It appears his data and not his query is the real culprit. I'll also explain INNER JOIN here since he was asking. Inner join simply combines columns from two tables where one or more columns in one table correspond to some columns in the other table (either directly or indirectly through a formula)
An INNER JOIN returns only data that is in both tables: SQL INNER JOIN Keyword[^]

So start by looking at the data and decide exactly what you want to return. Then compare that to the actual output and work out what there is that is extra and how it can be recognised.

We can't do that for you: we have no access to your DB and the data it contains!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900