| Username |
Post |
| sukuti |
Posted
on 11-Jun-04 09:18 AM
I am trying to get an information from one table that is not in another table. Does anyone know the query for that?
So I need to use, minus or intersect? If yes, what is the syntax?
|
| AT |
Posted
on 11-Jun-04 09:36 AM
SELECT * FROM TableOne WHERE FieldOne NOT IN (SELECT FieldOne FROM TableTwo)
|
| sukuti |
Posted
on 11-Jun-04 10:31 AM
For some reason, this query doesn't seem to execute.
Thanks anyway.
I figured it out using "intersect"
|
| MillionDollars |
Posted
on 11-Jun-04 10:59 AM
Neither minus nor intersect will work for your purpose. AT's suggestion is the ideal solution. But it really depends on whether you have accurately stated your problem here. You may want to post the actual problem and the query you are using. I would not mind reviewing it for you.
|
| sukuti |
Posted
on 11-Jun-04 11:17 AM
This is the query:
select store_number from symbol where store_number not in (select store_number from store_assign where scheduled_on <= '20004-06-16')
for some reason this doesn't execute.
I thought it worked with "intersect" but I was wrong. I did it with PHP script and got a totally different answer. I justed wanted to do it in PostgreSQL.
|
| MillionDollars |
Posted
on 11-Jun-04 11:18 AM
On a second though, minus should work also.. but the downside is you will have to select exactly the same columns in both select's...
|
| MillionDollars |
Posted
on 11-Jun-04 11:22 AM
select store_number
from symbol
where store_number not in
(select store_number
from store_assign
where scheduled_on <= '20004-06-16')
Was that supposed to be 2004 instead of 20004 ? Can you share the error message you received ?
|
| sukuti |
Posted
on 11-Jun-04 11:25 AM
yup,
but even correcting that doesn't work.
|
| MillionDollars |
Posted
on 11-Jun-04 11:26 AM
what does the error message say ?
|
| sukuti |
Posted
on 11-Jun-04 11:30 AM
no error message....
looks like it is doing something, but doesn't return anything.
|
| MillionDollars |
Posted
on 11-Jun-04 11:35 AM
select store_number
from symbol
where store_number not in
(select store_number
from store_assign
where scheduled_on <= '20004-06-16')
That probably means it did not get anything. In other words, all the store number in SYMBOL are actually in STORE ASSIGN... but you may wanna try the following if scheduled_on is a date attribute just be sure.
select store_number
from symbol
where store_number not in
(select store_number
from store_assign
where scheduled_on <= to_date('2004-06-16', 'yyyy-mm-dd')
|
| MillionDollars |
Posted
on 11-Jun-04 11:36 AM
select store_number
from symbol
where store_number not in
(select store_number
from store_assign
where scheduled_on <= to_date('2004-06-16', 'yyyy-mm-dd')
)
Missed a paranthesis earlier...
|
| nsshrestha |
Posted
on 11-Jun-04 11:49 AM
Altered the date format ?
alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
Here is the working SQL Query.
select meter_number from fc_meter where meter_number not in (select meter_number from fc_meter_periodic_values where effective_date <= '2004/6/16 00:00:00');
:)
|
| sukuti |
Posted
on 22-Jun-04 02:01 PM
How do you get the duplicates from a table?
I have a table with many rows, and I am wanting to see if there are any duplicate rows. I want to check the rows by store_number, and the table name is stores.
|
| NSShrestha |
Posted
on 22-Jun-04 03:48 PM
SELECT Count(*) AS StoreNumberCount, StoreNumber
FROM Stores
GROUP BY StoreNumber;
Works?
|
| sukuti |
Posted
on 23-Jun-04 10:11 AM
thanks..
|
