Page 2 - HELP With SQL Query - (complicated)

Oct 10, 2006

You can display both by referring to their position in the recordset or by aliasing them in the query and referring to the alias in your code.
Note that in order to put Products.Quantity or ProductDetails.Quantity in just one column you'll have to add some logic in the query, using a coalesce, like:

sql Code:






Original
- sql Code




SELECT
Products.ProductID, Products.ProductName, Products.ProductPict,
COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,
ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color
FROM
Products
LEFT OUTER JOIN
ProductDetails
ON Products.ProductID = ProductDetails.ProductID
WHERE
Products.Quantity <= "& +rsResults__var1 & "
OR
ProductDetails.Quantity <= "& +rsResults__var1 & "
Order by Products.ProductID asc;






SELECT Products.ProductID, Products.ProductName, Products.ProductPict, COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_ColorFROM Products LEFT OUTER JOIN ProductDetails ON Products.ProductID = ProductDetails.ProductID WHERE Products.Quantity <= "& +rsResults__var1 & " OR  ProductDetails.Quantity <= "& +rsResults__var1 & " ORDER BY Products.ProductID ASC;

Untested, beware of syntax errors!
In this case you'll have just one Quantity colum and you won't have to change anything in your asp code.

View 1 Replies


ADVERTISEMENT

Complicated SQL Query

May 23, 2007

Hello
I am trying to write and SQL query...

I have a table with 4 columns, ID, Key, Value and SiteID.

SiteID can be Null or an int.

As an example I could have these four rows,
ID=1, key=colour, value=red, siteID=Null
ID=2, key=colour, value=green, siteID=2
ID=3, key=font, value=arial, siteID=2
ID=4, key=size, value=6,siteID=Null

My query will pass in a siteID and I need to bring back all rows with that site ID plus any rows whos key I haven't already got who's siteID =Null.

The Null site ID is default, so it always needs to bring back the null if a key doesn't exist with a siteID.

Does anyone know if that is even possible with just a straight SQL query?

Thanks

Becky

View 3 Replies View Related

HELP With SQL Query - (complicated)

Oct 10, 2006

Hi I have been tearing my hair out trying to resolve this problem.
A form is submitted with a number in to checkthe database for the items with that number of items in the quantity field(s) and display them.

There are 2 relevant Tables in the 2005 server SQL Database, One called "Products" the other called "ProductDetails"

In both tables is a field called Quantity, this hold a numerical value for the quantity of the product.

Some products have additional attributes such as sizes or colours and these are stored in the ProductDetails table.

Every product is stored in the "Products" table and has a unique ProductID, where a product has additional attributes, these along with the ProductID and the quantity for that particular attribute are stored in the ProductDetails table.

I am trying to loop through the database and produce a list of all the items and if they have any additional size or colour attributes then display these too, and if they dont have additional attributes then just display the product details and quantity stored in the Products table.


What I have actually done instead is produce a list of every item and every attribute in the database, but for example Where product 1 doesnt have any attributes, and neither does product 2, but product 3 and product 3 do, when the details are displayed on the page (Using ASP),

Rather than displaying product 1 details and the quantity in the "Products" table, product 2 and the details & quantity in the Products table, then product 3 and each of the attributes & Quanity pertaining to that item in the "ProductDetails" table, and so on, it shows every item as having every combination of attributes,

so although products 1 and 2 have no attributes, it will show product 1 with the first attribute of the other products and then show another product 1 with the next attribute from another product and keep going untill all the attributes in the whole of the Product details table is shown , then do the same thing with product 2 and then product 3 and so on.....

Also the products that have no attributes are not only shown with the attributes that they dont have, they are not shown without attribues and with the correct details in the list.




Here is my string, which has changed a million times, I am just learning as I go along so if it is obvious please be pateient with me and help me out...


rsResults.Source = "SELECT DISTINCT Products.ProductID, Products.ProductName, Products.ProductPict, Products.Quantity, ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color, ProductDetails.Quantity FROM Products, ProductDetails WHERE Products.ProductID = Products.ProductID AND Products.Quantity <= "& +rsResults__var1 & " OR Products.ProductID = ProductDetails.ProductID AND ProductDetails.Quantity <= "& +rsResults__var1 & " Order by Products.ProductID asc "


