SQL with not related 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL with not related tables

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-06-03 : 13:56:13

I have two tables CustEmail and CustAddress as follows


 

CustEmail                  CustAddress

CID     EID               CID     AID

C1      E1                C1      A1

C1      E2                C1      A2

C1      E3

C1      E4

I need to write a single query using these 2 tables to get the following output.

Output


CID     EID     AID

C1      E1      A1

C1      E2      A2

C1      E3      NULL

C1      E4      NULL

is this possible?
------------------------
I think, therefore I am - Rene Descartes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-03 : 14:02:04

SELECT ce.CID, ce.EID, ca.AID
FROM CustEmail ce
LEFT OUTER JOIN CustAddress ca
ON ce.CID = ca.CID

Tara

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-06-03 : 14:09:02

Tara..this is not giving the desired o/p.
Note that both tables have cid column has value 'C1'
In the o/p i need data for only 2 rows. Hope i am not confusing you.!

------------------------
I think, therefore I am - Rene Descartes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-03 : 14:25:04

Apparently I am not understanding your data since it has been generalized so much. Could you provide a better example as well as DDL for the table and DML for the sample data?

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-03 : 14:55:03

With the data you've provided, the only way to get the output you want is to do something hacky like this (slightly modifying Tara's query):

SELECT ce.CID, ce.EID, ca.AID
FROM CustEmail ce
LEFT OUTER JOIN CustAddress ca
ON RIGHT(ce.EID, 1) = RIGHT(ca.AID, 1)

Otherwise, I don't see a way to uniquely relate the records the way you want them to. Is there no other column(s) that can be used to relate the tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources