5 Replies - 2222 Views - Last Post: 16 May 2006 - 11:08 AM Rate Topic: -----

#1 nlndeveloper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 26-April 06

Select From 2 Tables

Posted 26 April 2006 - 01:06 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Select From 2 Tables

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Select From 2 Tables

Posted 26 April 2006 - 07:24 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 msterhan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 08-May 06

Re: Select From 2 Tables

Posted 15 May 2006 - 02:26 PM

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
Was This Post Helpful? 0
  • +
  • -

#4 sigmazero13  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 10-May 06

Re: Select From 2 Tables

Posted 15 May 2006 - 03:17 PM

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

Was This Post Helpful? 0
  • +
  • -

#5 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 180
  • View blog
  • Posts: 4,667
  • Joined: 26-November 04

Re: Select From 2 Tables

Posted 16 May 2006 - 06:27 AM

Well said sigmazero13.
Table aliases can be anything other than SQL keywords.
Was This Post Helpful? 0
  • +
  • -

#6 msterhan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 08-May 06

Re: Select From 2 Tables

Posted 16 May 2006 - 11:08 AM

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 :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1