Home » SQL & PL/SQL » SQL & PL/SQL » Help with Exists or In Statement (Merged again)
Help with Exists or In Statement (Merged again) [message #391157] Wed, 11 March 2009 04:40 Go to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Somebody pls help me craft a SQL statement..

Table: MST_ITEM
Cols:
> item_code
> sub_group_code

Table: TEMP_LIGHTING
Cols:
> vc_item_code

I want to find out the records in MST_ITEM that don't have sub group code i.e sub_group_code is NULL and with matching item_code in TEMP_LIGHTING

I wrote this SQL but not getting desired result. I'm pretty sure that there are ITEMS in MST_ITEM without Sub Group Code with matching Item code in TEMP_LIGHING

SELECT *
FROM mst_item
WHERE EXISTS (SELECT *
FROM temp_lighting
WHERE temp_lighting.item_code = mst_item.item_code)
AND NVL(sub_group_code,0) = 0


Am i missing something..

Mave
Re: Help with EXISTS or IN statement [message #391160 is a reply to message #391157] Wed, 11 March 2009 05:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hi,

some questions:
1. does sub_group_code 0 exist? You might want to opt for
and sub_group_code is null

2. What's wrong with a regular join?
3. Is it TEMP_LIGHTING.VC_ITEM_CODE or TEMP_LIGHTING.ITEM_CODE? You might as well have a typo in your select.

MHE
Re: Help with EXISTS or IN statement [message #391170 is a reply to message #391157] Wed, 11 March 2009 05:45 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

Am i missing something..



Yes you have missed posting the DDL and DML Statements and ofcourse Formatting..

Regards,
Ashoka BL
Re: Help with EXISTS or IN statement [message #391171 is a reply to message #391157] Wed, 11 March 2009 05:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Didn't my previous answer help you in any way? You can answer threads. You don't have to create a thread on the same subject again.

MHE
Re: Help with Exists or In Statement (Merged again) [message #391198 is a reply to message #391157] Wed, 11 March 2009 07:40 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,
your query is like this

SELECT *
FROM mst_item
WHERE EXISTS (SELECT *
FROM temp_lighting
WHERE temp_lighting.item_code = mst_item.item_code)
AND NVL(sub_group_code,0) = 0

can you rewrite it as

SELECT *
FROM mst_item
WHERE EXISTS (SELECT *
FROM temp_lighting
WHERE temp_lighting.vc_item_code = mst_item.item_code)
AND NVL(sub_group_code,0) = 0

i have inserted some value and tested out it is workig fine


Re: Help with Exists or In Statement (Merged again) [message #391210 is a reply to message #391157] Wed, 11 March 2009 08:02 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Thanks Folks.

Sincere apologies for duplication...

It's not my fault b'coz the website was'nt giving me any indication whether the thread was getting created.
It just froze on me....

Mave
Re: Help with Exists or In Statement (Merged again) [message #391225 is a reply to message #391210] Wed, 11 March 2009 08:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That makes sense, the forum has its flaws. No problem.

That said, for your question: does my previous answer help?

MHE
Re: Help with Exists or In Statement (Merged again) [message #391342 is a reply to message #391157] Wed, 11 March 2009 20:14 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey,

Just for curiosity....can it be written like this ?

SELECT item_code
  FROM mst_item m, temp_lighting l
 WHERE sub_gr_code IS NULL AND m.item_code = l.vc_item_code


Regards,
Ashoka BL
Bengaluru
Re: Help with Exists or In Statement (Merged again) [message #391344 is a reply to message #391157] Wed, 11 March 2009 20:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Just for curiosity....can it be written like this ?
Of course it can, but why are there 2 tables in FROM clause when only a single data element being returned.
No data is coming FROM temp_lighting table; so temp_lighting should not be part of FROM clause.

[Updated on: Wed, 11 March 2009 20:30]

Report message to a moderator

Re: Help with Exists or In Statement (Merged again) [message #391357 is a reply to message #391157] Wed, 11 March 2009 22:33 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@BlackSwan,

Thanks for the reply.. I have modified as following,

could you please let me know what would be the best approach ( or do we have any other approach)

SELECT item_code
  FROM mst_item
 WHERE sub_gr_code IS NULL AND item_code IN (SELECT vc_item_code
                                               FROM temp_lighting)


SELECT item_code
  FROM mst_item m
 WHERE EXISTS (SELECT vc_item_code
                 FROM temp_lighting l
                WHERE l.vc_item_code = m.item_code) AND sub_gr_code IS NULL
Re: Help with Exists or In Statement (Merged again) [message #391358 is a reply to message #391157] Wed, 11 March 2009 22:40 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>could you please let me know what would be the best approach
The EXPLAIN PLAN for each need to be compared.

Results also may depend upon indexes or not & how skewed (or not) is the data.

SQL tuning must be done on a case by case basis.

The RULE for SQL Tuning is that no absolute rules exist!
For every "rule", I can find at least 1 exception.
Previous Topic: table or view does not exist error while using db links (merged)
Next Topic: sql query sequnce help
Goto Forum:
  


Current Time: Sun Dec 11 08:00:58 CST 2016

Total time taken to generate the page: 0.06197 seconds