Problem in join query - 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

Other Forums

MS Access

Problem in join query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-03-13 : 08:41:14

I am getting weird behavior for the join query
When I am runing following query in ms access it return correct result with as many rows as i could expect

SELECT equip_sch_trans.Original_cost, equip_sch_trans.Equip_description, equip_sch_trans.Equip_id,
vendor.vendor_name,tax_jurisdiction.tax_jurisdiction,
div_master.Div_Location, equip_sch_trans.Trans_id FROM tax_jurisdiction ,div_master, equip_sch_trans, vendor where equip_sch_trans.vendor_id = vendor.vendor_id and tax_jurisdiction.tax_jurisdiction_id = div_master.tax_jurisdiction_id and div_master.Div_id = equip_sch_trans.Div_id and equip_sch_trans.trans_id = 26

But when i am runing it through .net it is giving only single record
Problem is not with .net code it will give as many record for other query as i expected

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-13 : 08:43:59

Make sure that you are connecting to proper server and database from .net code.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 08:45:05

[code]SELECT equip_sch_trans.Original_cost,
equip_sch_trans.Equip_description,
equip_sch_trans.Equip_id,
vendor.vendor_name,
tax_jurisdiction.tax_jurisdiction,
div_master.Div_Location,
equip_sch_trans.Trans_id
FROM tax_jurisdiction
INNER JOIN div_master ON div_master.tax_jurisdiction_id = tax_jurisdiction.tax_jurisdiction_id
INNER JOIN equip_sch_trans ON equip_sch_trans.Div_id = div_master.Div_id
INNER JOIN vendor ON vendor.vendor_id = equip_sch_trans.vendor_id
WHERE equip_sch_trans.trans_id = 26[/code]

E 12°55'05.25"
N 56°04'39.16"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-03-13 : 08:53:02

it is saying syntax error

SELECT equip_sch_trans.Original_cost,
equip_sch_trans.Equip_description,
equip_sch_trans.Equip_id,
vendor.vendor_name,
tax_jurisdiction.tax_jurisdiction,
div_master.Div_Location,
equip_sch_trans.Trans_id
FROM tax_jurisdiction
INNER JOIN div_master ON div_master.tax_jurisdiction_id = tax_jurisdiction.tax_jurisdiction_id
INNER JOIN equip_sch_trans ON equip_sch_trans.Div_id = div_master.Div_id
INNER JOIN vendor ON vendor.vendor_id = equip_sch_trans.vendor_id
WHERE equip_sch_trans.trans_id = 26

also If i use
SELECT equip_sch_trans.Original_cost, equip_sch_trans.Equip_description, equip_sch_trans.Equip_id,
vendor.vendor_name, tax_jurisdiction.tax_jurisdiction,div_master.Div_Location,
equip_sch_trans.Trans_id
FROM tax_jurisdiction ,div_master, equip_sch_trans, vendor
where equip_sch_trans.vendor_id = vendor.vendor_id
and tax_jurisdiction.tax_jurisdiction_id = div_master.tax_jurisdiction_id
and div_master.Div_id = equip_sch_trans.Div_id
and equip_sch_trans.trans_id = 26

still only one result

If i remove tax_jurisdiction table it works fine

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-03-13 : 08:58:53

SELECT equip_sch_trans.Original_cost, equip_sch_trans.Equip_description,
equip_sch_trans.Equip_id, vendor.vendor_name,div_master.Div_Location,
equip_sch_trans.Trans_id FROM div_master, equip_sch_trans, vendor
where equip_sch_trans.vendor_id = vendor.vendor_id
and div_master.Div_id = equip_sch_trans.Div_id
and equip_sch_trans.trans_id = 26

This works fine

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-03-14 : 02:36:39

Any idea what could be wrong with the joining of tax_jurisdiction

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 03:09:59

Are there matching records?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-14 : 07:10:16

>>But when i am runing it through .net it is giving only single record

Show us your .net code.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-14 : 08:35:40

Also, you have posted in the Access forum, so I assume you are using Access. In JET SQL, you need to nest multiple INNER JOINS, you can't state them one after another like you can in T-SQL.

so, you must write:

inner join (a inner join (b inner join c on ...) on ... ) on ...

It's a pain, and why I pretty much always use the query designer in Access to write Jet SQL statements.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 08:41:01

I think you can past the suggestion posted 03/13/2008 : 08:45:05 and Access will reformat.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources