uniqueidentifier linking with subselect

[don2241]

Hi
Is there a trick when linking using a sub-select where the [id] is of type uniqueidentifier. I will demonstrate with a simple query. This query should simply link to itself but I get null values in the first column, what am I doing wrong.

select
x.id,
c.id

from Customer c

left outer join(
select id
from Customer
)x on
x.id=c.id


Thanks

 

[timfoster]

I just tried exactly the same code and get all rows returned. However, unless I'm missing something in reading this, you're never going to get anything other than two identical columns as you're joining a single column in a table. Your outer join will never return anything other than a regular inner join. The column can't be populated and not populated at the same time.

To achieve what your code does now, you can just as easily have select id, id from customers. Your join is doing nothing (other than slowing your code down)

 

[don2241]

Hi
Sorry I missed another join

select x.id, x.cid, c.id

from Customer c

left outer join(

select id, con.contactID as cid
from Customer c
join Contact con on
con.custID=c.id
group by c.id, con.custID

)x on
x.id=c.id


Since my sub-select might return duplicates of the id column the x.id and x.cid column is returning null (because of the left outer join). Where there is a match shouldn't the query just duplicate the row data based on the amount of cid's in the sub-select.

I am just trying to get my point through via this simple query. My "real" sub-select is using different tables that have the id and contactID. All I am trying to do is link a sub set of data with a table but it does not seem to work if my sub-set data contain duplicates, is this true?.

Thanks

 

[SQLSister]

Let's start with some terminology here. You are not using a subselect; you are using a derived table. There is a huge performance difference between the two. A derived table is in the join, a subselect is in the select list of columns. The derived table is the better technique, so you are doing the correct thing putting it there.

Perhaps we could better answer the question if you gave us some sample data, sample desired results, results you are getting and the actual query.

"NOTHING is more important in a database than integrity." ESquared