How do I join a table with no common field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I join a table with no common field?

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2011-05-06 : 09:21:32

I need to join in a table but it doesn't have a field in common with any of the other already joined tables, how can I add it in there?

Maybe a join isn't even what I need, all I need the table for is to be able to use a field from it in a WHERE clause to filter some data.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-06 : 09:23:27

Please make it more clear using example.

No, you're never too old to Yak'n'Roll if you're too young to die.

meef
Posting Yak Master

113 Posts

Posted - 2011-05-06 : 09:27:31

I'm not sure how to make it more clear without posting the entire stored procedure...

Basically, this table has a date column that I need so I can filter the data in a WHERE clause. I currently have 6 tables joined in the stored procedure, but this new table I need the date field from has nothing in common with them so I can't join it in there.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-06 : 09:30:16

If there's nothing to join to then you can't join it.

Maybe if you post the DDL for the tables in question (including primary keys), someone can help you out.

meef
Posting Yak Master

113 Posts

Posted - 2011-05-06 : 09:34:51

I'm not sure how to go about posting the DDL, can you explain?

EDIT: I'm reading through the FAQ now, hopefully I find it. How do I get the DDL in SQL Server?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-06 : 09:46:17

You can right-click on a table in Management Studio, choose the Script Table option, and then CREATE to a new window or clipboard. Paste the generated script into your post.

meef
Posting Yak Master

113 Posts

Posted - 2011-05-06 : 09:56:29

Thanks. It's going to be 8 tables though, is that too many to post?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-06 : 10:02:25

Probably not. Unless you've got 1000+ columns in each.

meef
Posting Yak Master

113 Posts

Posted - 2011-05-06 : 10:13:21

Ok, here goes.

Table 1:

USE [TBL_BL]
GO

