Inner join SQL not working - little help

[MottoK]

Hi - I am trying to write a piece of SQL within ASP to bring back a recordset and keep getting "Page cannot be displayed" SQL is below:

SQLOpDetails=("select * FROM OperatorDetails_tbl, Manager_tbl INNER JOIN Manager_tbl ON OperatorDetails_tbl.ManagerID = Manager_tbl.ManagerID WHERE OperatorDetails_tbl.FMSID=('"&varSearchFMSID&"')")

Any ideas?

P.S. this works fine:

SQLOpDetails=("select * FROM OperatorDetails_tbl WHERE OperatorDetails_tbl.FMSID=('"&varSearchFMSID&"')")

Many thanks.

 

[gmmastros]

SQLOpDetails=("select * FROM OperatorDetails_tbl INNER JOIN Manager_tbl ON OperatorDetails_tbl.ManagerID = Manager_tbl.ManagerID WHERE OperatorDetails_tbl.FMSID=('"&varSearchFMSID&"')")

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MottoK]

Ah - so does this still allow me to bring back everything from the Managers table as well as the OperatorDetails table? (I want to be able to return everything from both tables by linking the two tables by manager ID).

(can't try this at the moment as I'm not at work)

Many thanks.

 

[gmmastros]

Well...

This is a standard inner join. Meaning, if you want records from both tables that match records on manager id, then yeah. This is what you'll want to use.

Of course, if you wanted all records from the managers table, even if there isn't a corresponding record in the OperatorDetails table, then you would want to change this from an inner join to a left join.

Anyway... give it a try. I suspect this is what you want. If, after getting back to work, you determine this isn't what you want, then we can help more.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MottoK]

Many thanks - really appreciated and plain English too!

 

[MottoK]

Hi,

Just tried this out and I still seem to get "Page cannot be displayed" when searching on various FMSID's.

Any ideas?

Thanks again.

 

[Tarwn]

This means that something in your code is breaking for certain values. Are you getting any additional error information on the page that is being displayed?

 

[MottoK]

I've just found out what it is but don't know how to get around it.

Obviously the above code is interrogating and bringing back data from 2 tables - the problem is that both tables (OperatorDetails and manager_ID table) have a fields called "ManagerID".

The error appears when I do my:

varMgrManagerID = rsOperatorDetails("ManagerID")


Question is then - how do I get "ManagerID" from both tables?

Many thanks

 

[MottoK]

Ok got it - for those who don't know:

varMgrManagerID = rsOperatorDetails("Manager_tbl.ManagerID")

...does the trick.


Thanks to everyone for their help.

 

[Tarwn]

Actually, the question is why do you need to get ManagerID from both tables since your INNER JOINing and they ought to be exactly the same? Instead of using the * in your select list the fields you want and gett eh ManagerID frmo one table or the other instead of both.

Try prefixing the fieldname with the table name you want it from (with a ".").