[nlndeveloper]

TABLE1_COMPANY
FIELD1_MANAGER (holds value of FIELD1_ID from TABLE2)
FIELD2_OWNER
FIELD3_EMPLOYEE

TABLE2_CONTACT
FIELD1_ID
FIELD2_NAME
FIELD3_ADDRESS
FIELD4_PHONE

These are SQL tables. I need to create a report that shows the NAME of each MANAGER, OWNER, EMPLOYEE (not the ID #s stored in the FIELD1_MANAGER field).

Help!

will
nlnshark@yahoo.com

[Amadeus]

SELECT b.FIELD2_NAME,a.FIELD2_OWNER,a.FIELD3_EMPLOYEE FROM TABLE1_COMPANY a INNER JOIN TABLE2_CONTACT b WHERE a.FIELD1_MANAGER = b.FIELD1_ID

[msterhan]

please Amadeus could you explane that SQL statment !!

the a from you ? and also b .. is that ok ??

so.. if its like that.. if i make the tables in MSaccess is it important to make a relation between them ?

also what is the need for

INNER JOIN

is it make the relation between the table ??

thanx

[sigmazero13]

msterhan - Though I didn't post the query, I can probably explain it.

The "a" and "b" are aliases for TABLE1_COMPANY and TABLE2_CONTACT, respectively.

IE, having it say "... FROM TABLE1_COMPANY a..." is basically saying "pull from TABLE1_COMPANY, but I'm going to call it a for the rest of the query".

(This is useful if you are doing a join where both halves of the join are the same table; you'd need to distinguish between them).

He COULD have done the same join with more verbosity, such as:

SELECT TABLE2_CONTACT.FIELD2_NAME, TABLE1_COMPANY.FIELD2_OWNER, TABLE1_COMPANY.FIELD3_EMPLOYEE FROM TABLE1_COMPANY INNER JOIN TABLE2_CONTACT ON TABLE1_COMPANY.FIELD1_MANAGER = TABLE2_CONTACT.FIELD1_ID

The INNER JOIN is indeed what connects the table. Basically, an INNER JOIN is a join where it matches records in the first table with records in the second table where a certain condition is met. It looks like

...FROM table_1 INNER JOIN table_2 ON criteria...

criteria will be where you want them to "match", such as "table_1.fieldx = table_2.fieldy", which would match them where the fieldx in table_1 matches fieldy in table 2.

In the query he lists, it basically is saying, in english:

"Show me the name, owner, and employee from the Company table, joined with the Contact table where the Contact is the Company's manager."

This post has been edited by sigmazero13: 15 May 2006 - 03:18 PM

[born2c0de]

Well said sigmazero13.
Table aliases can be anything other than SQL keywords.

[msterhan]

born2c0de, on 16 May, 2006 - 05:19 AM, said:

Well said sigmazero13.
Table aliases can be anything other than SQL keywords.

perfect sigmazero13...
what can i say ?!!
thanx