/****** Object:  Table [dbo].[tbl_214_status]    Script Date: 05/06/2011 10:08:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_214_status](
	[tbl_214_status_id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[tbl_214_id] [uniqueidentifier] NOT NULL,
	[tbl_214_st_id] [uniqueidentifier] NULL,
	[bol_id] [uniqueidentifier] NULL,
	[record_num] [int] NOT NULL,
	[bol_number_a] [varchar](30) NULL,
	[pro_number_a] [varchar](30) NULL,
	[status_date] [varchar](8) NULL,
	[status_time] [varchar](8) NULL,
	[status_time_zone] [varchar](2) NULL,
	[status_code] [varchar](2) NULL,
	[status_reason_code] [varchar](2) NULL,
	[appt_status_code] [varchar](2) NULL,
	[appt_reason_code] [varchar](2) NULL,
	[status_city] [varchar](30) NULL,
	[status_state] [varchar](2) NULL,
	[equipment_scac] [varchar](4) NULL,
	[equipment_id] [varchar](10) NULL,
	[status_effective_date_a] [varchar](6) NULL,
	[status_effective_time_a] [varchar](4) NULL,
	[est_del_date] [varchar](50) NULL,
	[date_added] [smalldatetime] NULL,
	[loop] [varchar](4) NULL,
 CONSTRAINT [PK_tbl_edi_status] PRIMARY KEY CLUSTERED 
(
	[tbl_214_status_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_214_status] ADD  CONSTRAINT [DF_tbl_edi_status_tbl_edi_status_id]  DEFAULT (newid()) FOR [tbl_214_status_id]
GO

Table 2:

USE [TBL_BL]
GO

/****** Object:  Table [dbo].[edi_status_reason_descriptions]    Script Date: 05/06/2011 10:09:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[edi_status_reason_descriptions](
	[edi_status_reason_description_id] [uniqueidentifier] NOT NULL,
	[edi_status_reason_code] [varchar](3) NOT NULL,
	[edi_status_reason_description] [varchar](100) NOT NULL,
	[srd_display] [varchar](100) NULL,
 CONSTRAINT [PK_edi_status_reason_codes] PRIMARY KEY CLUSTERED 
(
	[edi_status_reason_description_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[edi_status_reason_descriptions] ADD  CONSTRAINT [DF_edi_status_reason_codes_edi_status_reason_code_id]  DEFAULT (newid()) FOR [edi_status_reason_description_id]
GO

Table 3:

USE [TBL_BL]
GO

/****** Object:  Table [dbo].[edi_appt_reason_descriptions]    Script Date: 05/06/2011 10:10:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[edi_appt_reason_descriptions](
	[edi_appt_reason_description_id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[edi_appt_reason_code] [varchar](3) NOT NULL,
	[edi_appt_reason_description] [varchar](100) NOT NULL,
	[ard_display] [varchar](100) NULL,
 CONSTRAINT [PK_edi_appt_reason_descriptions] PRIMARY KEY CLUSTERED 
(
	[edi_appt_reason_description_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[edi_appt_reason_descriptions] ADD  CONSTRAINT [DF_edi_appt_reason_descriptions_edi_appt_reason_description_id]  DEFAULT (newid()) FOR [edi_appt_reason_description_id]
GO

Table 4:

USE [TBL_BL]
GO

/****** Object:  Table [dbo].[tbl_214_isa]    Script Date: 05/06/2011 10:11:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_214_isa](
	[tbl_214_isa_id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[tbl_214_id] [uniqueidentifier] NOT NULL,
	[record_num] [int] NOT NULL,
	[ISA_01] [varchar](2) NULL,
	[ISA_02] [varchar](10) NULL,
	[ISA_03] [varchar](2) NULL,
	[ISA_04] [varchar](10) NULL,
	[ISA_05] [varchar](2) NULL,
	[ISA_06] [varchar](15) NULL,
	[ISA_07] [varchar](2) NULL,
	[ISA_08] [varchar](15) NULL,
	[ISA_09] [varchar](6) NULL,
	[ISA_10] [varchar](4) NULL,
	[ISA_11] [varchar](1) NULL,
	[ISA_12] [varchar](5) NULL,
	[ISA_13] [varchar](9) NULL,
	[ISA_14] [varchar](1) NULL,
	[ISA_15] [varchar](1) NULL,
	[ISA_16] [varchar](1) NULL,
	[IEA_01] [varchar](5) NULL,
	[IEA_02] [varchar](9) NULL,
 CONSTRAINT [PK_tbl_edi_isa_level] PRIMARY KEY CLUSTERED 
(
	[tbl_214_isa_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_214_isa] ADD  CONSTRAINT [DF_tbl_edi_isa_level_tbl_edi_isa_level_id]  DEFAULT (newid()) FOR [tbl_214_isa_id]
GO

Table 5:

USE [TBL_BL]
GO

/****** Object:  Table [dbo].[tbl_bol]    Script Date: 05/06/2011 10:11:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_bol](
	[bol_id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[client_id] [uniqueidentifier] NULL,
	[client_name] [varchar](50) NULL,
	[client_add] [varchar](100) NULL,
	[client_city] [varchar](50) NULL,
	[client_state] [varchar](2) NULL,
	[client_zip] [varchar](10) NULL,
	[client_sid] [varchar](50) NULL,
	[client_fob] [bit] NOT NULL,
	[client_div] [varchar](2) NULL,
	[customer_id] [uniqueidentifier] NULL,
	[customer_name] [varchar](50) NULL,
	[customer_add] [varchar](100) NULL,
	[customer_add1] [varchar](100) NULL,
	[customer_add2] [varchar](100) NULL,
	[customer_city] [varchar](50) NULL,
	[customer_state] [varchar](2) NULL,
	[customer_zip] [varchar](10) NULL,
	[customer_sid] [varchar](50) NULL,
	[customer_fob] [bit] NOT NULL,
	[customer_location] [varchar](50) NULL,
	[email] [varchar](500) NULL,
	[email_bcc] [varchar](500) NULL,
	[third_party_id] [uniqueidentifier] NULL,
	[third_party_name] [varchar](50) NULL,
	[third_party_add] [varchar](100) NULL,
	[third_party_city] [varchar](50) NULL,
	[third_party_state] [varchar](2) NULL,
	[third_party_zip] [varchar](10) NULL,
	[bol_number] [varchar](50) NULL,
	[carrier_id] [uniqueidentifier] NULL,
	[carrier_name] [varchar](50) NULL,
	[trailer_number] [varchar](50) NULL,
	[seal_number] [varchar](50) NULL,
	[pro_number] [varchar](50) NULL,
	[carrier_pro_id] [uniqueidentifier] NULL,
	[pay_type] [varchar](1) NULL,
	[prepaid_amt] [float] NULL,
	[cod] [decimal](18, 2) NULL,
	[cod_terms] [varchar](50) NOT NULL,
	[loaded_by] [int] NULL,
	[counted_by] [int] NULL,
	[tbl_req_date] [smalldatetime] NULL,
	[tbl_spot_quote] [varchar](50) NULL,
	[tbl_f_charge] [float] NULL,
	[tbl_notes] [varchar](5000) NULL,
	[special_instructions] [varchar](300) NULL,
	[bol_status] [varchar](50) NULL,
	[ship_date] [smalldatetime] NULL,
	[ref_number] [varchar](50) NULL,
	 [varchar](20) NULL,
	[order_units] [varchar](1) NULL,
	[deleted] [bit] NOT NULL,
	[check_accepted] [bit] NOT NULL,
	[declare_value] [float] NULL,
	[dba_id] [uniqueidentifier] NULL,
	[hazard] [bit] NOT NULL,
	[hasmat_name] [varchar](50) NULL,
	[hasmat_phone] [varchar](50) NULL,
	[confirm_date] [smalldatetime] NULL,
	[declare_value_per] [varchar](50) NULL,
	[est_charges_manual] [bit] NOT NULL,
	[accrue_amt_manual] [bit] NOT NULL,
	[export_date] [smalldatetime] NULL,
	[bolpk] [int] IDENTITY(100001,1) NOT NULL,
	[mastid] [int] NOT NULL,
	[fp_tmstmp] [smalldatetime] NULL,
	[fp_btsinvdt] [smalldatetime] NULL,
	[fp_btsinv] [decimal](10, 0) NULL,
	[bol_status_id] [uniqueidentifier] NULL,
	[fiscalpd] [varchar](6) NULL,
	[accrueamt] [decimal](10, 2) NULL,
	[clientscac] [varchar](50) NULL,
	[clientcanm] [varchar](36) NULL,
	[acno] [varchar](20) NULL,
	[lhok] [numeric](1, 0) NULL,
	[io] [varchar](1) NULL,
	[removed_by_user_id] [uniqueidentifier] NULL,
	[crp_status_id] [uniqueidentifier] NULL,
	[mode_id] [uniqueidentifier] NULL,
	[routescac] [uniqueidentifier] NULL,
	[routeused] [varchar](1) NULL,
	[routeamt] [decimal](10, 2) NULL,
	[rate_status] [tinyint] NULL,
	[imported] [tinyint] NOT NULL,
	[datasrc] [varchar](1) NULL,
	[user_added] [uniqueidentifier] NULL,
	[date_added] [smalldatetime] NULL,
	[user_lused] [uniqueidentifier] NULL,
	[date_lused] [smalldatetime] NULL,
	[master_customer_id] [uniqueidentifier] NULL,
	[client_carrier_id] [uniqueidentifier] NULL,
	[old_bolpk] [int] NULL,
	[old_mastid] [int] NULL,
	[delivery_notes] [varchar](1000) NULL,
	[delivery_date] [smalldatetime] NULL,
	[delivered] [bit] NOT NULL,
	[one_time_entry] [bit] NOT NULL,
	[i_fbttl] [numeric](10, 2) NULL,
	[i_lhamt] [numeric](10, 2) NULL,
	[i_fsamt] [numeric](10, 2) NULL,
	[i_acamt] [numeric](10, 2) NULL,
	[sfs_sent] [bit] NOT NULL,
	[master_bol_number] [varchar](50) NULL,
	[master_bol_creation_date] [smalldatetime] NULL,
	[master_bol_stop_num] [int] NULL,
	[req_del_date] [smalldatetime] NULL,
	[delivered_date] [varchar](8) NULL,
	[force_delivered] [bit] NOT NULL,
	[force_delivered_date] [smalldatetime] NULL,
	[force_delivered_user] [uniqueidentifier] NULL,
	[exception_handled] [bit] NOT NULL,
	[sp_match_bol] [bit] NOT NULL,
	[sp_matched_bol] [bit] NULL,
	[is_parcel] [bit] NOT NULL,
	[ex_id] [uniqueidentifier] NULL,
	[ex_name] [varchar](50) NULL,
	[ex_add] [varchar](100) NULL,
	[ex_add1] [varchar](100) NULL,
	[ex_add2] [varchar](100) NULL,
	[ex_city] [varchar](50) NULL,
	[ex_state] [varchar](2) NULL,
	[ex_zip] [varchar](10) NULL,
	[client_add1] [varchar](100) NULL,
	[client_add2] [varchar](100) NULL,
	[fp_sntmstmp] [datetime] NULL,
	[client_country] [varchar](50) NULL,
	[customer_country] [varchar](50) NULL,
	[third_party_country] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_bol] PRIMARY KEY CLUSTERED 
(
	[bol_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_bl_id]  DEFAULT (newid()) FOR [bol_id]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_client_fob]  DEFAULT ((0)) FOR [client_fob]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_customer_fob]  DEFAULT ((0)) FOR [customer_fob]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_prepaid]  DEFAULT ((0)) FOR [pay_type]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_cod_collect]  DEFAULT ((0)) FOR [cod_terms]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_loaded_shipper]  DEFAULT ((0)) FOR [loaded_by]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_deleted]  DEFAULT ((0)) FOR [deleted]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_check_accepted]  DEFAULT ((0)) FOR [check_accepted]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_hazard]  DEFAULT ((0)) FOR [hazard]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_est_charges_manual]  DEFAULT ((1)) FOR [est_charges_manual]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_accrue_amt_manual]  DEFAULT ((0)) FOR [accrue_amt_manual]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_mastid]  DEFAULT ((0)) FOR [mastid]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_imported]  DEFAULT ((0)) FOR [imported]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_delivered]  DEFAULT ((0)) FOR [delivered]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_one_time_entry]  DEFAULT ((0)) FOR [one_time_entry]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_sfs_sent]  DEFAULT ((0)) FOR [sfs_sent]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_force_delivered]  DEFAULT ((0)) FOR [force_delivered]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_exception_handled]  DEFAULT ((0)) FOR [exception_handled]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_sp_match_bol]  DEFAULT ((0)) FOR [sp_match_bol]
GO

ALTER TABLE [dbo].[tbl_bol] ADD  CONSTRAINT [DF_tbl_bol_is_parcel]  DEFAULT ((0)) FOR [is_parcel]
GO



Table 6:


USE [TBL_BL]
GO

/****** Object:  Table [dbo].[tbl_ref_nums]    Script Date: 05/06/2011 10:11:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_ref_nums](
	[ref_num_id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[bol_id] [uniqueidentifier] NOT NULL,
	[ref_num] [varchar](50) NULL,
	[user_added] [uniqueidentifier] NULL,
	[date_added] [smalldatetime] NULL,
	[user_lused] [uniqueidentifier] NULL,
	[date_lused] [smalldatetime] NULL,
 CONSTRAINT [PK_tbl_ref_nums] PRIMARY KEY CLUSTERED 
(
	[ref_num_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_ref_nums] ADD  CONSTRAINT [DF_tbl_ref_nums_ref_num_id]  DEFAULT (newid()) FOR [ref_num_id]
GO



Table 7:

USE [TBL_BL]
GO

/****** Object:  Table [dbo].[tbl_214_datatable]    Script Date: 05/06/2011 10:12:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_214_datatable](
	[tbl_214_datatable_id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[bol_id] [uniqueidentifier] NOT NULL,
	[date_last_updated] [smalldatetime] NOT NULL,
	[filename] [varchar](500) NOT NULL,
	[scac] [varchar](4) NULL,
	[client_code] [varchar](15) NULL,
	[message_date] [varchar](8) NULL,
	[message_time] [varchar](8) NULL,
	[message_number] [varchar](9) NULL,
	[test_indicator] [varchar](1) NULL,
	[pro] [varchar](30) NULL,
	[bol] [varchar](30) NULL,
	[shipper] [varchar](60) NULL,
	[shipper_add1] [varchar](55) NULL,
	[shipper_add2] [varchar](55) NULL,
	[shipper_city] [varchar](30) NULL,
	[shipper_state] [varchar](2) NULL,
	[shipper_zip] [varchar](15) NULL,
	[consignee] [varchar](60) NULL,
	[consignee_add1] [varchar](55) NULL,
	[consignee_add2] [varchar](55) NULL,
	[consignee_city] [varchar](30) NULL,
	[consignee_state] [varchar](2) NULL,
	[consignee_zip] [varchar](15) NULL,
	[contact_name] [varchar](50) NULL,
	[actual_pickup_date] [varchar](8) NULL,
	[orig_est_deliver_date] [varchar](8) NULL,
	[curr_est_deliver_date] [varchar](8) NULL,
	[appt_date] [varchar](8) NULL,
	[delivered_date] [varchar](8) NULL,
	[interline] [varchar](1) NULL,
	[interline_scac] [varchar](4) NULL,
	[interline_point] [varchar](30) NULL,
	[exception] [varchar](50) NULL,
	[spec_requirements] [varchar](50) NULL,
	[weight] [varchar](10) NULL,
	[units] [varchar](1) NULL,
	[copied] [bit] NOT NULL,
	[billed] [bit] NOT NULL,
 CONSTRAINT [PK_tbl_edi_datatable] PRIMARY KEY CLUSTERED 
(
	[tbl_214_datatable_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_214_datatable] ADD  CONSTRAINT [DF_tbl_edi_datatable_tbl_edi_datatable_id]  DEFAULT (newid()) FOR [tbl_214_datatable_id]
GO

ALTER TABLE [dbo].[tbl_214_datatable] ADD  CONSTRAINT [DF_tbl_214_datatable_copied]  DEFAULT ((0)) FOR [copied]
GO

ALTER TABLE [dbo].[tbl_214_datatable] ADD  CONSTRAINT [DF_tbl_214_datatable_billed]  DEFAULT ((0)) FOR [billed]
GO



And this is the table I need the date field from (edi_start_date:

[code]USE [TBL_BL]
GO

/****** Object:  Table [dbo].[tbl_carrier_access]    Script Date: 05/06/2011 10:12:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_carrier_access](
	[carrier_access_id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[carrier_id] [uniqueidentifier] NULL,
	[dba_id] [uniqueidentifier] NULL,
	[exclude_accrual] [bit] NOT NULL,
	[lower_mode_id] [uniqueidentifier] NULL,
	[upper_mode_id] [uniqueidentifier] NULL,
	[lbs] [int] NULL,
	[edi_start_date] [smalldatetime] NULL,
	[date_added] [smalldatetime] NULL,
	[user_added] [uniqueidentifier] NULL,
	[date_lused] [smalldatetime] NULL,
	[user_lused] [uniqueidentifier] NULL,
 CONSTRAINT [PK_tbl_carrier_access] PRIMARY KEY NONCLUSTERED 
(
	[carrier_access_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_carrier_access] ADD  CONSTRAINT [DF_tbl_carrier_access_carrier_access_id]  DEFAULT (newid()) FOR [carrier_access_id]
GO

ALTER TABLE [dbo].[tbl_carrier_access] ADD  CONSTRAINT [DF_tbl_carrier_access_exclude_accrual]  DEFAULT ((0)) FOR [exclude_accrual]
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-06 : 10:26:36

Carrier_Access_ID needs to be added to one of the other tables (tbl_214_status perhaps).

What's up with all those uniqueidentifiers? Usually a poor choice for primary keys.

Sholabor
Starting Member

2 Posts

Posted - 2011-05-07 : 14:56:06

for full details of SQL Server check out this web site unspammed

everything is good with faith

meef
Posting Yak Master

113 Posts

Posted - 2011-05-09 : 09:00:12

Thanks, I actually got it working by using this:

INNER JOIN tbl_carrier c ON c.scac_code = p.scac
INNER JOIN tbl_carrier_access a ON a.carrier_id = c.carrier_id AND a.dba_id = tb.dba_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources