?
Solved

which JOIN type to use in sql query?

Posted on 2005-05-06
9
Medium Priority
?
224 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:triohelp
6 Comments
 
LVL 6

Accepted Solution

by:
PePi earned 672 total points
ID: 13948481
if you need to show data only from the 1st table, why join the 2nd table? i suggest an INNER JOIN
0
 
LVL 9

Assisted Solution

by:dbeneit
dbeneit earned 664 total points
ID: 13948504
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
 
LVL 14

Assisted Solution

by:Renante Entera
Renante Entera earned 664 total points
ID: 13949320
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:Renante Entera
ID: 13949330
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
 

Author Comment

by:triohelp
ID: 13960593
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
 
LVL 6

Expert Comment

by:PePi
ID: 13970213
We need to see the structure of the 2 tables (ITEMS & INVENTORS)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question