Unrelated Tables - SQL Server Forums

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

Unrelated Tables

Author

Topic

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-03-07 : 10:16:42

We're getting duplicate records b/c we're joining two tables that really don't have a unique common field. Does anyone know of a way to join two tables that aren't completely related?? I know this is contradictory to the entire concept of a relational db :).

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-07 : 10:32:29

Are they exact duplicates? Distinct could remove that after that, there are options (like using a temp table ) but they can be pretty slow in performance.

Please give us DDL for the tables/SQL for the join and some sample data and expected results...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-03-07 : 10:36:16

How do I copy the DDL? I know how to list the DML.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-07 : 11:00:37

if you are using query analyser (SQL 2000), then open the object browser (F8). Find the database, then under "user tables" find the table. right-click and drag into the query pane - when you "release" it will pop up with a list of option - select the "create" option.

BE VERY CAREFUL NOT TO RUN THIS - just copy the dll created. FOR SAFETY PLEASE DO THIS IN YOUR DEV/TEST ENVIRONMENT.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-03-07 : 12:02:00

OK. Here is the query that's producing 24 records when it should only be producing 8 and the DDL of both tables are below it. Help.

--PROPERTYITEM
INSERT INTO [USCONDEX_Production].[dbo].[propertyItem]( [propertyId], [ItemId])
SELECT Property.propertyId, ITEM.ItemID
FROM ITEM RIGHT OUTER JOIN
miamiherald ON ITEM.StartDate = miamiherald.FirstInsertDate AND ITEM.Price = miamiherald.PropertyPrice AND ITEM.Classified = convert(int,miamiherald.AdNumber) LEFT OUTER JOIN
Property ON property.adprintid = miamiherald.adprintid
WHERE validAD=1

Here is our table DDL -

CREATE TABLE [propertyItem] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[propertyId] [int] NOT NULL ,
[ItemId] [int] NOT NULL
) ON [PRIMARY]
GO
--------------------------------------------------------------
Here is the our content partners table DDL

CREATE TABLE [miamiherald] (
[AccountNumber] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdNumber] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstInsertDate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TraceAdNumber] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdvertiserName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdvertiserAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdvertiserEmail] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdvertiserCompany] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdvertiserURL] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdvertiserPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdvertiserType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyBuilding] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyStreetAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyZipCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyUnitNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyBedrooms] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyBathrooms] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertySquareFeet] [float] NULL ,
[PropertyDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyCommBroker] [float] NULL ,
[PropertyPrice] [money] NULL ,
[PropertyAssocFee] [money] NULL ,
[PropertyUnitFloor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyTotalFloors] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HasDen] [bit] NULL ,
[HasLoft] [bit] NULL ,
[HasView] [bit] NULL ,
[isPenthouse] [bit] NULL ,
[AdPrintId] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[validAd] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources