sql query has me stumped...

[Anti-Rich]

hi everyone,

i have a question relating to inner joins. i know that an inner join is used to join two tables
in order to get relating data between them, but i have a table which has three fields which
can get data from 3 other tables, none of which relate to each other (therefore, i would think,
eliminating an inner join within an inner join), so can anyone show me how to do this properly,
because the query i have below returns 3 rows, when i know there is only one.

[SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]HazNo[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LocationID[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]BuildingID[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]EmployeeID[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] e[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]FirstName[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] e[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LastName [/SIZE]
[SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] tblHazRegister h[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] tblBuildingLocation b[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] tblEmployee e [/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]HazNo [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2]0 [/SIZE][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][SIZE=2] e[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]EmployeeID [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]EmployeeID[/SIZE]

cheers all
adam

 

[cjard]

this is not really an ansi sql inner join (it doesnt use SELECT .. FROM.. INNER JOIN .. ON .. syntax), there is a cartesian join here because one of your tables is not joined with any predicate

if you have a table with 2 rows and a table with 3 then saying:


SELECT * FROM table1, table2

gets 6 rows, because there is no predicate:

a,1
a,2
a,3
b,1
b,2
b,3

-

If you have one table that bears no relation to the others, and you join it in, then the resultset will have N*M rows where N is the number it would have without the table, and M is the number of rows in the table

From your posted query, you dont relate tblBuildingLocation in with anything.
If this sql:

[FONT=Courier New][COLOR=#0000ff]SELECT[/COLOR][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]HazNo[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LocationID[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]BuildingID[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]EmployeeID[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] e[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]FirstName[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] e[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LastName [/SIZE][/FONT]
[FONT=Courier New][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] tblHazRegister h[/SIZE][SIZE=2][COLOR=#808080], [/COLOR][/SIZE][SIZE=2]tblEmployee e [/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]HazNo [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2]0 [/SIZE][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][SIZE=2] e[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]EmployeeID [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]EmployeeID[/SIZE][/FONT]
[FONT=Courier New]                ^^^^^^^^^^ note tblBuildinglocation REMOVED!!![/FONT]
[FONT=Courier New]

[/FONT]

returns 1 row, and tblBuildingLocation has 3 rows, then you jsut found out why the result is 3 rows (you dont say how the table relates to the others, so SQLS just joins it in with everything)

-

If youre still stumped, run this:

SELECT b.*, h.HazNo, h.LocationID, h.BuildingID, h.EmployeeID, e.FirstName, e.LastName
FROM tblHazRegister h, tblBuildingLocation b, tblEmployee e WHERE h.HazNo =0 AND e.EmployeeID = h.EmployeeID

then youll see why you have 3 rows


-
So how could you join in a table that isnt related to any other table?
A better question is WHY would you want to join in an unrelated table?

Why inner join types_of_chalk into a query_about_cheeses ?