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.
| Author |
Topic |
|
nicki_assumption
Yak Posting Veteran
69 Posts |
 Posted - 2006-02-26 : 14:33:48
|
| Hi- I'm trying to join a 3 tables. The field they have in common in FID. tblFollowup2 is the table that holds all possible FIDs, the other two may or may not have the FID. I'm trying to get all the data from all of them in this query, whether or not the FID exists in all of the tables. select FIDS.fid, prid,itemname, itemdata from (select distinct FID from tblFollowup2) FIDSleft join tblSIFups aon FIDS.fid = a.fIDleft join tblFupProcs bon FIDS.fid = b.fIDThe ONLY thing they have in common is FID and there can be multiple. The reason I can tell it's cartesian is that I shouldn't get multiple FID, PRID combinations in my output and I am. I checked in the table where PRID exists, which is tblFupProcs and they are not duped there. The two tables I'm left joining have multiple entries for FID though.I can put the scripts here for creating the tables but I have a feeling someone will see something blatantly wrong with the query. It seems like a straight forward left join scenario. Thanks a lotNicki |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-02-27 : 08:50:39
|
| Some DDL and sample data would help, but I suspect you are trying to join two transaction tables. you cannot do this unless they are summarized by a common set of columns, otherwise you will see the effect you are seeing.In addition, you have prid, itemname, itemdata all listed in your SELECT without any prefixes, so we have no idea which tables these should be pulling from, so it is impossible to figure out what you are trying to do.So, if you can post some sample data and expected results, we can help, but otherwise it is impossible to tell what you have or what you want without more specific information. |
 |
|
|
|
|
|
|
|
left join producing cartesian output