Strange output from join query

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 2005 Forums

Transact-SQL (2005)

Strange output from join query

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-09-21 : 14:32:09

I've got this query

SELECT main.*, der.cder_transmission, der.cder_fueltype, co.tech_value_string FROM vwAllMatrixWithLombardAndShortModel main
LEFT JOIN PUB_CAR..CapDer der ON main.capid = cder_id
LEFT JOIN PUB_CAR..NVDTechnical co ON main.capid = tech_id
WHERE co.tech_techcode = 67

For each CapID there should be 12 rows returned - and all the rows should have the same co.tech_value_string

But what happens is I get 24 rows per capid - 12 with one value and 12 with another.

Can anyone see what the problem might be?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-21 : 15:57:21

Yes.
You are JOINing on same "base" column.

If you have 2 records that match in CapDer and 12 records in NVD, you will get 24 records in total.

Replace the WHERE with AND and see if you see any change in query output.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 04:11:09

quote:
Originally posted by Mondeo

I've got this query

SELECT main.*, der.cder_transmission, der.cder_fueltype, co.tech_value_string FROM vwAllMatrixWithLombardAndShortModel main
LEFT JOIN PUB_CAR..CapDer der ON main.capid = cder_id
LEFT JOIN PUB_CAR..NVDTechnical co ON main.capid = tech_id
WHERE co.tech_techcode = 67

For each CapID there should be 12 rows returned - and all the rows should have the same co.tech_value_string

But what happens is I get 24 rows per capid - 12 with one value and 12 with another.

Can anyone see what the problem might be?

Thanks

this menas there's one to many relationship existing between a table and your base table. which is table field thats having different values in 24 rows.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources