Prev: Finding tables with specific columns
Next: reading image data type
From: axapta on 16 Jul 2008 13:25
Hi Group,
I have the following query, but I need to add a column from another table in
the resultset. No matter what I do I end up with what seems like a
cartesian product. I want the same number of records that this query gives
me but just with the additional field (accessstatus) from the other table.


SELECT p.harea, p.propref, p.no, p.flatfl, p.address1, p.address2,
p.address3, p.postcode, p.propstatus, p.changedate, p.tenstartdate,
p.tentitle, p.tenfirstname, p.tensurname, p.hometel, p.othertel, p.worktel,
p.voiddate, p.incontract, p.lastsafetydate, p.currentplandate,
p.hsispropref, p.iworldref
FROM FMC.dbo.property p
WHERE (p.incontract='Yes')

The other table is called programme the key to this table and the property
table is the propref so the join will be where

pr.propref = p.propref which I had when I was trying to get this to work but
as I said more records were returned.

The field I want to bring back from the Programme table is called
accessstatus - this is in addition to the fields above in the property
table.

TIA


From: Tom Cooper on 16 Jul 2008 13:53
If you are getting more than one rows back with the join, that means that
one or more rows in property has multiple matching rows in programme. And
those rows might have different values in accessstatus. So to answer your
question, you need to tell us how to pick which accessstatus you want in
that case. Also tell us what version of SQL Server you are using.

Tom

"axapta" <jas.jackie(a)gmail.com> wrote in message
news:4E6CD175-5D5D-4E3E-87C4-D924D7C79045(a)microsoft.com...
> Hi Group,
> I have the following query, but I need to add a column from another table
> in the resultset. No matter what I do I end up with what seems like a
> cartesian product. I want the same number of records that this query
> gives me but just with the additional field (accessstatus) from the other
> table.
>
>
> SELECT p.harea, p.propref, p.no, p.flatfl, p.address1, p.address2,
> p.address3, p.postcode, p.propstatus, p.changedate, p.tenstartdate,
> p.tentitle, p.tenfirstname, p.tensurname, p.hometel, p.othertel,
> p.worktel, p.voiddate, p.incontract, p.lastsafetydate, p.currentplandate,
> p.hsispropref, p.iworldref
> FROM FMC.dbo.property p
> WHERE (p.incontract='Yes')
>
> The other table is called programme the key to this table and the property
> table is the propref so the join will be where
>
> pr.propref = p.propref which I had when I was trying to get this to work
> but as I said more records were returned.
>
> The field I want to bring back from the Programme table is called
> accessstatus - this is in addition to the fields above in the property
> table.
>
> TIA
>
>


 | 
Pages: 1
Prev: Finding tables with specific columns
Next: reading image data type