•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 391,609 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,621 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 1025 | Replies: 2 | Solved
 |
•
•
Join Date: May 2008
Posts: 31
Reputation: 
Rep Power: 1
Solved Threads: 0
Light Poster
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:
(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:
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?
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:
SELECT 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] FROM Vio.ViolationList,DriverInfo,Records WHERE 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:
SELECT * FROM Records,DriverInfo,Vio.ViolationList WHERE 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?
Last edited by harcaype : Jul 2nd, 2008 at 10:42 am.
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
[quote=harcaype;639245]
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
SELECT 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] FROM Vio.ViolationList,DriverInfo,Records WHERE 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
Last edited by tesuji : Jul 2nd, 2008 at 12:33 pm.
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
Similar Threads
- Cities and Districts DataBase - Basic Question (Database Design)
Other Threads in the MS SQL Forum
- Previous Thread: Copying data from another database in the same server
- Next Thread: SQL Stumper
