SQL Help Please????? - 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

SQL Help Please?????

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2001-11-27 : 11:38:19

I really need help writing an SQL Select statement for an ADO c++ application accessing an access database. I have 2 tables:
Table 1: Component_Hardware_Tbl
project_id
component_id
hardware_id
hardware_quantity

Table 2: Hardware_Info_Tbl
project_id
hardware_id
hardware_type
hardware_description
hardware_finish
hardware_detail

I need to get all the hardware_id and hardware_quantity entries with the same project_id and component_id from the Component_Hardware_Tbl
as well as the hardware_description from the Hardware_Info_Tbl.

I tried:
SELECT Component_Hardware_Tbl.hardware_id, hardware_quantity
FROM Component_Hardware_Tbl, Hardware_Info_Tbl
WHERE Component_Hardware_Tbl.project_id = 'csProjectId'
AND Hardware_Info_Tbl.project_id = 'csProjectId'
AND Component_Hardware_Tbl.component_id = 'csComponentId';
AND Hardware_Info_Tbl.component_id = 'csComponentId'
AND Component_Hardware_Tbl.hardware_id = Hardware_Info_Tbl.hardware_id";

Well that is is, any help from the DB Gurus??
Mike B

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-27 : 12:03:42

This should work a little better:

SELECT CH.hardware_id, hardware_quantity
FROM Component_Hardware_Tbl CH INNER JOIN Hardware_Info_Tbl HI
ON (CH.project_id=HI.project_id AND CH.component_id=HI.component_id AND CH.hardware_id=HI.hardware_id)
WHERE CH.project_id = 'csProjectId' AND CH.component_id = 'csComponentId'

The JOIN syntax does just that: JOIN two or more tables together. IMHO, while the WHERE clause syntax usually works (but not always), it is much better to use the JOIN syntax because it separates the two processes. Combining everything in the WHERE clause may not optimize the JOIN conditions, and could seriously affect the query performance.

The Access help file will detail the inner workings of the JOIN syntax, including LEFT and RIGHT joins. You could also design this in Access, join the tables in the query designer and set your conditions, then switch to SQL view. Copy and paste the generated SQL query and you'll be fine.

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2001-11-27 : 12:40:36

Never thought of using access to design it. GREAT ADVICE!!!!
Thank you for your time. I am not sure if it is working yet but it compiled! ;)

Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources