Database

Re: Need help for Customize the except query

3-Feb-12 1:30

I need one column from the table1 which need not be used comparassion but I need the colume (ID colum in this case)to get lised in the query result.

Ex :

SQL

<pre lang="SQL">select ID a,b,c from table1 except select a,b,c from

table2
in the above example ID color of Table1 need not be compared with table to but just required in the query result for each rows.

Thanks in advance

modified 3-Feb-12 7:37am.

jschell3-Feb-12 11:02

jschell

3-Feb-12 11:02

I think some of your post was truncated.

As a guess if you are suggesting that you want to return rows from two tables where there is no common columns between them then the answer is simple - use two queries.

Re: Need help for Customize the except query

Mycroft Holmes3-Feb-12 12:00

Mycroft Holmes

3-Feb-12 12:00

If you are looking for records in table 1 where A,B,C records are not in table 2 then the following should help

SQL

Select * from table1 T
where not exists(Select * from Table2 TT where T.A = TT.A and T.B = TT.B and T.C = TT.C)

Never underestimate the power of human stupidity
RAH

Re: Need help for Customize the except query

ptr_Electron3-Feb-12 22:53

Oracle - odd behavior with nested select/join

3-Feb-12 22:53

Gr8 sir .. it worked .. you made my weekend Smile | :)

... I wish you a wonderfull weekend. Smile | :)

Paladin20002-Feb-12 9:19

Paladin2000

2-Feb-12 9:19

Here is a strange result that I am getting with Oracle. Unless I wrap the select from TABLE1 as shown (specifying "rownum > 0"), the query never seems to complete. However, when it is included, it only takes a second or two.

It doesn't make any sense to me, because all that it seems to do is force Oracle to evaluate the nested select with the alias "a" before performing the join. I had thought that placing it in parenthesis would do that automatically. Perhaps the Oracle optimizer does something wonky that this compensates for..? Scratching my head here, because all that the "where" condition adds is basically saying "give me any rows that you find".

SQL

select COUNTRY_NAME, count(*) TALLY from
  (
    select * from (
    select IP_SOURCE_NUM from TABLE1 
    where log_date between '01-JAN-2012' and date '01-FEB-2012'
      and IP_SOURCE_NUM > 0
    ) where rownum > 0 
  ) a
  JOIN ip_geo b on b.ip_from = 
  (
    select max(ip_from) as ip_from_match from ip_geo
    where ip_from <= a.IP_SOURCE_NUM
  )
group by COUNTRY_NAME
order by count(*) desc

Mycroft Holmes2-Feb-12 13:16

Mycroft Holmes

2-Feb-12 13:16

While passing up the opportunity to make sarcasic remarks about a 'wonky' Oracle you might be better served by asking Tom[^] as it sounds like a rather deep and very specific Oracle issue.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson2-Feb-12 21:32

Jörgen Andersson

2-Feb-12 21:32

I think your link is broken.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Jörgen Andersson2-Feb-12 21:31

Jörgen Andersson

2-Feb-12 21:31

As you already realized yourself, the paranthesis doesn't define in which order the query is executed, only what data the result is built from.
But by specifying Rownum > 0 you're forcing the optimizer to change the order of execution.

But I would use an optimizer hint instead. Like Select /* +materialize */ * from...

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Paladin20003-Feb-12 4:14

Paladin2000

3-Feb-12 4:14

I suspected something like that was happening. I've never used optimizer hints before; I will look into that.

Thanks for the tip. Thumbs Up | :thumbsup:

Insert Statment

Seema Bawa2-Feb-12 9:05

Seema Bawa

2-Feb-12 9:05

What is the wrong in this query

insert into tbTasks_New( AssignedToId)
values('Employee')
where
Owner = 'Seema'

jschell2-Feb-12 9:13

jschell

2-Feb-12 9:13

1. It isn't a query
2. An insert is an insert - 'where' doesn't apply (not for the form given.)

It is also possible that 'Employee' is not a valid value for 'AssignedToId'

Chris Meech2-Feb-12 15:30

Chris Meech

2-Feb-12 15:30

By Owner do you mean the schema owner of the table? I don't think I've ever seen an insert statement with a 'where' clause before. Confused | :confused:

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Corporal Agarn3-Feb-12 1:24

Corporal Agarn

3-Feb-12 1:24

As jschell said.

If Owner is a column in a table an insert might be like:

INSERT INTO tbTasks_New(AssignedToId) 
SELECT column1
WHERE owner = 'Seema'

If you use the value command you cannot use the where (you do not need to!)

Find un identical rows from two Tbls based on combination of 3 columns

Dnyanesh Wahiley8-Feb-12 1:25

Dnyanesh Wahiley

8-Feb-12 1:25

your queary is like this...
insert into tbTasks_New( AssignedToId)
values('Employee')
where
Owner = 'Seema'

and your solution will be like this...!

Update tbTasks_New set='Employee' where Owner='Seema'
-----------------------------------
Actually u have to use update statment insted of insert statment...! and then u will get ur expected output.!

Dnyanesh Wahiley....

ptr_Electron2-Feb-12 2:38

Re: Find un identical rows from two Tbls based on combination of 3 columns

2-Feb-12 2:38

I need to Find un identical rows from two table based on combination of 3 columns. Both table has same structure. Using SQL Server 2005

Ie Table1 and Table2 to has the columns Type,ID,Parent
1. Query the rows from Table1 which are not matching in Table2 with respect to columns Type,ID,Parent (ie. Need not required the rows which are matching combination of Type,ID,Parent in both table)

2. Query the rows from Table2 which are not matching in Table1 with respect to columns Type,ID,Parent (ie. Need not required the rows which are matching combination of Type,ID,Parent in both table)

Please help!!

Jörgen Andersson2-Feb-12 4:03

Jörgen Andersson

2-Feb-12 4:03

Here's[^] some nice basic info about joins and how to do them. It even has pictures

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: Find un identical rows from two Tbls based on combination of 3 columns

Blue_Boy2-Feb-12 5:05

Blue_Boy

2-Feb-12 5:05

Did you try to write query before you ask here? In your previous question you took good answers which are valid for your question which you are asking.

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

finding full path of subgroup with t-sql?

maliheSadat1-Feb-12 21:03

maliheSadat

1-Feb-12 21:03

hi I have a table with this records(GroupId,ParentGroupId,GroupName).
i want to show a group with its full path, from main parent to it self

like this: red cherry: red cherry>>cherry>>fruit

i write a Procedure that give me its parents names,but i want to have a prosedure that append the parents name and give me one string

i want to do this for all records in that table

thanx for u answers

Re: finding full path of subgroup with t-sql?

Mycroft Holmes2-Feb-12 0:57

Mycroft Holmes

2-Feb-12 0:57

What you need is a common table expression[^] which is recursive processing of a table.

This may not be correct as your structure seems to be rigid (you know how many level to the top) and therefor you simply do a bunch of inner joins.

Select *
From Fruit
Inner join Category on Category.CategoryID = Fruit.CategoryID
Inner Join level2.ID = Category.Level2.ID
...

Never underestimate the power of human stupidity
RAH

Query the records that are not same in two different table.

ptr_Electron1-Feb-12 3:21

Re: Query the records that are not same in two different table.

1-Feb-12 3:21

Dear Experts,
I am using MS SQL server 2005. I want to Query the records that are not same in two different table.

ie., I have 2 different tables TABLE1, TABLE2 which are having same columns. (and most of the row are same)

Q1 : I want to query the records of TABLE2 which are not avaliable in TABLE1

Q2 : I want to query the records of TABLE1 which are not avaliable in TABLE2

Needless to compare the primay key.
Thanks in advance!!

Simon_Whale1-Feb-12 3:30

Simon_Whale

1-Feb-12 3:30

SQL

SELECT a.*
FROM table1 a left outer join table2 b
--a.id, b.id are the columns that you would use to join the tables together
ON a.id = b.id
WHERE b.id IS NULL

That will show you a list of records that are not in table2.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: Query the records that are not same in two different table.

ptr_Electron1-Feb-12 3:46