Re: SQL 101

On 3/20/07, Tyrone Baseck <tyrone_bak@xxxxxxxxxxx> wrote:
> I will get help from the DBA on that query listed earlier.  The one listed
> below works.  Now, what is it doing:
>
> select * from MGD_REQ_HOLDG m inner join AUTH_REQ_ITEM_HDR a on
> m.REQ_ITEM_HDR_ID = a.REQ_ITEM_HDR_ID
> where a.auth_req_id
>
> Do I need to change the MGD_REQ_HOLDG in the first line to look at another
> table?

You are saying join tables MGD_REQ_HOLDG and AUTH_REQ_ITEM_HDR by
matching records where the field REQ_ITEM_HDR_ID in both tables is
equal.  If you need columns from other tables, then you would add them
to your join.  The where clause :

where a.auth_req_id

is incomplete.  You should give a value for auth_req_id like :

where a.auth_req_id = 42

or not include a where clause at all (which could be quite a large
number, depending on the data)

>
> What is the lower case "m" and "a" for?

Table aliases are more convenient than using the entire table name.

>
> Why do you need to add a "a" to a.REQ_ITEM_HDR_ID instead of doing it
> without the "a"?

Both tables apparently have a column called REQ_ITEM_HDR_ID, so the
database doesn't know which one you are referring to.  The table
aliases "m" and "a" tell it which one you mean.


Rob

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV