homepage Welcome to WebmasterWorld Guest from 54.204.128.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
guidance on selecting records from table that refer to other tables.
nelsonm




msg:4362472
 4:48 pm on Sep 14, 2011 (gmt 0)

hi all,

I'm retrieving records from a work-order table that contains foreign keys to other ancillary tables that provide redundant finite items like state name, customer name and status, payment type and so on.

I'm using one SELECT statement that selects not only work-order table fields but also the associated text description to the foreign keys fields in the work-order table. No one wants to see records with only id numbers for state, customer and payment type names.

I currently have only one test work-order record in the work-order table but I'm getting multiple (100's) results due to all permutations from the ancillary table look-ups. I just want to retrieve the one work-order record with all the foreign key fields replaced with their associated descriptive names contained in the ancillary tables.

Using a single "SELECT (fields from work-order and foreign tables) FROM (work-order and foreign tables) WHERE conditions" is not as planned. I'm guessing selecting records using "FROM (work-order and foreign tables)" is the problem and i need to use "INNER JOIN"?

I'm guessing that instead of:
"SELECT (fields from work-order and foreign tables) FROM (work-order and foreign tables) WHERE conditions".

I need to use:
"SELECT (fields from work-order and foreign tables) INNER JOIN (work-order and foreign tables) ON conditions".

I'm pretty sure using "INNER JOIN" is correct, i just wanted to get your opinion as to whether it's the only correct method.

thanks.

 

nelsonm




msg:4362520
 6:16 pm on Sep 14, 2011 (gmt 0)

Sorry... I mean...

I need to use:
"SELECT (fields from work-order and foreign tables) FROM work-order INNER JOIN (foreign tables) ON conditions".

arms




msg:4362837
 12:49 pm on Sep 15, 2011 (gmt 0)

just using "SELECT (fields from work-order and foreign tables) FROM (work-order and foreign tables)" gives you a cartesian product (like a cross jojun all by all) using "WHERE Table1.Column = Table2.Column" is the same as an inner join, but yes you do need to join these effectively for the query to work

nelsonm




msg:4363191
 4:42 am on Sep 16, 2011 (gmt 0)

thanks for responding...

When you say cartesian, do you mean all permutations between tables?

In any case, conceptually, if all you want to do is

nelsonm




msg:4363213
 5:51 am on Sep 16, 2011 (gmt 0)

thanks for responding and sorry - ran out of time to post so the previous message is incomplete.

When you say cartesian, do you mean all permutations between tables?

In any case, conceptually, if all you want to do is retrieve records from table1 with all the foreign keys replaced with associated descriptive names contained in other ancillary tables. What would be the proper method to do it?

For instance, if a workorder table contains the following records:

WORKORDER Table

cuid: int // foreign int key to customer table names.
stid: int // foreign int key to state table names.

When i retrieve the workorder records for display, i want the select result list fields to contain the descriptive names associated with the foreign keys like the following:

SELECT customer.name, state.name
FROM workorder, customer, state
WHERE (workorder.cuid = customer.cuid) and (workorder.stid = state.stid)


SELECT customer.name, state.name
FROM workorder
INNER JOIN customer, state
ON (workorder.cuid = customer.cuid) and (workorder.stid = state.stid)


although i think that the following is the better join syntax...

SELECT customer.name, state.name
FROM workorder
JOIN customer ON workorder.cuid = customer.cuid
JOIN state ON workorder.stid = state.stid


The above selects should give me the same results. One result for each record in the workorder table with the foreign keys replaced with the descriptive names contained in the ancillary name tables.

The following should give me the similar results. One result for each record in the left workorder table with the foreign keys replaced with the descriptive names contained in the ancillary name tables, if possible, even if there are no matches in the right tables.

SELECT customer.name, state.name
FROM workorder
LEFT JOIN customer ON workorder.cuid = customer.cuid
LEFT JOIN state ON workorder.stid = state.stid


Do i have the right method for what i want to do?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved