which JOIN type to use in sql query?

Hi,

I have 2 tables : i need to show data only from the 1st table & not from the second? The 1st table has 1 -many relation with the second. what type of join should be used? inner join ., let? outer ? which?

Thanks
triohelpAsked:
Who is Participating?
 
PePiCommented:
if you need to show data only from the 1st table, why join the 2nd table? i suggest an INNER JOIN
0
 
dbeneitCommented:
I asume that you need the table2 for "where" clause or "on" conditions
Inner... you want those records from table1 that exist a relationated record on table 2
left ...  you want all records from table1 and if exist a record relationated record on table 2 also. Caution not use table2 in where clause (except is null and similars) the conditions of table2 should be in ON conditions.
left outer join... idem left join


0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi triohelp!

I agree to both of them.

For your reference on how to use JOINS, visit this site : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_3mk3.asp

Hope this makes sense.


Goodluck!
eNTRANCE2002 :-)
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Renante EnteraSenior PHP DeveloperCommented:
By the way, if you need a direct help from us regarding the query.  It would be better if you post your table structures as well as sample data from it and the expected result that you want to accomplish.

Then, we can rest assure that we can help you.


Regards!
eNTRANCE2002 :-)
0
 
triohelpAuthor Commented:
Hi,

I am using the following query:

SELECT     ITEMS.*, INVENTOR.MFG_SER AS Mfg_ser, INVENTOR.SERIAL_NO AS SERIAL_NO
FROM         ITEMS INNER JOIN
                      INVENTOR ON ITEMS.ITEMNO = INVENTOR.ITEM_NO
WHERE     (ITEMS.AUTOSERIAL = 'T') AND (INVENTOR.MFG_SER IS NULL)

Here, the ITEMNO is the primary key in ITEMS table. When I run this query, i get the following output:

active   Autoserial      ITEMNO          Devices                Date            List_doc          Mfg_Ser              SERIAL_NO
F             T                 1011           Treo650            12/12/2004           303               null                         121
F             T                 1011           Treo650            12/12/2004           303               null                         122
F             T                 1011           Treo650            12/12/2004           303                null                        123
F             T                 1011            Charger           01/01/2005            767               null                         154
F             T                 1011            Charger           01/01/2005            767               null                         155

Now in this result set, the first 3 rows are the same but they have a different SERIAL_NO ( which is taken from the INVENTOR table).Similarly, the 4th & 5th rows are the same devices. I want the devices to be listed only once even though have different serial numbers in the INVENTOR table. Because I want to see which all devices have no Mfg_Ser but have a serial number. i.e I want the Treo650 to appear only once.

Now there is a List_Doc field. Probably this condition can be used for choosing Distinct values. How do we use this so that devices appear only once?

Thanks

0
 
PePiCommented:
We need to see the structure of the 2 tables (ITEMS & INVENTORS)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.