|
I don't know syntax of OPENQUERY command, but I am sure that you are missing a database name here:
select myserver.DATABASE.dbo.fnGetOrderItemPriceDummy
|
|
|
|
|
I have these tables
Customer Table:
CustomerID int; PK
FirstName nvarchar;
LastNaem nvarchar;
etc.;
Products Table:
ProductID int; PK
Name varcahr;
etc.;
OrderItems Table:
OrderItemID int; PK
ProductID int; FK
CustomerID int; FK
etc.;
Now I would like to get all users that have ordered: Product1 AND Product2, etc.
Only those people that orders all the selected products
-- modified at 21:15 Monday 10th September, 2007
|
|
|
|
|
Pass @Products --> with comma separated values like 'product1,product2'
and no of items @ProductCount --> 2
Select * from Customers Where CustomerID in
(Select CustomerID From
(Select CustomerID from OrderItems Inner Join Products on Products .ProductID = OrderItems.productid
Where [Name] IN (@products)
Group by CustomerID,[Name]) as Temp
Group by CustomerID having Count(CustomerID) = @productCount)
Best Regards,
Chetan Patel
|
|
|
|
|
This can't possibly work, can it?
I mean, the WHERE [Name] IN (@Products) part specifically.
|
|
|
|
|
If this has to work then the query should get execute using "EXEC sp_executesql " command
Regards J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
You could create the inner query like this:
SELECT oi.CustomerID
FROM OrderItems oi
JOIN Products p
WHERE p.ProductID IN ('Product1', 'Product2')
GROUP BY oi.CustomerID
HAVING COUNT(*) = 2
You'll have to dynamically create the WHERE p.ProductID IN (...) part, and the value for the HAVING COUNT(*) = 2 should use a parameter.
Joined with the customer table, the results could look something like this:
DECLARE @NumberOfProducts int
SET @NumberOfProducts = 2
SELECT
c.*
FROM Customer c
JOIN
(
SELECT oi.CustomerID
FROM OrderItems oi
JOIN Products p
WHERE p.ProductID IN ('Product1', 'Product2')
GROUP BY oi.CustomerID
HAVING COUNT(*) = @NumberOfProducts
) AS t
ON t.CustomerID = c.CustomerID
|
|
|
|
|
|
Notify when a special partition in analysis service must update?
how i can triger a program to update a special partition of a cube at a required time?
or
how to found a way to know if a special partition need to be update?
thanks
|
|
|
|
|
when you want to connect to the db in the server do i have to use sqloledb provider , can't i use sqlclient provider.
|
|
|
|
|
http://wwww.connectionstrings.com
I Love SQL
|
|
|
|
|
you want to add port on firewall port number = 1433 to you can use sqlclient
Rami Abdalhalim
|
|
|
|
|
sqlclient uses a an ole connection and you can use to connect it to SQL Server, this is the prefered method and is used in most examples.
|
|
|
|
|
dear friends,
I develop an Win application by using MS Access.I have got a problem in SQL statement.I have got a form that has
got 2 ComboBox and a DataGridView.The name of the first is cmbCategories and the second one is cmbContent.Content
is listing the content of categories.I use an array to transfer letters when I choose the category in
cmbCategories as it is Alphabetical Array.When I choose a letter from cmbContent like A,B or another one,but no
records is listing in the DataGridView.I try to use "Like" statement for this action.It is like this:
string strSQL = "select FilmName from films where FilmName like" + "'" + cmbContent.Text + "*'";
cmbContent has got the Alphabetical letters by sequence.I want to list the FilmName that is beginning as
alphabetic.For example,when I choose letter A,the films begins A must be listed.Does anybody help me?Thanks for
your help.
|
|
|
|
|
string strSQL = "select FilmName from films where FilmName like '" + cmbContent.Text + "*'";
I Love SQL
|
|
|
|
|
You should use parameterized SQL commands to prevent SQL injections...
|
|
|
|
|
I tried,but it doesnt work.I wrote the whole code below.maybe it helps what I want to do.
private DataView Get_DataAlphabetic()
{
string constr = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Environment.CurrentDirectory + "\\mdb\\film_db.mdb";
OleDbConnection con_ole = new OleDbConnection(constr);
string strSQL = "select FilmName from films where FilmName like " + "'" + cmbContent.Text + "*'";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, con_ole);
DataTable dt = new DataTable();
da.Fill(dt);
DataView dv = new DataView(dt);
dv.Sort = "FilmName ASC";
da.Dispose();
con_ole.Close();
return dv;
}
I send it to a datagrid's datasource property in another method as I use cmobobox's SelectedIndexChanged method.Hope it is clear.I wait for your replies
-- modified at 5:58 Monday 10th September, 2007
a
|
|
|
|
|
Why you aren't trying to use Filter instead of getting directrly from database.
Download all data at first time from database then use filter property.
I Love SQL
|
|
|
|
|
thanks buddy maybe sometimes you must look at it in a true point.I have done it.
Thnx.
a
|
|
|
|
|
You are welcome...
I Love SQL
|
|
|
|
|
zmskerem wrote: "*'";
Try a percent sign (%).
|
|
|
|
|
Database is Access, not SQL and condition % always return null value.
Instead of % is used *
I Love SQL
|
|
|
|
|
hi friend
you are right there is mistake in sql please correct it
right
string strSQL = "select FilmName from films where FilmName like '" & cmbContent.Text & "%'" ;
wrong this is your
"select FilmName from films where FilmName like" + "'" + cmbContent.Text + "*'";
|
|
|
|
|
rohitsrivastava wrote: right
string strSQL = "select FilmName from films where FilmName like '" & cmbContent.Text & "%'" ;
false
_________________
rohitsrivastava wrote: wrong this is your
"select FilmName from films where FilmName like" + "'" + cmbContent.Text + "*'";
true
I Love SQL
|
|
|
|
|
|
Hi there .
I have a problem with SqlServer 2005 Log file .
I have an application and i want to attach my database to SqlServer , I did it by SqlConnection and ConnectionString ,in many times it gives me an error.
I know that when we use AttachDbFilename property in Connection for attach DB we don't need database log file, But most of times it gives me this error:
"Unable to open physical file 'filpath_log.LDF'. Operation system error 5: "5(error not found)".<br />
Could not open new database 'DbName'. CREATE DATABASE is aborted.<br />
Cannot attach the file 'FilPath.mdf' as database 'DbName'.<br />
File activation failure. The physical file name 'filpath_log.LDF' may be incorrect.<br />
The log cannot be rebuilt when the primary file is read-only.'
I wonder to say sometimes attachment works but sometimes not.
I already did this attachment process with SQLServer 2000 and it never gives me error .
DMASTER
|
|
|
|