triohelp
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
We need to see the structure of the 2 tables (ITEMS & INVENTORS)
Then, we can rest assure that we can help you.
Regards!
eNTRANCE2002 :-)