How SELECT In WHERE Clause Works?
Jun 8, 2008
Please help
I'm trying to do a select command but doesn't return any record
I have two tables one is "lists" another one is "list_records"
in the liss table I have 4 records and in the list_records I have only one record which is tell who is already visited the site so I created a query to get people who is NOT visited the site
Here is my query (I got 0 record return)
SELECT *FROM lists
list_reccords
WHERE NOT EXISTS (SELECT *
FROM lists,
list_records WHERE list_records.is_visited = 1
AND lists.list_id = list_records.list_id)
View 4 Replies
ADVERTISEMENT
Jul 20, 2005
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies
View Related
Aug 27, 2005
Hi
The query below with table join works on my dev server sql server MC
v2, but returns only NULL values for Questiona and Module on the live
server MC v1.2, is this error to do with the version or something else
like relationships?
SELECT
Induction_Module.Induction_Module, Induction_Questions.Question,
Induction_AnswerIncorrectStats.AnswerIncorrectNo,
Induction_Questions.AnswerCorrect,
CAST(Induction_AnswerIncorrectStats.DateAnswered AS VarChar(11)) AS
DateAnswered
FROM Induction_Questions LEFT OUTER JOIN
Induction_Module ON Induction_Questions.InductionModuleID =
Induction_Module.Induction_ModuleID RIGHT OUTER JOIN
Induction_AnswerIncorrectStats ON Induction_Questions.QuestionID =
Induction_AnswerIncorrectStats.QuestionID
Regards
Steve
View 4 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
Nov 29, 2007
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View 1 Replies
View Related
Jul 24, 2006
I have a pretty complex SQL statement that looks like this:
SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact, SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username) AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM aspnet_Employers LEFT OUTER JOIN aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active
It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null".
Why does it do this?
What would i change to prevent this from happening?
Thanks!
View 3 Replies
View Related
Apr 21, 2007
hey friends!
pleaze help, i am using sql server 2000.i have tried to fix my problem for more than five weeks just to solve for one problem and just still now it is unsolved, opps my due date is almost approaching, i don't know what to do more than what i did, i have search through the net, but still i did not get the correct answer to my problem, friends please just forward your answer to me, it may be best answer to my question .
create table mytable( english varchar(120), tigrigna Nvarchar(120))
insert into mytable values('peace',N'sälam')
insert into mytable values('kiss',N'samä ')
insert into mytable values('to kiss each other',N'täsasamä ')
then
select * from mytable where english='peace'; this works fine
but
select * from mytable where tigrigna=N'sälam'; this doesnot work;
select * from mytable where tigrigna='sälam'; this also doesnot work,
so what should i do to select this unicode select statement?
tigrigna is one of the spoken language in east africa(ethiopia).
Hopefully, i have joined this forum today, and just looking for your reply
I am Looking for your reply !
View 1 Replies
View Related
Jul 23, 2005
HiWe are migrating from Access to SQL server. The code is in VB and usesDAO3.6. I have successfully made the connection to the databases onthe SQL server and done operations with recordsets. I have also used aSELECT INTO command to create new tables. However CREATE TABLE andALTER TABLE commands do not work.Any ideas?The DBA says we have owner privileges on the databases and thereforeshould be able to do what we like.Many thanksSi
View 1 Replies
View Related
Jul 23, 2005
This statement failsupdate ded_temp aset a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)With this error:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'a'.But this statement:select * from ded_temp awhere a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)Runs without error:Why? and How should I change the first statement to run my update. Thisstatement of course works fine in Oracle. :)tksken.
View 17 Replies
View Related
Jan 2, 2008
I hope this is a easy one. We are trying to find a fix for a select statement that works in 2000 but not in 2005 with a simple select statement.
The easiest statement that will duplicate the error is:
TestTable has 3 columns: Primary, strTest, strTest2
SELECT strTest, strTest AS Name
FROM TestTable
ORDER BY strTest2
If you sort by the Primary column you will not receive an error.
How can you select the same column twice and then sort in the SQL statement?
View 3 Replies
View Related
Jun 4, 2007
Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430'
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha
View 2 Replies
View Related
Nov 9, 2006
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
View 1 Replies
View Related
Mar 14, 2008
Hi,
I'm attempting to extract some yearly average figures from our DB. I've written a SELECT statement in SQL Server MS which returns exactly what I need:
SELECT YEAR, CAllSource, AVG(CallTotal) AS [Average calls per day]
FROM (SELECT COUNT(CallID) AS CallTotal, DATEPART(YEAR, Recvddate) AS Year, CASE WHEN CallSource IN ('Auto Ticket', 'Email')
THEN 'Email' WHEN CallSource IN ('Phone') THEN 'Phone' ELSE 'Other' END AS CallSource
FROM Calllog where
DATEPART(MONTH, Recvddate) = 3
AND DATEPART(dw, RecvdDate) NOT IN ('7', '1')
GROUP BY RecvdDATE, callsource) as sub
GROUP BY YEAR, CallSource
ORDER BY YEAR, CallSource
The problem is that when I attempt to use this in SSRS I get the following error:
"sub.Year is not a recognised DATEPART Option".
Now as you can see, "sub.Year" is not even mentioned in the expression. However I noticed that when running the query, SSRS automatically adds "sub." before the YEAR in the section highlighted in yellow above. a) Why is it doing this, and b) does anyone know of a workaround/fix?
Thanks
Matt
View 4 Replies
View Related
Mar 31, 2015
We have a rather large environment and have just a couple of boxes out there that we are getting cannot begin distributed transaction on inserts and updates but works fine on selects. Inserts and updates work fine outside the begin tran / commit so it's definitely DTC
We have checked the configuration on and the source box is set to No authentication required same for destination.
We have: Verified credentials running the service, changed them, same problem. Uninstalled and re-installed MSDTC per Microsoft instructions.
Have run all the tools for checking DTC DTCPing etc and followed those procedures which typically in the past has resolved any DTC issues. Other than swapping out the offending pc for a new one we are at a loss.
View 2 Replies
View Related
Aug 3, 2006
Hi SQL Experts,
i have a strange problem
i have a variable which stores some values(ID) with single quote (so that i can use directlt inside the IN Clause of SELECT
Declare @DMSIDs AS VARCHAR(1000) -- variable declare,
Select @DMSIDs = '''DMS00046847'',''DMS00048305''' -- for test putting 2 correct values with escape characters
Select * from issue where id in (@DMSIDs) -- valid statment, but does not return any data
Select * from issue where id in ('DMS00046847','DMS00048305') -- same above constant value this returns data, but putting the values in varaible then trying fails.
The reason is i have a master table called issue and have another table [delta] where a particular column will store all the ID's of the issue table comma separated with single quote and i wanted to use something like below in my actual application
Select * from issue where ID in (Select distinct delta_ID from Delta_branch where date = getdate())
but since the above example with variable is not returning any data i wonder if such is possible in any other ways.
thank you for reading and helping me.
View 3 Replies
View Related
Aug 3, 2007
I'm having trouble using the To clause in my select statement. The following errs out with
Incorrect syntax near the keyword 'to'.
use pubs
select
*
from
view_Rates to 'c: est.csv'
I also tried this and got same error:
use pubs
select
*
to 'c: est.csv'
from
view_Rates
View 6 Replies
View Related
Mar 26, 2004
Hi,
I have a question about Insert into .....
Select clause in a SP. I need to insert some rows into a temperary table in a specific order. For example,
insert into #TempTable
{
.......
........
}
Select * from products order by @SortBy @SortDirection.
//
First of all, the order by clause does not take
variables. I have tried to use
declare @query varchar (1000)
set @query = 'Select * from products order by " + @SortBy + ' ' + @SortDirection
exec (@query)
//
However, I get an error message because I should supply a Select statement.
How can I solve this problem??
Thanks for your help!
View 3 Replies
View Related
Jan 10, 2006
Here is the first part of a query for MySQL that I am trying to get working on MSSQL:
Code:
SELECT n.*,
round((n.rgt-n.lft-1)/2,0) AS childs,
count(*)+(n.lft>1) AS level,
((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
(( (n.lft-max(p.lft)>1) )) AS upper
FROM table n
...
But, I get this error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
Is there a way to convert this? Thanks
View 2 Replies
View Related
Jun 12, 2008
Hi Everyone,
I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:
IF @categoryID <> 12
AND (category.categoryID = @categoryID)
STORED PROCEDURE:
CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS
SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)
View 4 Replies
View Related
Aug 23, 2007
Hello,
When you rename a field/calculation in the select part of the sql statement, how do you reference it in the where clause?
For example:
Select A, B, (C + D) as X
From test
WHERE X > 1
Many thanks!
View 3 Replies
View Related
Feb 28, 2008
Hi,
I have a simple select statement that joins a master and a detail tables using a single field. Looks somthing like this:
Master:
Field1 (Unique key)
more fields...
DateField (Index field)
Detail:
Field1 (unique key)
Field2 (unique key)
more fields....
The master has 100 thousand records and the detail has 100 million records.
If I had a statment "Select.....From Master, Detail" what would be the best way to write the where clause?
Would one of the following where clauses run faster than the other based on the number of records in the tables?
Where1:
where Master.DateField = value and Master.Field1 = Detail.Field1
Where2:
where Master.Field1 = Detail.Field1 and Master.DateField = value
View 3 Replies
View Related
Oct 11, 2006
I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error:
Server: Msg 16924, Level 16, State 1, Line 78
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor?
select colnum, coldesc, colname into #ae_defs from ae_adefs
select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') +
', ' +
(select colname from #ae_defs where coldesc = 'PATIENT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT DATE') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT TYPE') +
', DocID from ae_dtl1'
DECLARE myCursor CURSOR FOR
Select @SQL
OPEN myCursor
print @@Cursor_rows
FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID
View 2 Replies
View Related
May 3, 2004
I have sp20, simplified, as:
ALTER PROCEDURE dbo.sp20 (@CustomerID int, @aDate as datetime) AS
SELECT Customers.* INTO #EndResult1 FROM Customers WHERE Customers.CustomerID >= @CustomerID
SELECT Orders.* INTO #EndResult2 FROM Orders Where Orders.[TakenDate] >= @aDate
SELECT #EndResult1.*, #EndResult2.*
FROM #EndResult1 INNER JOIN #EndResult2 ON #EndResult1.CustomerID = #EndResult2.CustomerID
This works fine in EM.
When I try to execute it from MS Access ADP Project I get
'Stored Procedure excuted succesfully, but did not return any records'
Although, in EM it returns the right number of records.
Thank you in advance - Rehman
View 2 Replies
View Related
Sep 11, 2000
Hi,
Is there any way of emulating Oracle's capability of passing output of user-defined functions in the select statement or better still in the Where clause in SQL server 7.0? If not then could we hope for it in SQl server 2000?
Regards,
Vikas..
View 1 Replies
View Related
Dec 28, 2004
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.
What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).
This is the code in my procedure..
DECLARE @tab_list CURSOR
set @tab_list = CURSOR FOR select * from table_list
OPEN @tab_list
DECLARE @tab_name varchar(256)
DECLARE @rec_cnt int
FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt
select count(*) from @tab_name
This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.
Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.
Please help me to construct the select statement that is similiar to
x=<table name>
select * from x
where x is a variable and the table name gets substituted.
what is the syntax for it ?
View 7 Replies
View Related
Aug 10, 2004
Hello
What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.
campaign Col1 Col2
<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>
View 1 Replies
View Related
Dec 14, 2004
Hi all
I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address.
Simple change to the stored procedure (this is just an example not the real stored proc):
From:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’
To:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’
Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock.
Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on:
strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode.
So I created an index just for the strPostCode (non clustered).
This had no change to the ‘Like select’ what so ever. So I am now stuck.
1)Is there another way to search for part of a text field in SQL.
2)Does ‘Like’ comparison use the index in any way? If so how do I set this index up?
3)Can I stop a ‘Shared Lock’ being created when I do a ‘like select’?
4)Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me).
Any advice truly appreciated.
View 8 Replies
View Related
Nov 7, 2008
I have a table which stores datewise Transactions of different items. Fields and sample data is
RecID, ItemID, Date, Received, Issued, Stock
1, 5, 11-03-08, 10, 10
2, 5, 11-05-08, 3, 7*
3, 8, 11-15-08, 25, 25
4, 8, 11-16-08, 8, 33
5, 8, 11-18-08, 6, 27*
Now i want to select last row for each item (indicated by *). Is it possible in one single statement.
View 6 Replies
View Related
Oct 22, 2013
I need to check the value of a column being pulled from an embedded select in the WHERE clause of a script but can't figure out how to do it. The script looks like this:
SELECT
LIST_ID,
NAME,
ADDRESS
(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWER
FROM VIEW_LIST
WHERE ANSWER = 'No'
The syntax above works in Oracle but in SQL Server I receive the following error: "Invalid column name 'OHIP'."
View 6 Replies
View Related
Jul 14, 2014
Just wondering if it's possible to select distinct results from the where clause?
View 3 Replies
View Related
Nov 3, 2005
Hi there,
It's a very strange thing!
I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.
I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do:
select * from invoices where invoiceID = 302247 (and i'll get one result)
select * from customer_payments where invoice = 302247 (and i'll get none results)
however, if i do the following:
select * from invoices where invoice_id not in
(select invoice_id from customer_payments)
I get nothing!!!???
It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?
thank you so much for any help!
Grazi
View 6 Replies
View Related
Jul 23, 2005
I'm having this query:SELECTss.subscription_id AS SubscriptionId,s.id AS ScopeId,s.[name] AS ScopeName,s.base AS ScopeBase,dbo.iqGetShapesByScopeAsString(s.id) AS ShapesAsStringFROMsubscription_scope ss,scope sWHEREss.subscription_id = @subscription_idANDss.scope_id = s.idORDER BYs.[name]The select only returns a single row but my database (SQL Server 2005CTP) seems to execute the "iqGetShapesByScopeAsString" function foreach row in the subscription_scope and scope tables. This is a bug,right? The function should be executed only once for each *returned*row in the SELECT, right? I believe that was the case in SQL 2k thoughI can't check it at the moment.// pt
View 6 Replies
View Related
Jan 3, 2007
Hello All,
I have a question about a Select over 2 Tables,
with the Following Scenario (Not all Products (ARTICULOS) haves CARAC's on the CFG_CARAC_ARTICULOS table):
Picture of the tables here:
http://www.pci-baleares.com/pantallazoSql.jpg
We have per example 7 Slots (Motherboard, CPU, VGA Card, RAM, TOWER, etc...)
When we fill the Slot with a CPU-> Then we open the Slot for VGA CARD, we do the Followin Select:
SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO
Ok it brings up ALL Graphic Cards because they dont depends on CPU
Now we go to the Motherboard Slot
And we make the following Select to obtain the compatible Motherboards:
SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO
WHERE
((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E')) AND
((ID_CARAC = 1) AND (VALOR = '775'))
We check the motherboards if they support PCI-E (because we selected a Graphic card of that, and SOCKET 775 because the CPU)
But SQL return 0 Rows, if we do the following Select:
SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO
WHERE
((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E'))
OR
SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO
WHERE
((ID_CARAC = 1) AND (VALOR = '775'))
It return Rows, it happens just if the Where clause haves more as 1 specifications...
Any solution for it? It drives me crazy :D
Thanks and regards
Marc Hägele
View 3 Replies
View Related
