July 13, 2017 at 3:04 pm
Hello my friends ,
i am seeking for your assistant in building query that helps me to join three tables and display the result from both tables to explain more i have one table that has the following coREATE TABLE [dbo].[Main](
[LastName] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[Rloc] [varchar](50) NULL,
[Class] [varchar](50) NULL,
[Fare Basis] [varchar](50) NULL,
[Fltno] [varchar](50) NULL,
[Status] [varchar](50) NULL,
[NetFare] [varchar](50) NULL,
[AgentID] [varchar](50) NULL,
[IssOffCode] [varchar](50) NULL,
[Flownstatus] [varchar](50) NULL,
[TicketNo] [varchar](50) NULL,
[DateofIssue] [varchar](50) NULL,
[FltDate] [varchar](50) NULL,
[Departure] [varchar](50) NULL,
[Arrival] [varchar](50) NULL
INSERT [Main] ([LastName], [FirstName], [Rloc], [Class], [Fare Basis], [Fltno], [Status], [NetFare], [AgentID], [IssOffCode], [Flownstatus], [TicketNo], [DateofIssue], [FltDate], [Departure], [Arrival]) VALUES (N'lastname1', N'firstnameMR1', N'AAAH93', N'Y', N'YOW', N'0852', N'ETKT', N'377.78', N'2437OW/GS', N'10014530', N'ELFT', N'555 2300009285/1', N'17/04/2012 18:07:44', N'25/04/2012', N'del', N'BAH')
INSERT [Main] ([LastName], [FirstName], [Rloc], [Class], [Fare Basis], [Fltno], [Status], [NetFare], [AgentID], [IssOffCode], [Flownstatus], [TicketNo], [DateofIssue], [FltDate], [Departure], [Arrival]) VALUES (N'lastname2', N'firstnameMR2', N'AAAK3W', N'Y', N'YOW', N'0853', N'ETKT', N'466.33', N'2437OW/GS', N'100145422', N'ELFT', N'555 2300009283/1', N'17/04/2012 18:07:44', N'24/05/2012', N'BAH', N'del')
INSERT [Main] ([LastName], [FirstName], [Rloc], [Class], [Fare Basis], [Fltno], [Status], [NetFare], [AgentID], [IssOffCode], [Flownstatus], [TicketNo], [DateofIssue], [FltDate], [Departure], [Arrival]) VALUES (N'lastname3', N'firstnameMR3', N'AAAK3W', N'Y', N'YOW', N'0852', N'ETKT', N'377.78', N'2437OW/GS', N'100140', N'ELFT', N'555 2300009288/1', N'17/04/2012 18:07:44', N'25/04/2012', N'del', N'BAH')
CREATE TABLE [dbo].[Contacts](
[PNR Number] [varchar](50) NULL,
[FLTNO] [varchar](50) NULL,
[Flt_Date] [varchar](50) NULL,
[DEPART] [varchar](50) NULL,
[DESTIN] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[TicketNumber] [varchar](50) NULL,
[Home Phone] [varchar](50) NULL,
[Business Phone] [varchar](50) NULL,
[Other Phone] [varchar](50) NULL,
[Mobile Phone] [varchar](50) NULL,
[Travel Phone] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Address] [varchar](50) NULLINSERT [Contacts] ([PNR Number], [FLTNO], [Flt_Date], [DEPART], [DESTIN], [FirstName], [LastName], [TicketNumber], [Home Phone], [Business Phone], [Other Phone], [Mobile Phone], [Travel Phone], [Email], [Address]) VALUES (N'AAAH93', N'0852', N'17/04/2012 0:00', N'del', N'BAH', N'firstnameMR1', N'lastname1', N'555 2300009285/1', N'', N'', N'', N'CTCM-1 +917727700', N'', N'CTCE email11@GMAIL.COM', N'')
INSERT [Contacts] ([PNR Number], [FLTNO], [Flt_Date], [DEPART], [DESTIN], [FirstName], [LastName], [TicketNumber], [Home Phone], [Business Phone], [Other Phone], [Mobile Phone], [Travel Phone], [Email], [Address]) VALUES (N'AAAK3W', N'0853', N'17/04/2012 0:00', N'BAH', N'del', N'firstnameMR2, N'lastname2', N'555 2300009283/1', N'', N'', N'', N'CTCM-1 +917777030', N'', N'CTCE-1 email222@GMAIL.COM', N'')
INSERT [Contacts] ([PNR Number], [FLTNO], [Flt_Date], [DEPART], [DESTIN], [FirstName], [LastName], [TicketNumber], [Home Phone], [Business Phone], [Other Phone], [Mobile Phone], [Travel Phone], [Email], [Address]) VALUES (N'AAAK3W', N'0852', N'25/04/2012 0:00', N'del', N'BAH', N'firstnameMR3', N'lastname3', N'555 2300009288/1', N'', N'', N'', N'CTCM-1 +917777030', N'', N'CTCE-1 email233@GMAIL.COM', N'')
CREATE TABLE [dbo].[gender](
[Surname] [varchar](50) NULL,
[Firstname] [varchar](50) NULL,
[Gender] [varchar](50) NULL,
[RLOC] [varchar](50) NULL
) ON [PRIMARY]
INSERT [Gender] [Surname], [Firstname], [Gender], [RLOC]) VALUES (N'lastname1', N'firstnameMR1',N'AAAH93',N'Male')
INSERT [Gender] [Surname], [Firstname], [Gender], [RLOC]) VALUES (N'lastname2', N'firstnameMR2',N'AAAK3W',N'Male')
INSERT [Gender] [Surname], [Firstname], [Gender], [RLOC]) VALUES (N'lastname2', N'firstnameMR2',N'AAAK3W',N'Male')
the result needed
i need to have view or query to display the main table facing each row the matching row from table contact and their gender from gender report.
keeping in mind that the gender table i have only Rloc column and lastname and first name as way to compare there is no single column that is matched with both.
each RLOC can contain single or multiple names so its not unique.
thank you in advance and appreciates your always help
regards
Alhakimi
July 13, 2017 at 4:17 pm
You have no primary key on any of the tables? That design looks painful to deal with. There is no nice way to set up a foreign key constraint on that.
So, if it was me, I'd be tempted to redesign that. BUT if that is not an option, I'd join on whatever columns you can find that make logical sense to match up on.
For example, something like:
SELECT *
FROM MAIN
JOIN GENDER ON MAIN.LastName = GENDER.Surname
AND MAIN.FirstName = GENDER.Firstname
AND MAIN.RLOC = GENDER.RLOC
JOIN CONTACTS ON MAIN.LastName = CONTACTS.LastName
AND MAIN.FirstName = CONTACTS.FirstName
AND MAIN.FltNo = CONTACTS.FltNo
But it really depends on what you are trying to do. This design, to me seems strange. But I believe the above should work, although I'd change the SELECT * to SELECT the columns you care about as there will be duplicate columns.
This query would be a lot easier to read, understand and work with if the data was normalized, but I know that is easier said than done.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply