EQL Format Question

[Freefall27]

This is probably a fundamental question but I have not seen SQL written in this format before.

I recieved these SQL instructions to run a report in the future from a consultant. Can someone help me determine what it means? I am not sure of the signifigance of the s. e. c. ??

not all the referenced fields are contained in table cardview. There must be something I am missing.


Report Instructions:
1. Select s.base_num district, site_name, Equip_name, e.vendor, c.type card_type, slot, description, c.card_inst_id
from cardview c, equip_inst e, site_inst s
where e.equip_inst_id = c.equip_inst_id
and s.site_inst_id = c.site_inst_id

2. select * from card_attr_settings

Create a table ( table1) with query 1 and then add two new fields Revision and Issue.
Join table1 with from query 2 on card_inst_id.
From Query 2 – Val_attr_inst_id = 1434 gives the Revision and Issue 1435 gives the Issue

 

[Freefall27]

THis should be SQL in the title.....hit the button to quick.

 

[lespaul]

[tt]Select s.base_num district, site_name, Equip_name, e.vendor, c.type card_type, slot, description, c.card_inst_id
from cardview c, equip_inst e, site_inst s
where e.equip_inst_id = c.equip_inst_id
and s.site_inst_id = c.site_inst_id[/tt]

The bold section above shows that you are actually selecting from three tables:
Cardview
Equip_inst
site_inst

by using the From tablename, tablename2, tablename3 - you are creating a cartesian join. See the Understanding SQL Joins link below for more information on joins and specifically the cartesian join.

The c, e and s after each of the table names in the FROM clause are aliases for the three tables. In the select clause you can see which tables the fields are coming from based on the c, e or s in front of the field names. For instance, Vendor is coming from equip_inst and base_num is coming from site_inst. If there is no qualifier, then the field only appears in one table. Since base_num is qualified by table s, that same field also appears in the other tables and if you don't tell the query which one to get you will get an 'ambiguous name' error.

Not sure about the 'Create a table and add new fields' portion, do you have the data that will be added to those fields?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Freefall27]

Lespaul....Thank You for the help. It all makes sense now. I'll determine the process of adding the additional fields. They should be contained in one of those tables. Perhaps the original query must be done to group the records and then add the other two fields on at the end. The database right now has many blank fields and the joins are not always working. Many right joins at this point based on one table that is fully populated. Yikes....

Thanks again!