problem: row redundancy

[harcaype]

I am trying a multi select query in management studio and I am only expecting a result that is a single output per row, however, when I tried executing it. The rows I called repeated once. For example I have 2 rows in my column1 and when I multi-selected my tables. I received 4 rows which is just a CLONE of the 2 rows. To make things clear here's the full detail of my database and tables:

Database : MMDAserver
Tables : dbo.Records, dbo.DriverInfo, Vio.ViolationList

Columns in dbo.Records :
PlateNo
[Violation Commited]
[Street Name]
VCategory
VType
Date

Columns in dbo.DriverInfo :
[License Number]
[Reg'd Last Name]
[Reg'd First Name]
[Reg'd Middle Name]
[Reg'd Address]
[Address' City Code]
[Reg'd B-Date]
[Plate Number]
[Conduction Number]
[Vehicle Category]
[Vehicle Type]
[Vehicle Brand]

Columns in Vio.ViolationList
ViolationCode
ViolationD
FineAmnt

Here's my multi-select query:

[B]SELECT[/B] Vio.ViolationList.ViolationCode,Records.[Violation Commited],Vio.ViolationList.FineAmnt,DriverInfo.[Plate Number]
,DriverInfo.[License Number],DriverInfo.[Reg'd Last Name],DriverInfo.[Reg'd First Name],DriverInfo.[Reg'd Middle Name],DriverInfo.[Reg'd Address],
DriverInfo.[Address' City Code],DriverInfo.[Reg'd B-Date],DriverInfo.[Conduction Number],
DriverInfo.[Vehicle Category],DriverInfo.[Vehicle Type],DriverInfo.[Vehicle Brand]
,Records.[Street Name],Records.[Date]
[B]FROM[/B] Vio.ViolationList,DriverInfo,Records
[B]WHERE[/B] Records.PlateNo like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Vio.ViolationList.ViolationD

(I have placed an attachment where the output of this is shown. It's filename is output.jpg)

I've tried to test another query,which I narrowed down, to see where it might be going wrong:

[B]SELECT * FROM[/B] Records,DriverInfo,Vio.ViolationList 
[B]WHERE[/B] Records.PlateNo like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Vio.ViolationList.ViolationD

When I removed the and Records.[Violation Commited] like Vio.ViolationList.ViolationD and the FROM Vio.ViolationList. It just works fine. No clones. And I think the problem occurs from there. Any suggestions?

[tesuji]

[B]SELECT[/B] Vio.ViolationList.ViolationCode,Records.[Violation Commited],Vio.ViolationList.FineAmnt,DriverInfo.[Plate Number]
,DriverInfo.[License Number],DriverInfo.[Reg'd Last Name],DriverInfo.[Reg'd First Name],DriverInfo.[Reg'd Middle Name],DriverInfo.[Reg'd Address],
DriverInfo.[Address' City Code],DriverInfo.[Reg'd B-Date],DriverInfo.[Conduction Number],
DriverInfo.[Vehicle Category],DriverInfo.[Vehicle Type],DriverInfo.[Vehicle Brand]
,Records.[Street Name],Records.[Date]
[B]FROM[/B] Vio.ViolationList,DriverInfo,Records
[B]WHERE[/B] Records.PlateNo like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Vio.ViolationList.ViolationD

In above from clause you are joining the tables ViolationList, DriverInfo and Records together. These are inner joins where each row of one table will be combined with each row of second table etc. To prevent such cartesian products you must add join constraints to where clause, for example say you have tableA with columns (a,b) what you want to join with tableB having columns (c, d, e). you want to join them on column a from tableA and column d from tableB, so you have to write:

select a, b, c, d from tableA, tableB where a = d AND ...your further predicates

Join constraint is a = b, which means that only rows will be jointed with same values in both tables.

If you join various tables you must carefully define the joining constraints to avoiding bad cartesian products.

There is a more modern inner join syntax:

select .... from tableA inner join tableB inner join tableC etc

But this requires that primary and foreign keys are properly defined. If not, you have to add the ON clause to each inner join.

krs,
tesu

[harcaype]

thanks! what i did was just make the table under the .dbo schema instead of vio and i no longer used JOIN. =)