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
ADVERTISEMENT
Jul 23, 2005
I'm having some trouble coming up with the correct select statement.Lets say I have the following two tables:---------------- ----------------Orders OrderItem---------------- ----------------PK OrderID (int) PK OrderItemID (int)OrderDate (datetime) FK OrderID (int)CustomerName (nvarchar) Priority (int)My search would basically return a list of all the orders placed withinthe last week. The select statement would include the entire contentsof the Orders table. The tricky part is that I also need to return thenumber of items within each order that have a priority of 1 (so theycan be displayed more prominently).The first part of the select statement is easy enough, but I'm stuck onthe count part. I'd appreciate any help. Here's what I have so far:SELECTOrders.OrderID,Orders.OrderDate,Orders.CustomerNameFROMOrdersWHEREDATEDIFF(dd, Orders.OrderDate, GetDate()) < 7--Jason
View 3 Replies
View Related
Mar 30, 2006
After hours of trying, I finally got a select statement to return what I needed, but I am not sure how I am doing is the most efficient way. Please give me your input.
Here's the situation: I want to allow customers to add to their magazine subscriptions online, so I created an aspx form that shows the magazines they are currently subscribed to and allows them to choose from other "available" magazines. In the available magazines field, I want to show all magazines that we have minus the ones the customer is already subscribed to.
MY pb table lists all of the magazines. The info table lists customers and their current magazine subscriptions.
I also have a table, PBExclusive that is for magazines that we have, but only want to be available to certain customers. So, I also need to make sure the "available" magazines field doesn't list any of the exclusions unless the customer is listed as the exception for that magazine.
Here is what I have:
("SELECT p.code, p.WebName FROM pb p WHERE (p.code IN(Select e.code FROM PBExclusive e WHERE e.id = " & lblID.Text & ") OR p.code NOT IN(Select e.code From PBExclusive e Where e.code = p.code)) AND p.code NOT IN (SELECT i.code FROM info i, pb p WHERE i.id = " & lblID.Text & " AND i.code = p.code)AND p.contract <> '1' AND p.code <> '00' AND WebListing <> '0' AND p.code <> '' AND p.code <> 'SU' AND p.code<> 'AGC' ORDER BY p.WebName", conn)
View 1 Replies
View Related
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
Jan 3, 2008
Hello..
The first problem:
I have 2 tables in MS SQL database: topicstbl and txtfilestbl. And every one have column topicID.
And in both I have fulltext column I want to search in it; but in the first one I want to search in the fulltext column (topicflds) and also in normal columns (topicname and topicsn)
I need to search in these tables and get the (topicID) column as result by 1 table without repeated results.
I tried this code:
SELECT topicID, KEY_TBL.RANK FROM (SELECT topicID, 255 as RANK FROM topicstbl WHERE topicname like '%search word%' or topicsn like '%search word%') AS KEY_TBL
UNION
SELECT topicID, KEY_TBL.RANK FROM topicstbl AS topicstbl INNER JOIN CONTAINSTABLE(topicstbl, topicflds, 'search NEAR word') AS KEY_TBL ON topicstbl.topicID = KEY_TBL.[KEY]
UNION
SELECT topicID, KEY_TBL.RANK FROM txtfilestbl AS txtfilestbl INNER JOIN CONTAINSTABLE(txtfilestbl, txtfile, 'search NEAR word') AS KEY_TBL ON txtfilestbl.txtfileID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC
But the problem is the result table has repeated records (same topicID, different Rank Value).
Also I used "SELECT DISTINCT" but same problem.
The second problem:
I want to merge the previous code with ROW_NUMBER code to get results between x and y.
Like this code:
WITH OrderedOrders AS (SELECT *, ROW_NUMBER()
OVER (order by topicID DESC) as RowNumber
FROM topicstbl)
SELECT * FROM OrderedOrders
WHERE RowNumber between 1 and 50
But without change the first order.
Now how to do all these??!
View 6 Replies
View Related
Feb 19, 2008
i m getting results like
metrics mdate items total
------------------------------------------------
001awer 2007-1-14 1 100.00
net100 2007-1-14 1 10.00
214sdf 2007-1-14 1 25.00
net16wrew 2007-2-14 2 80.00
so in short i want values of same items - total
but different metrics
like
metrics mdate items total
--------------------------------------------------------
001awer 2007-1-14 1 135.00
net100
net100
net16wrew 2007-2-14 2 80.00
how should i get this?
View 4 Replies
View Related
Aug 8, 2007
Hi,
Table A
member_no - character 5course1 - character 1course2 - character 1
Table Bcourse_no - character 1course_name - character 20I'd like a resultant view that contains A.member_no, A.course1, B.coursename as COURSE1NAME, A.course2, B.course_name as COURSE2NAMEI know how to make the join and pick up the name for course1 but I DON'T know how to pick up both names of course 1 and course 2 at the same time.I'm thinking I have to make a join on one column and then join the result of that a second time to pick up the other column's value?
Thanks in advance for any assistance,Stewart
View 2 Replies
View Related
Mar 4, 2008
Hi,
I am trying to do a simple select statement which returns data from the table. The criteria is only if the count of the records is more than 10. Say, if a vendor called test exists 10 times in the table, I would like to display that vendor only, if less than 10, then that vendor won't be displayed at all.
Here is what I have started.
Select vendor_id, vendor_name, Invoice_Date, Invoice_Number from Vendors where Invoice_Date Between '01/01/2007' And '03/03/2008' Group By vendor_id, vendor_name, Invoice_Date, Invoice_Number Having Count(Invoice_Number) > 10
This doesn't seem to work. What am I doing wrong here?
View 7 Replies
View Related
Mar 26, 2008
Hi,
I have 2 tables. Plan and User. In my User table I have the following columns
UserId ClientId Login Password PlatformId
1 1 abc XXX 1
4 1 jcn XXX 2
20 1 djfd XXX 1
and the Plan table
PlanId PlanName
1 abc plan
2 Plan etc..
and ihave a sproc that will return the all the plans based on the clientId but i want to also get all the plans that have PlatformID 1 for the same client. This is my Sproc..
SELECT
NULL PlanId,
'< All Plans >' PlanName
UNION
SELECT Distinct
PlanId,
PlanName
FROM
Plan cp
Join User u on cp.ClientId = u.ClientId
WHERE
u.PlatformId = 1
AND
cp.ClientId = @ClientId
OR
(
cp.ClientId = cp.ClientId
AND
@ClientId IS NULL
)
ORDER BY
PlanName ASC
But they dont filter it by PlatformId
any help will be appreciated.
Regards,
Karen
View 3 Replies
View Related
May 27, 2008
Hi all,
I need some help please with an select query. here's so scenario:3x Tables linked: Vehicles, Requests & ResultsTable Vehicles - vId, make, model, priceTable Requests - rId, name lastname, requestedmake, requestedmodelTable Results - vId, rId
I am trying to do a select on the Vehicles table where vId is not already also in the Results table for an given rId
What happens on the app: 1. dealers add cars on website - populate Vehicles tbl. 2. clients do a request for a vehicle - Populates Requests tbl. 3. dealers go page where they can asossiate their vehicles with requests - which populates the results tbl with both id's.All help is very much appreciated.
RegardsJacques
View 6 Replies
View Related
Mar 15, 2004
Hi everyone,
I've got a problem that I've been unable to work out.
I'm wanting to count the number of people that fall into a specific age group. For simplicity, lets say I'm JUST after people that are 10 years old.
This is what I'm trying to do:
SELECT 'Aged 10 years old', count(*)
FROM [People]
WHERE ([DOB] + [Date Entered]) = ?5 years?
Does anyone have a solution to this?
Thanks
Andrew
View 2 Replies
View Related
May 7, 2004
I have worked on developing a project that I am pleased to say is comming along nicely considering I have only been doing programming for a few weeks but I am now stuck and may be going down the wrong path.
I have two tables one that is used for logging via username and password, it also holds a third coloumn realting to the suers account that they do not enter. Once they have logged in I am recalling the users identity and running a query to establish what account the user belongs to. Later on in the script I am trying to run a query on a table called orders that list all orders belonging to that account that was established in the earlier query labelled as 'accountrequesting'.
The script I have below returns all orders for all accounts even though I have tried a wide variety of endings on the Sql search on the orders table.
The code may look a little untidy and to advanced programmers may look rough but I am still learning.....please can anyone help.
sub Page_Load(sender as Object, e as EventArgs)
'collecting the user identity
Dim user as string
user=request("User.Identity.Name")
'running a query to establish what account they have access to all from users table
Dim accountrequesting as string
accountrequesting = "select account_id from users where username = " & user & "'"
'opening the data source and connection
Dim ConnectionString As String = "Data Source=xxxx;Database=xxxx;User ID=xxxxxxx;Password=xxxxxxx"
Dim Myconn as New SQLConnection(connectionstring)
'this is the line I am stuck on, the account id in this orders table needs to match against the value gained in accountrequesting above tried a lot of different endings
Dim MySQL as string ="select prodshipped, account_id from orders"
'produces output
Dim ds as Dataset=New Dataset()
Dim Cmd as new SQLDataAdapter (MySQL, Myconn)
Cmd.Fill (ds,"orders")
MyDataGrid.datasource=ds.tables("orders").defaultview
MyDataGrid.Databind()
End Sub
View 9 Replies
View Related
Jan 26, 2006
What is the correct syntax for querying the first 20 characters of amemo field?I'm trying toSELECT CAST (varchar(20) fieldname) as newfieldnameand i get a bunch of errorsCan someone please help me?MS Access DB used with ColdFusionThanks
View 4 Replies
View Related
Aug 16, 2006
I have a table consisting of two fields, OStype and OSversion, withentries like:OStypeOSversionsolaris 2.5solaris 2.6redhat 6.2redhat 6.2solaris 8redhat AS4solaris 10solaris 10redhat AS2.1redhat AS3redhat AS4I want to create a select statement that returns for each OS type, thetotal number of entries and for each version the total number ofentries.In the example the result would be:OStype OStype Count OSversion OSversion Countsolaris 52.5 12.6 18 110 2redhat 66.2 2AS2.1 1AS3 1AS4 2Thanks in advance for your help.Ian
View 3 Replies
View Related
Jul 31, 2007
Hey guys, Hoping someone can help me ot here.. I am developing an SQL statement that can be run on DB2 and have got something already setup, but i need to modify it so that it only returns the highest record for a certain field.
let me explain/Show you what i have got, and what i want to acheive: The highlighted rows, is what i want to work with, as i only want to return the row with the highest value in ICPNLX (first Column)
Im hoping someone can help me out please, one person told me to use a sub-select query, but i am having trouble doing it.
SQL CODE:
Select icpnlx , IBPUSL, IBPUST, IBSUNO, XBXSMT, ICPUNO, IBITNO,ICPNLI, ICPNLS, ICREPN, IBRVQA-IBSDQA as REMAIN, MBWHSL
FROM mvxbdtaCF3.MPLIND INNER Join
mvxbdtaCF3.MPLINE On ICCONO = IBCONO And ICFACI = IBFACI And
ICWHLO = IBWHLO And ICPUNO = IBPUNO And ICPNLI = IBPNLI And
ICPNLS = IBPNLS INNER Join
mvxbdtaCF3.XANLIN On ICPUNO = XBPUNO And ICPNLI = XBPNLI And
ICPNLS = XBPNLS And IBCONO = XBCONO And IBFACI = XBFACI INNER JOIN MVXBDTACF3.MITBAL ON ibitno = mbitno and ibcono = mbcono and ibwhlo = mbwhlo
WHERE (IBCONO = 200) And (IBFACI = '010') And (IBWHLO = '010') And (XBXSMT = '91730') AND
IBITNO = '8552-6654' AND (ICREPN > 0) And (IBPUSL >= '50' And IBPUSL < '75')
RESULTS:
ICPNLX
IBPUSL
IBPUST
IBSUNO
XBXSMT
ICPUNO
IBITNO
ICPNLI
ICPNLS
ICREPN
REMAIN
MBWHSL
2
50
70
GRCUSA
91730
T05091
8552-6654
10
1
16978004
4
G-25-1
3
50
70
GRCUSA
91730
T05091
8552-6654
10
1
16978004
4
G-25-1
4
50
70
GRCUSA
91730
T05091
8552-6654
10
1
16978004
4
G-25-1
2
50
50
GRCUSA
91730
T50296
8552-6654
30
0
16978008
9
G-25-1
2
50
50
GRCUSA
91730
T50296
8552-6654
50
1
16978009
23
G-25-1
many many thanks guys.
Scotty
View 5 Replies
View Related
Mar 20, 2007
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
View 2 Replies
View Related
May 2, 2008
Hi,
Any help with this would be great. I have SQL tables all in the following format:
ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE
Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g
(326-229) + 278 = new measure_value.
This is for each id-product where the following 3 conditions meet:
the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.
thanks
Jay
p.s this what i have started with at the moment as a test however it dont work.
SELECT ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE
FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT,
dbo.DPOUT_EXCEPTIONS_326.ID_MARKET,
dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET,
dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION,
dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE,
dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE'
FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on
dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product
where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product
and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET
and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)
View 3 Replies
View Related
Jun 2, 2008
I'm very green with SQL so I could do with some advice please.
I need to pull some data from a table based on the year portion of a datetime field, so far I've got this...
USE MfgSys803
SELECT orderdate, ordernum FROM orderhed
WHERE ((SELECT CONVERT(VARCHAR(4),GETDATE(),111)) = (SELECT CONVERT(VARCHAR(4),ORDERDATE,111) FROM orderhed))
... the field 'orderdate' is the datetime. The purpose of the WHERE statement is to get the current year fromt he system and then compare this to the current year of the field 'orderdate'.
Unfortunately I get the error...
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
... the 'SELECT CONVERT' portions of the WHERE work fine on thier own but I can't use them together.
Hopefully this makes sense, thanks :)
View 7 Replies
View Related
Jan 11, 2002
I have 2 SQL 2000 servers. One has been added as a linked server.
They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain.
Thanks
View 1 Replies
View Related
May 30, 2006
Hi All,
With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?
I cannot see any difference in performance between the two.
Thanks.
View 6 Replies
View Related
May 15, 2008
Greetings,
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM
...
...
ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]
END
Thanks
View 14 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
