June 12, 2012 at 2:15 pm
Hi,
I've two tables in the production does not have the primary key and foreign keys and yet I've to join those two tables in order to get the data for the reports.
My issue is that I'm getting the Cartesian Product when I join the two tables based on the matching field and I dont want that. Basically, in my example below, if I join Customer table with the Ticket table based on the CustomerID, I should get only 3 rows. However, I'm getting 6 rows right now.
I'm not sure if I'm doing anything wrong here or if it is even possible.
Any kind of guidance is much appreciated.
Thank you in advance.
I've tried to create the sample tables and data here hoping that somebody can help me on this. I'm new to this field and even to this forum. So, please bear with me if my postin is not up to the standard.
Below are my sample queries
--Create Table dbo.Customer
CREATE TABLE dbo.Customer(
FlightNbr varchar(50) NULL,
DeptDateTime datetime NULL,
Origin varchar(50) NULL,
Destination varchar(50) NULL,
FirstName varchar(50) NULL,
CustomerID varchar(50) NULL,
SeatRow varchar(50) NULL
) ON [PRIMARY]
--INSERT 3 rows into dbo.Customer table
GO
INSERT INTO dbo.Customer (FlightNbr, DeptDateTime, Origin, Destination,FirstName, CustomerID, SeatRow)
SELECT 0111, '2012-05-06 12:00:00.000','LAS','LGA','John','ASDF','1'
UNION ALL
SELECT 0112, '2012-05-03 12:00:00.000','LGA','LAS','John','ASDF','1'
UNION ALL
SELECT 0112, '2012-05-03 12:00:00.000','DEN','LAS','John','ASDF','1'
GO
--Create Table dbo.Ticket
CREATE TABLE dbo.Ticket (
CustomerID varchar(50) NULL,
TicketNumber char(13) NOT NULL,
TktAmount decimal(18, 4) NOT NULL
) ON [PRIMARY]
--Insert 2 rows into dbo.Ticket table
GO
INSERT INTO dbo.Ticket (CustomerID, TicketNumber, TktAmount)
SELECT 'ASDF',1110099988833,150.0000
UNION ALL
SELECT 'ASDF',22244466666666, 85.0000
GO
June 12, 2012 at 2:21 pm
shil_jsh (6/12/2012)
Hi,I've two tables in the production does not have the primary key and foreign keys and yet I've to join those two tables in order to get the data for the reports.
My issue is that I'm getting the Cartesian Product when I join the two tables based on the matching field and I dont want that. Basically, in my example below, if I join Customer table with the Ticket table based on the CustomerID, I should get only 3 rows. However, I'm getting 6 rows right now.
I'm not sure if I'm doing anything wrong here or if it is even possible.
Any kind of guidance is much appreciated.
Thank you in advance.
I've tried to create the sample tables and data here hoping that somebody can help me on this. I'm new to this field and even to this forum. So, please bear with me if my postin is not up to the standard.
Below are my sample queries
--Create Table dbo.Customer
CREATE TABLE dbo.Customer(
FlightNbr varchar(50) NULL,
DeptDateTime datetime NULL,
Origin varchar(50) NULL,
Destination varchar(50) NULL,
FirstName varchar(50) NULL,
CustomerID varchar(50) NULL,
SeatRow varchar(50) NULL
) ON [PRIMARY]
--INSERT 3 rows into dbo.Customer table
GO
INSERT INTO dbo.Customer (FlightNbr, DeptDateTime, Origin, Destination,FirstName, CustomerID, SeatRow)
SELECT 0111, '2012-05-06 12:00:00.000','LAS','LGA','John','ASDF','1'
UNION ALL
SELECT 0112, '2012-05-03 12:00:00.000','LGA','LAS','John','ASDF','1'
UNION ALL
SELECT 0112, '2012-05-03 12:00:00.000','DEN','LAS','John','ASDF','1'
GO
--Create Table dbo.Ticket
CREATE TABLE dbo.Ticket (
CustomerID varchar(50) NULL,
TicketNumber char(13) NOT NULL,
TktAmount decimal(18, 4) NOT NULL
) ON [PRIMARY]
--Insert 2 rows into dbo.Ticket table
GO
INSERT INTO dbo.Ticket (CustomerID, TicketNumber, TktAmount)
SELECT 'ASDF',1110099988833,150.0000
UNION ALL
SELECT 'ASDF',22244466666666, 85.0000
GO
I don't see anything that tells you which flight either of the tickets are for? Which flight was ticket 1110099988833 purchased for and which one was ticket 22244466666666? There is nothing to further restrict the join between Customer and Ticket.
June 22, 2012 at 3:20 am
I am not sure if this is something close to what you were looking for. But I got three rows as you wanted.. Here check this out
select distinct C.* from Customer C with (nolock)
left join
Ticket T on
C.CustomerID = T.CustomerID
where T.CustomerID = C.CustomerID
OUTPUT:
FlightNbrDeptDateTimeOriginDestinationFirstNameCustomerIDSeatRow
1112012-05-06 12:00:00.000LASLGAJohnASDF1
1122012-05-03 12:00:00.000DENLASJohnASDF1
1122012-05-03 12:00:00.000LGALASJohnASDF1
🙂
June 22, 2012 at 3:42 am
Nthuloane.Marotholi (6/22/2012)
I am not sure if this is something close to what you were looking for. But I got three rows as you wanted.. Here check this outselect distinct C.* from Customer C with (nolock)
left join
Ticket T on
C.CustomerID = T.CustomerID
where T.CustomerID = C.CustomerIDOUTPUT:
FlightNbrDeptDateTimeOriginDestinationFirstNameCustomerIDSeatRow
1112012-05-06 12:00:00.000LASLGAJohnASDF1
1122012-05-03 12:00:00.000DENLASJohnASDF1
1122012-05-03 12:00:00.000LGALASJohnASDF1
🙂
The same join predicate need not be repeated - and by referencing a column from the left-joined table in the WHERE clause, you turn the left join into an inner join. This only serves to confuse the OP.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 22, 2012 at 4:14 am
Thanks Chris; will keep that in mind
June 22, 2012 at 9:59 am
This issue has been resolved. Our DBAs added another column and that gives us what we were looking for.
Thanks for all of your comments.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply