odd Joining problem

[simanek]

Hi all,

Does anyone have any idea why the following statment succeed (BMMT is the table name):


SELECT * FROM BMMT WHERE make='optic' and Classification='super_special';

and this one won't even though inherently, there is no difference in the WHERE statment:

SELECT * FROM BMMT INNER JOIN hTypenumL on BMMT.typenum=hTypenumL.typenum WHERE BMMT.make='optic' and BMMT.Classification='super_special';

Any help is very much appreciated. Thanks.

Mike
~~~~
simanek@uiuc.edu
"It's a Swingline!"
~~~~

 

[Jerrycurl]

I assume hTypenumL is another table?
Are you getting an error or is it just not returning the rows it should? If it's an error, what is it?

 

[wushutwist]

Problem:
You cannot use * the SELECT portion of table join query. You must use tablename.*. Since there are multiple column with the same name the RDBMS doesn't know which one you want. Try this:

SELECT BMMT.*,hTypenumL.* FROM BMMT INNER JOIN hTypenumL on BMMT.typenum=hTypenumL.typenum WHERE BMMT.make='optic' and BMMT.Classification='super_special';

Note:
You may need to use an OUTER JOIN. If hTypenumL has no values for the records you are requesting than you may not get the results you want.

Wushutwist

 

[Jerrycurl]

That depends on the database. I use SQL Server and it has no problem with SELECT * from joined tables with the same column names (not that that's a good way to code).