Can anyone see why it is doing this instead of only showing the attributes next to the items that have attribues?

Please help

Warm Regards,
Joe

View 14 Replies View Related

Help With Complicated SQL Query

Sep 26, 2005

Hi everyone.

This is my scenario:

I have two tables:
persons (id,age,roleid)
roles (roleid,description)

I want to build a sql query to produce the following rows (example):
range(age) role1 role2 role3 .... rolen
0 to 4 11 24 5 7
5 to 9 42 7 1 0
10 to 14 14 21 9 8
15 to 20 7 0 7 19

I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.

Thanks for all your help!

Roland

View 4 Replies View Related

Complicated Query

Mar 2, 2008

I am trying to use ssis (sql query or .net-script task)
to transfer data from one table to another.
I have difficulties to make the query:

i have one table tbl_games which fields are :
type ,startDateTime, EndDateTime, playerId.
I need to check every 1/2 hour according to the startDateTime,
How many times a person is playing and to show it in a new table
tbl_collectData like this:
TimeplayerId Games_0-30_secondsGames_30-50_seconds

8-8:30 A 3 2
8:30-9B 2 10
9-:30C 20 7

The length of the game is measured by the StartDateTime -EndDateTime,
and only games type # 2 is being collected.
The number 3 example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.

I tryed to start by doing something like this , but it is not a complete query:
INSERT INTO tbl_collectData
(Time, playerId, Games_0-30_seconds)
SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS numberOfCalls_0_30
FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND
(StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM')
GROUP BY playerId

I just don't know how to do it! Please help!!!!

View 2 Replies View Related

Complicated Query

Sep 6, 2007

Hi,I have two tables Trade table and Cons table. Records are inserted inboth the tables independent of each other. There are fields likeExc_Ref, Qty, Date in both the tables.I need to write a query which should give me records :1. Where there is missing Exc_Ref value in either of the table. i.e.If Trade table has a Exc_Ref value but missing in Cons table then thatrecord should be displayed. Similarly if Cons has a Exc_Ref valuewhich is not found in Trade table then that too should be displayed.2. In case where both the tables have matching Exc_Ref data then itshould display the record only when the remaining column does notmatch like Qty or Date.Please help me to resolve this complicated query.ThanksNick

View 2 Replies View Related

Complicated Query

Sep 18, 2006

Hi Guys

I Have not been able to solve this problem from quiete a while now.

I am using sql server 2005.


I have got a table which contains these columns - start date, end date and volumes
if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month


I have to perform a query on this table so that I can group the volumes for different months and different years.

Here is the sample data...









Service Start
Service End
FMIS Code
No of Units
Year
Month
Volumes

01-Oct-00
15-Aug-01
6440
32
?
?
?

01-Oct-00
30-Sep-02
6441
40
?
?
?

01-Oct-02
22-May-03
6440
78
?
?
?

01-Oct-02
23-May-03
6990
87
?
?
?

06-Mar-03
31-Jul-03
6997
102
?
?
?

07-Mar-03
31-Jul-03
6744
3
?
?
?

01-May-03
31-May-03
6440
789
?
?
?

23-Jun-03
31-Aug-03
6447
1000
?
?
?

29-Jun-03
30-Jun-03
6440
981
?
?
?

30-Jun-03
31-Jul-03
6000
50
?
?
?

01-Jul-03
08-Jul-03
6002
54
?
?
?

01-Jul-03
13-Jul-03
6000
562
?
?
?



I have to calculate the columns coloured in blue..

Please help guys!



Thanks

Mita

View 2 Replies View Related

Complicated Query

Mar 2, 2008



I am trying to use ssis (sql query or .net-script task)
to transfer data from one table to another.
I have difficulties to make the query:

i have one table tbl_games which fields are :
type ,startDateTime, EndDateTime, playerId.
I need to check every 1/2 hour according to the startDateTime,
How many times a person is playing and to show it in a new table
tbl_collectData like this:
Time playerId Games_0-30_seconds Games_30-50_seconds

8-8:30 A 3 5
8:30-9 B 2 10
9-:30 C 20 7

The length of the game is measured by the StartDateTime -EndDateTime,
and only games type # 2 is being collected.
The number 3 in the example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.

I tryed to start by doing something like this , but it is not a complete query:
INSERT INTO tbl_collectData
(Time, playerId, Games_0-30_seconds)
SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS Games_0-30_seconds
FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND
(StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM')
GROUP BY playerId

I just don't know how to do it! Please help!!!!

View 20 Replies View Related

Bit Of A Complicated SQL Query To Try And Write...

Feb 12, 2008

I'll start by giving the basic idea of the data structure I'm looking at in the database (pardon my MS Paint skills!):

And this all starts with a root plan. So (taking projects out of the picture for the minute), it could be a structure going any number of levels down like so: plan -> goal -> strategy -> plan -> goal -> strategy -> plan -> goal -> strategy, etc.And not forgetting, that a strategy can also have any number of projects, and all these relationships are one to many, so it ends up being like a large tree structure.
What I am trying to accomplish is user security related, in that I need to enforce rules set to say whether a single user is allowed to view a project. You can define the rules at any level in the structure above. So if for instance, a user is given a rule that they can access anything from a goal, they can see any projects that exist in any strategies underneath that goal in the tree structure. I hope this is making sense so far... The rules can also just be defined at a specific project level.
These rules (at the moment, I'm more than happy to change if this is a silly way of doing it) are set in a table that has the columns:user_id intplan_id intgoal_id intstrategy_id intproject_id intallow bitEvery row will have a user_id value, and one of either a plan_id, goal_id, strategy_id or project id (which I thought would be a simple way to pull out what kind of rule if needed), and the allow column just defines if it's an allow or deny rule (a specific deny rule on any object lower in the tree structure will override any cascaded allow rule).
The way my application needs to grab this information, is to simply have some way of returning a list project_id values for which the current user is allowed to access. I'm basically stumped. The closest I have gotten (which still didn't quite work) was going to end up using quite a large number of cursors... Which is bad. It can be inefficient to an extent, as once these values are gathered once, I can store them in a Session variable, so it's not grabbing them every time the user tries to load a project.
Thanks for any help!

View 4 Replies View Related

Complicated Cross-Tab Query

Feb 15, 2006

I have the following table and data:tblDepartments: (each department can only have a maximum of 3 sections attached to it)Columns: DepartmentName , SectionName Row1: dep1, sec1.0Row2: dep1, sec1.1Row3: dep1, sec1.2Row 4: dep2, sec2.0Row 5: dep3, sec3.0Row 6: dep3, sec3.1I need to derive the following table from tblDepartments :Columns: DepartmentName, SectionName1, SectionName2,  SectionName3Row1: dep1, sec1.0, sec1.1, sec1.2Row2: dep2, sec2.0, '', ''Row3: dep3, sec3.0, sec3.1, ''Any ideas?

View 3 Replies View Related

Complicated Query - SQL Gurus Help Please

Mar 20, 2008

Hi,
I am positive some SQL Gurus can help me with this complicated query. I am not sure if it is possible with a single query or needs multiple queries.

I have 3 objects. First, PaintOrder; Second, Combination; and Third, Material. Each Paint Order can have multiple Combinations and each Combination can have multiple (from 0 to 5) Materials. All the data is stored in single table called paintMatCom.

Here is the sample Data

PaintOrderId CombId MaterialId
1000 1 1
1000 1 2
1000 2 3
2000 1 1
2000 2 3
2000 2 4
3000 1 1
3000 1 2
3000 2 3
3000 3 4
3000 3 5

I have a Search Screen where user can select multiple Materials (upto 5) to search for the appropriate PaintOrders. Also, use has the option to select AND/OR between materials. Please look at the attached image for clear understanding.

Some of the sample searches are as follows (Combination IDs are not needed in the Output; just PaintOrder IDs).
1. Find PaintOrders with MaterialIDs 1 AND 2 OR 4
2. Find PaintOrders with MaterialIDs 2 OR 3 OR 4
3. Find PaintOrders with MaterialIDs 3 AND 4 OR 5 OR 1

Hope you got the idea. I think it would require some Joins and GROUP BY which I am not expert at. I would appreciate any help.

[IMG]C:Documents and SettingsOwnerDesktopmaterials.JPG[/IMG]

Thanks
vmrao

View 13 Replies View Related

Complicated Query? Cant Figure Out What I Need...

Jan 3, 2006

I have run into a problem, I have 2 fields in my database, both key fields:

Table 1
=====
Field X <key>
Field Y <key>

In field X, there are say about 3 records for each unique Field Y. I let my users query the data base like follows:

Enter the Codes you want: 1000 and 3000 and 8500

So I want to pick up records where there will be the above values for All Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there is even ONE of the X values not matching a record without a matching X value, leave it out.

i.e:
X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB
X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC

When the query runs, I want to see the following records:

X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB

BUT NOT:

X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC

because one of the X values was not matched (the last X value =9999 and not one of the requirements of the search)

So I guess I want something like this:

SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES (X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD

^^ Hope the above makes sense... but I am really stuck. The only other way I think I could do it is, copy all records that match all 3 X values into a temp table, and weed out any that are missing any one of the X values after they are copied but, I am running this on MYSQL 5.0 Clustered, and there is not enough room in memory for it probably... and query time has to remain under a second.

Anyhelp would be appreciated...

View 5 Replies View Related

SQL Server Query (complicated)

Jul 20, 2005

Folks,I have the following data in a table:4 NULL NULL2 abc NULL2 aaa NULL4 xyz NULL4 xyz pqr4 pyz xqrI want to get only one record for each record number. that is, theresult set should be like this:4 NULL NULL2 abc NULLPlease suggest how the query should be built. Thanks for the help.Murali

View 3 Replies View Related

Complicated Query In Ssis

Mar 2, 2008



I am trying to use ssis (sql query or .net-script task)
to transfer data from one table to another.
I have difficulties to make the query:

i have one table tbl_games which fields are :
type ,startDateTime, EndDateTime, playerId.
I need to check every 1/2 hour according to the startDateTime,
How many times a person is playing and to show it in a new table
tbl_collectData like this:
Time playerId Games_0-30_seconds Games_30-50_seconds

8-8:30 A 3 5
8:30-9 B 2 10
9-:30 C 20 7

The length of the game is measured by the StartDateTime -EndDateTime,
and only games type # 2 is being collected.
The number 3 in the example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.

I tryed to start by doing something like this , but it is not a complete query:
INSERT INTO tbl_collectData
(Time, playerId, Games_0-30_seconds)
SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS Games_0-30_seconds
FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND
(StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM')
GROUP BY playerId

I just don't know how to do it! other ways? ideas? Please help!!!!

View 6 Replies View Related

Complicated Sql Condition Query

Sep 12, 2007

Hi,
hope someone can help or suggest something to help me with the issue.

I have a list of projects. this list contains all master (parent) and all subprojects(child). when I click on a project I want to be able to retreive information about that project and it's subprojects. here is my delema. I want my sql query to check if project is master or sub. if master then get all data for this project and its subprojects but if it's sub then get data only for that sub. below is a sample data that I hope it clear things up

parent_ID child_id Type projname
-----------------------------------------------
100 100 P parent_X_proj
100 25 C child_X_proj
100 29 C child_X_proj2
200 200 P parent_Z
300 300 P parent_Y
etc................

this is how my table is constructed. my application passes child_id and what I want is if someone clicks on parent_X_proj I want to be able to retreive the three projects (100,25,29) but if someone clicks on child project (29) then I want only that project.
so I want my query to look for the type and if my type is P (parent) then get all project where parent_id = 100(for example) but if type= C then get child_id = 29.

I know it could be done in stored procedure buy my application cannon executre SP but only sql statements.

Thank you for any help

View 3 Replies View Related

Complicated Query - Select Based On Value

Jul 23, 2005

SQL2K on W2KserverI need some help revamping a rather complicated query. I've given thetable and existing query information below. (FYI, changing thedatabase structure is right out.)The current query lists addresses with two particular types('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check eachcontact for address type 'AM39DK3KD9' and then (2) if the contact hastype 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if thecontact does not have that type then select types ('MN30D843J2','SC93JDL39D'). (Context - the current query selects two standardaddress types "Main" and "Secondary"; we've added new data and now havetypes "Alternate Main" and "Alternate Secondary". If the Contact hasAlternate addresses, I need to select those; if not, I need to selectthe standard addresses. There are other address types in use, so Imust specify which types to select.)Can anyone point me in the right direction?Thanks very much! jamilehCREATE TABLE [CONTACTS] ([CONTACT_X] [char] (10),[LONGNAME] [char] (75),[ACTIVE] [bit])CREATE TABLE [CONTACTADDRESSES] ([CONTACT_X] [char] (10),[ADDRESS_X] [char] (10),[ADDRESSTYPE_REFX] [char] (10),[ACTIVE] [bit])CREATE TABLE [ADDRESSES] ([ADDRESS_X] [char] (10),[ADDRESSLINE1] [char] (60),[ADDRESSLINE2] [char] (60),[CITY] [char] (20),[STATE] [char] (2),[ZIPCODE] [char] (11),[PHONE] [char] (10))CREATE TABLE [REFERENCETABLE] ([REFERENCETABLE_X] [char] (10),[ADDRESS_X] [char] (10),[DESCRIPTION] [char] (60))CREATE TABLE [MASTERTABLE] ([CONTACT_X] [char] (10),[RECORDTYPE] [char] (1),[ACTIVE] [bit])CREATE VIEW vw_CONTACTInfo_ListLocASSELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,CONTACTADDRESSES.ADDRESSTYPE_REFX,Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,ADDRESSES.ZIPCODE, ADDRESSES.PHONEFROM CONTACTS INNER JOIN CONTACTADDRESSES ONCONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOINADDRESSES ON CONTACTADDRESSES.ADDRESS_X =ADDRESSES.ADDRESS_XINNER JOIN REFERENCETABLE Type_REFERENCETABLE ONCONTACTADDRESSES.ADDRESSTYPE_REFX =Type_REFERENCETABLE.REFERENCETABLE_XWHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFXIN('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =1)) AND(CONTACTS.CONTACT_X IN(SELECT CONTACT_X FROM MASTERTABLE WHEREACTIVE = 1 AND RECORDTYPE = 'E'))

View 10 Replies View Related

Query Table For Cheapest Items (but A Little More Complicated Than That)

Oct 18, 2006

I'm not sure if this is even possible but can i pull out rows from two tables (that have a one-to-many relationship) but only if they satisfy a few conditions.


tblWine tblSources
ID Name ID WineID Source Price Status
----------------- -----------------------------------------
1 Le Dome 1 1 Smith 100.00 IB
2 Teyssier 2 1 Jones 110.00 IB
3 Muscat 3 1 Hill 100.00 DP
4 2 Smith 135.00 DP
5 2 Hill 125.00 DP


I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.


tblWineSources
WineID Name SourceID Source Price Status
-------------------------------------------------------------
1 Le Dome 1 Smith 110.00 IB
1 Le Dome 3 Hill 100.00 DP
2 Teyssier 5 Hill 125.00 DP

View 9 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)
 scenario
In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.
 I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance

View 2 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

Reporting Services :: All Record Are Displaying On One Page - How To Display Page By Page

Nov 11, 2015

I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.

View 3 Replies View Related

Complicated SQL Help

Apr 27, 2007

Perhaps you can help with something that I consider kind of omplicated? (I am SO hoping that I am NOT going to have to do this manually!!!)

I have a table (about 3000 rows) where two of the columns have Domain User information.

COL1 has DOMAINUsername and COL2 has (or SHOULD have) DOMAIN@username.com

I need to look at each field in COL1 and if exists DOMAINusername, I need to populate COL2 with username@DOMAIN.com

Is this possible???

View 8 Replies View Related

It's Complicated

Aug 27, 2007

The low down:
Local Web Server on Windows 2003
Local SQL Server on Windows 2003
Hosting dynamic website tied to inhouse Access Application

Ok, basically, how it is set up, people can login to our website and enter data (insert record), on our end, we have an Access application where we can play with the data that was entered via the website. Currently, we do not have either server set up as a Mail server.

What we need to be able to do:
When a customer enters data on our website, their supervisor, and about 2-3 other people related to the transaction need to be emailed to be notified that an order was submitted. So how do I code that? On the page with the Insert Record? OR after Insert Record redirect them to another page that sends the mail out?

Which server do I enable the mail?

I was reading about SQL Mail etc, which would be good since we do mass emails to clients weekly, but I have no idea how to set that up and I look crossed eyed at any tutorial.

Do I want to set up theSQL server to also be a Mail Server that way we can use the SQL database to email as well as data entered from the website? But then again, the website points to the Web Server which pulls data off the SQL server (so unless the Web Server is a mail server, nothing will be sent, am I right?)

*sigh*

I know very little about SQL and I'm being asked to impliment this and I am 100% confused. I'm a graphics artist not a programmer! LOL

Thanks in advance to anyone who can/will help me.

View 6 Replies View Related

How To Run An Asp Page From Sql Query

May 28, 2008

Hi,
      I want to run an asp file from a query written in sql 2005.
     How can I achieve this in sql?
      Please let me know its very urgent.
 

View 1 Replies View Related

SQL Query From Web Page?

Sep 9, 2004

Hi, I have an SQl database that i want to build a simple search query for that would be accessible from an HTML page, what would be the best way of going about this? and what would be the best language to use?

Thanks in advance

View 1 Replies View Related

I Think I Have Made Something Way To Complicated...

Dec 27, 2007

All I am trying to do is use a SqlDataSource, Read that Data from the Session Variable, Do a RowFilter off the Data and then return the Data (Currently a DataSet but doesnt matter).  But Since I dont use SqlDataSource that much not sure all the code I have is neccasry:
 protected DataSet GetSearchAppData(string strWhere)
{
DataSet dsSearchAppTable = new DataSet("SearchAppTable");
SqlDataSource ss = new SqlDataSource();
DataView dv = new DataView();
DataSourceSelectArguments dsArgg = new DataSourceSelectArguments();

dsArgg = DataSourceSelectArguments.Empty;
ss = ((SqlDataSource)Session["SearchAppTable"]);
dv = (DataView)ss.Select(dsArgg);
dv.RowFilter = strWhere;
dsSearchAppTable.Tables.Add(dv.Table.Clone());

return dsSearchAppTable;
}
 And to mention it, dsSearchAppTable returns a Table with 0 rows.  Not sure what the heck I'm doing wrong.
 
J

View 1 Replies View Related

Help Complicated Updates

Jun 16, 2007

Hello guys,
I am askng for any help ...am trying to get this SQL language.

I want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100.
--------------------------------------------------------------------
There are three tables in the database: Customer, Product and Orders.

The Customer table has three column: Customer_id (PK), priority, Address.

The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)

The Product table has three columns as well: Product_id(PK), Product_name, Importance.

So the order table is connected to both the product and the customers table by respective foreign key.

View 2 Replies View Related

Complicated SQL Question

Mar 7, 2008

Our office uses a program to keep demographic and private customer information. This program is installed in a Dell PowerEdge Windows 2000 Server with 8 desktops spread throughout the office for our employees to use. We back the SQL database up every night on 2 WD external HD that we rotate and the server does a nightly backup too. Our software vendor is changing to a different software program that we don't care for since our current software works so well. As a result, we want to keep using the original software. Here's my question:

If we choose to stay with the original software, do we need this software to read and print out the SQL database for individual customers forever? In other words, if this software fails, is there a way to read and print the existing records without using the original software? Our software vendor tells us that our version will be discontinued and no support will ever be available again. But since we are required to keep our records for many years down the road, we need a way to print this information out if the customer wants to have a copy say 5 years from now. Is there any way to do this without the original software? Thank you very much for your help and opinions.

View 6 Replies View Related

Complicated Distinct

Mar 20, 2008

Hello,can anyone help me on this:
I have a table with 3 columns:id,time,description
let's say that I have this set of records:
ID TIME DESC
1 8:04 aa
1 8:05 aa
1 8:06 aa
2 8:07 ab
2 8:08 --
1 8:09 bb

I need a query that return
1 8:04 aa
2 8:07 ab
1 8:09 bb
means that from every set of same ID a need the distinct one,coz the first set of "1" refere to same person,the 4th "1" refere to different person.

Help me plz

View 7 Replies View Related

Complicated (at Least To Me) Insert

Jul 20, 2005

The best way to explain this is by example.I have a source table with many columns.SourceSYMBOLEXCHANGE_NAMECUSIPTYPEISSUE_NAMEand so onThen I have 3 other destination tables.ExchangesEXCHANGE_ID IDENTITYEXCHANGE_NAME UNIQUESecurityMasterSECURITY_MASTER_ID IDENTITYSYMBOL UNIQUECUSIPTYPEISSUE_NAMEand so onExchange_mm_SecurityMasterEXCHANGE_IDSECURITY_MASTER_ID-- The Source table has multiple rows of the same symbol.-- The Exchanges table is already populated with all the exchanges.-- A single security (in the SecurityMaster table) can belong to manyExchanges, hence the Exchange_mm_SecurityMaster table.Now. If I just wanted to insert into the SecurityMaster table withouttouching the Exchange_mm_SecurityMaster table I could just execute:INSERT INTO SecurityMaster ([SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME])SELECT DISTINCT[SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME]FROM SourceWHERE NOT EXISTS (SELECT * FROM SecurityMaster SM WHERE SM.SYMBOL =Source.SYMBOL)Now to the Exchange_mm_SecurityMaster. I need the individual identityvalues for each row inserted into SecurityMaster so I can then turnaround and insert into Exchange_mm_SecurityMaster. Here are theissues/possibilities as I see it.- @@IDENTITY will not work since I am not inserting a single row at atime- I guess I could INSERT INTO SecurityMaster first, THEN do anotherINSERT INTO Exchange_mm_SecurityMaster with different where clause.- I could create a stored procedure that does a single insert intoSecurityMaster and Exchange_mm_SecurityMaster. Then call thatprocedure for each row in the SELECT DISTRICT from the Source table.My main worry is the number of arguments passed in. My example onlyshows a few but a regular SecurityMster table could have 30-50columns.- Maybe do something with a trigger but I am not sure if I can passthe EXCHANGE_NAME value to the SecurityMaster trigger when that tabledoes not need it.Hope I explained it clearly. Any help would be appreciated.

View 3 Replies View Related

Query For Page Views

Apr 28, 2008

I have a table which records the amount of times a user views a webpage

tblpageview

id
pagename
view_date
userid

I would like to have a query that returns the unique pagename and the amount of times that page has been viewed. I've had a go with count etc. but cant seem to get it. Anyone can help?

View 3 Replies View Related

Page 2 - Query Problem

Sep 6, 2007

Hmm... the query should return the number of athletes with scores >= to your own.

So if you have the highest score in the world, for example, then the only score >= to your own is... yours. The count of that is 1, giving you rank 1.

If the order seems backwards to you just reverse the <= operator in the WHERE clause. Either way, as a responsible developer, never never never put a SQL query you don't understand into production.

Fixed the ServerID thing, too. It was there because I don't have a table that matches yours and wrote the query against one with a compatible structure, but different names.

View 4 Replies View Related

I Need To Pass Data Entered /created On The First Page To The Next Page And Populate The Next Page With Some Data From The Fir

Nov 28, 2006

Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.

When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.


I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)


I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.


Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦

View 2 Replies View Related

Complicated SQL Select Help Needed

Nov 13, 2007

Hi,
My users table contains a field called researchInterestId which looks like this: 1, 5, 10
This is because users where allows to select multiple options when choosing their research interests.
I have another table which contains the names of those research interests, which looks like this:
researchInterestId     researchInterestName
1                             Biology
2                             Cancer
 
My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10)
Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called?
Thanks
Sam

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved