Complex Select Query

Mar 13, 2008

Hi.

I have two tables. The first is a price proposal table which contains fields like the index of the proposal, proposal decription, proposal date etc. The second table contains data about the products associated with a certain price proposal. This table has a proposal index field and other fields like the index of the product (a unique identifier), product catalog number, price of the product etc.

I want the rows which returned by my select query to display the following:

The Proposal index, description, date, product catalog number grouping, the number of products with a certain catalog number in this proposal, the sum of the prices of these products.

Example:

PriceProposalTable : ProposalIndex = 1 ----->  ProductsTable: ProposalIndex = 1, ProductIndex = 1, CatalogNumber=A, Price=100

                                                                                      ProposalIndex = 1, ProductIndex = 2, CatalogNumber=A, Price=90

                                                                                      ProposalIndex = 1, ProductIndex = 3, CatalogNumber=B, Price=80

 

PriceProposalTable : ProposalIndex = 2 ----->  ProductsTable: ProposalIndex = 2, ProductIndex = 4, CatalogNumber=C, Price=100

 

I want the select query to return:

ProposalIndex         ProposalDescription     ProposalDate    CatalogNumber  NumOfProducts     SumOfPrices

          1                             ...                         ...                   A                     2                      190

          1                             ...                         ...                    B                    1                       80

          2                             ...                         ...                    C                    1                      100

 

I hope the example is clear enough. I would appreciate your help on this.

Thanks.

View 3 Replies


ADVERTISEMENT

Complex SQL Select Query

Apr 29, 2007

Hi all
I im trying to write a SELECT query to display a set of my logged in user's 'Friends'. Although the way that i have designed my tables means that its very complex, and im hoping someone out there can tackle it!
To start ill show you how i contruct friends:
Friends
FriendshipID                     Incrementing PKInviteeID                           Unique UserID of person who offered the friendship linkInvitedID                           Unique UserID of person who was invitesApprovedBInvitee              True/False - sets to 'True' by default (probably isnt needed come to think of it )ApprovedByInvited            True/False/Declined - an nvarchar
Next, I have my UserDetails table:
UserDetails
UserID                            Unique UserID PKUserName                      Unique Username  (foreign key from aspnet_Users as created by aspnet_regsql.exe)Avatar                             Integer which represents an image name in a photos folder
So, on the myFriends.aspx i firstly set an invisible label's text property to the unique UserID of the logged in user. This gives me a control paremater for the select statement.
The information I want to display is just the UserName and Avatar of all users who are friends with the logged in user.
I know that to get the records where the logged in user is either that Invited or the Invitee, I do this:WHERE (@loggedInUser = Friends.IniteeID) OR (@loggedInUser = Friends.InvitedID)
 (that will show the logged in user as his own friend but i dont mind that)
After that I am stuck more or less... it seems to become very complex... maybe i need 2 queries?
If anyone can help i would be very very grateful

View 12 Replies View Related

Complex Select N Query

Mar 28, 2008

Ok, I have a table with two columns in the group by clause, ServerID and Database ID. I have 9 unique ServierIDs and 117 unique DatabaseIDs. I need a query that says gimme the 5 highest database id hitcount totals for each server id. I would like to do this without a #temp table or a cursor.
here's what I have:

-------------------------------------------------------------------
create table #server_hitcount_summary(
SID int not null,
DBID int not null,
ORD int not null,
SUM_HITCOUNT bigint not null,
SUM_MAXWRITES bigint not null,
SUM_MAXREADS bigint not null,
SUM_MAX_DURATION bigint not null,
SUM_MAX_CPU bigint not null
)

declare @id int
set @id = 1
while (@id < 20)
begin
insert #server_hitcount_summary
select top (@top)
SID,
DBID,
ROW_NUMBER() over (order by sum(hitcount) desc) as ORD,
sum(hitcount) as SUM_HITCOUNT,
sum(MAX_WRITES) as SUM_MAXWRITES,
sum(MAX_READS) as SUM_MAXREADS,
sum(MAX_DURATION) as SUM_MAX_DURATION,
max(max_cpu) as MAX_MAX_CPU
from
hitcounts_table group by SID, DBID having SID = @id and DBID <> 1 order by sum_HITCOUNT desc
set @id = @id + 1
end
select
SID,
DBID,
ORD,
SUM_HITCOUNT,
SUM_MAXWRITES,
SUM_MAXREADS ,
SUM_MAX_DURATION,
SUM_MAX_CPU
from #server_hitcount_summary order by SID asc
drop table #server_hitcount_summary
-------------------------------------------------------------------

What d'ya think? is there a better way to do this?


-The Universe is naturally cold. It takes the power of a star to change that.

View 2 Replies View Related

Complex/annoying SELECT/JOIN Query

Feb 8, 2008

I have a very confusing/complicated query that I am trying to run and getting not the results that i want.

Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables.
When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.



Select t1.*, t3.field2, t3.field3
FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6

I suspect there is plenty wrong with this query - where should I start correcting it?

View 10 Replies View Related

Help With Complex SELECT

Jan 31, 2007

Can someone help me with an advanced query?I have two tablesTable 1: EmployeesCols: UID, lname, fnameTable 2: StatEntryCols: UID, Timestamp, descriptionThe queary should reaturn all the information in both tables. If morethan one entry exists in the second table, it should return the onewith the greatest timestamp. If not entries exist I would like thesecond table columns set to "no value"Something link:select Employees.*,StatEntry.* from Employees JOIN StatEntry ONemployees.uid == statentry.uid WHERE timestamp in (selectMAX(timestamp) from statentry where uid=employees.uid).Anyone db guru's out there?-SJ

View 4 Replies View Related

Complex SELECT

Jul 20, 2005

Hi,I have problem about writing a proper SELECT query for the followinggoal:Table name: peoplelistcolumn 1: id (not NULL, auto_incremental)column 2: namecolumn 3: countrynow, there are about 7,000 rows in this table. I want to select out:first 10 or less people in the table for each country.for example: suppose there are :1000 people from US3000 people from UK3000 people from CanadaI want to list totally 30 people, i.e. 10 people from each country.The problem is , the actual table includes many countries, not onlythree. How can I do this by a SELECT sql query ?Thanks.Han

View 1 Replies View Related

Complex SELECT Statement Help

Aug 30, 2007

Hi All,
 My sql is a little rusty, i ve been trying to do few things but still no luck. I m trying to query some data in one column based on certain . Here is my puzzle:
I have 7 tables: categories, characteristics, configs, rm_cat, rm_chars, rm_conf and rooms.
And here are the details on these tables:
- categories: {cat_id, cat_name}
- characteristics: {char_id, char}
- configs: {conf_id, conf}
- rm_cat: {room_id, cat_id}
- rm_chars: { room_id, char_id}
- rm_conf: {room_id, conf_id}
- room: {room_id. room_name}
 
I m trying to select a "room_name" based on a certain cat_id, char_id and conf_id and i don't know how to do this.
 
Sincerely,

View 3 Replies View Related

Complex SQL SELECT Statement

Jul 23, 2004

There are 3 tables, VendorLists, Vendors, and Referrals.

VendorLists is a linking table. It has VendorListID, VendorID, and ListID fields.

Vendors is linked to VendorLists through the VendorID field (one to many)

Referrals is linked to VendorLists through VendorListID (one to many)

I'm given a value for ListID and have to pull records from both the Vendors and Referrals table (a referral is a description of a vendor, one to many).

I am able to do this with the following SQL select statement:

SELECT Referrals.Description, Vendors.Company
FROM Referrals CROSS JOIN Vendors
WHERE Referrals.VendorListID IN
(SELECT VendorListID FROM VendorLists WHERE (ListID = lid))
AND
(Vendors.VendorID IN (SELECT VendorID FROM VendorLists WHERE ListID = lid))
ORDER BY Vendors.VendorID

This pulls all the appropriate records and values that i need and orders them by the identifier for the vendor. However, I want to randomly order the vendors but still group them together by company, so, if the VendorID is 1 for "joe's crab shack" and 2 for "billy's ice cream shop", the above will always list joe's crab shack first and all it's referrals. i want to be able to randomly order the vendors, but still keep the referrals of those vendors grouped together so that when i iterate over them, they're grouped.

Does anyone have any idea how to do this? I'm stumped!!

View 4 Replies View Related

Complex SELECT Statement

Feb 24, 2004

TABLE : USER
USERID
1
2

TABLE : TIME
TIMEID|USERID|RT|OT|DOT|DATE
1|1|8|2|1|2004-02-01
2|1|8|2|0|2004-02-02

3|2|8|0|0|2004-02-01
4|2|8|2|2|2004-02-02

RT : Regular Time
OT : Over-Time
DOT : Double Over-Time

I need to write a query to display the results in this way

USERID|DATE|TIME
1|2004-02-01|8
1|2004-02-01|2
1|2004-02-01|1

1|2004-02-02|8
1|2004-02-02|2

2|2004-02-01|8
2|2004-02-02|8
2|2004-02-02|2
2|2004-02-02|2

basically, the time entries for each user each day , seperate rows for RT, OT, DOT if they are not equal to 0

ive been breaking my head on this for quite a while. any help is appreciated.

thanks

View 5 Replies View Related

Help: Complex Select Statement

Jul 20, 2005

Here is my SQL string:"SELECT to_ordnum, to_orddate," _& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ONDDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ONDOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from resultof outer select) AS Total" _& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnumDESC"The outter Select statement returns various amounts of order numbersrepresented by 'to_ordnum' in the outer Select clause which has tomeet the critera in the outer WHERE clause. I would like to placethese numbers selected into the inner WHERE clause for the innerselect statement where DOMBOM2.b2_ordnum = ?the order selected byouter select statement.I have tried placing to_ordnum into that location but the SQL2000server does not process it.Any suggestions, ideas?Thank you,Brett

View 1 Replies View Related

Complex SQL Select Statement That Works But...

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

Complex SELECT Statement Issue

Sep 30, 2007

I may not be seeing the forest through the trees here but here goes.  I've got a table of computer configurations with columns for cpu, ram, m/b, hdd, etc.  The values in those columns are related to the id field in another table named parts.  The parts table has columns, id, partnumber, description, and cost.  What I want to do is be able to pull a record from the computer configurations table and instead of getting the integers in the cpu, ram, etc. fields I want to put the corresponding description field from the parts table.  For example:I want this...id    Name            CPU            RAM        MB              HDD    ... 1   Fast Machine   Fast CPU   Big RAM   Greate MB   Huge HDDNOT this....id    Name            CPU            RAM        MB              HDD    ...
1   Fast Machine   1               3               2                7 Below is a screenshot of my actual table relationships.Thanks in advance 

View 14 Replies View Related

A Complex SELECT Stored Procedure, Pls Help

Apr 6, 2008

hi!
i have two tables: Pictures (PictureID, UserName, UserID, DateAdded, Comments int) and UserVisits (VisitID, UserName, UserID, PictureID, NewComment bit)
if a new comment is added on a picture with PictureID = 4, i update NewComment from UserVisits with 1 (true) where PictureID = 4 to all rows, 
and if a user visits a picture with PictureID = 5, NewComment will be set to 0 back where pictureID = 5 (something like asp.net, when a reply is added to a therad)
i need to make a stored procedure with UserName input parameter witch selects ALL pictures from Pictures table and selects NewComment (true or false) from UserVisits witch corresponds with that UserName
!!! Is possible that in UserVisits to not have yet any column where UserName corresponds with the user who visits if the user did not visited that picture yet (this stored procedure will be used on a Repeater witch lists all the pictures)
if i was not clear please tell me, sorry i am verry bad at T-SQL language
thanks in advance

View 2 Replies View Related

Row_number Selecting From A Complex Select Statement

Sep 3, 2007

Hi,


Code Snippet


This is difficult to explain in words, but the following code outlines what I am trying to do:


with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)

select * from myTableWithRowNum
Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?

View 4 Replies View Related

Problem With Building SQL To Select Complex Joined Olumns

Mar 17, 2006

John writes "Problem with building SQL to select columns from three joined tables, all of which can have an outer join to a fourth table.

Environment is SQL Server 2000.

Here is a simplified version of schema:

EveTable:
EveTableId (key)
Title
OrgTableId
LocTableId
ImageId (can be null)

EveTable Joins to:

OrgTable:
OrgTableId (key)
Title
ImageId (can be null)

EveTable also Joins to:

LocTable:
LocTableId (key)
Title
ImageId (can be null)

All three tables join to:

ImgTable
Title
ImageId
Title

Problem: I wish to: Select EveTable.Title, LocTable.Title, OrgTable.Title, ImgTable.Title [all] where EveTableID=n

I am currently stuck at building even the basic SQL for this!

Best Regards,"

View 1 Replies View Related

I Need Some Help With A Complex Query

Jan 6, 2007

I've written a lot of queries in the past, but I'm having a lot of trouble with this one.

View 4 Replies View Related

Very Complex Query

Mar 19, 2007

I'm sure there is a way of cracking this, but I can't think of a good solution. Right now I am not happy with the solutions I come up with, one of which takes 4 minutes to run on SQL Server
The scenario: User is presented with search page where one or more search terms can be entered/selected. There are no required parameters. It can be any or all of the possibilities presented. Below is a model of the search parameters presented.
The user will either select to show more options under Profile ABC, or go down to Profile STU or Profile XYZ to show more options, or even select all Profiles and then select from Type 1 and either a. or. b. or. c. or ALL of the above.
I cannot predict what a user will make part of the search query so I have to have a stored procedure ready which can handle any or all of the parameters  a user may select.
Am I biting off more than I can chew (it seems so)? Or is there an elegant way of handling the unknown combination of search parameters that a user might throw into my sql query?
I'm running this under ASP 1.0 and SQL Server 2000.
 
[check to show the options below] Profile ABC
[check to shore more options] Type 1




A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Type 2





A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Type 3





A. Contains fields for entering another data string and selecting from drop-down boxes




B. ditto
C. ditto
D. ditto
[check to select more options] Type 4





A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Profile XYZ (as above)
[check to select more options] Profile STU (as above)

View 6 Replies View Related

Complex Query

Oct 30, 2007

HI.
I have 3 tables
1- std with : stdID , programID.
2- Programs with :ProgramID , Cost
3 - Movements with : stdID , balance.
the first table contain the stdID and ProgramID , some times the std hasn't programID that mean he hasn't programID. then we return null.
if the std has programID there is to cases.
the first one he have a movement on his balance then we get the biggest balance for the std.
the second case he hasn't any moventen then we get his balance from Programs  table by the ProgramID .
 
I need sql server function that return table like this
stdID , Balance
 that means every std with his Balance.
Regards.

View 11 Replies View Related

Complex Query

May 13, 2008

This is too complex anyone know how to make it less complex.
I am trying to get all the selected fields from contacts into a datagrid where the other fields contain a string in textbox1.
This works
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%'))
When i add all the rest of the fields it says its too complex. Please Help
 
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%') AND ([B] LIKE '%' + ? + '%') AND ([BB] LIKE '%' + ? + '%') AND ([BD] LIKE '%' + ? + '%') AND ([BA] LIKE '%' + ? + '%') AND ([BH] LIKE '%' + ? + '%') AND ([BL] LIKE '%' + ? + '%') AND ([BN] LIKE '%' + ? + '%') AND ([BR] LIKE '%' + ? + '%') AND ([BS] LIKE '%' + ? + '%') AND ([BT] LIKE '%' + ? + '%') AND ([CA] LIKE '%' + ? + '%') AND ([CB] LIKE '%' + ? + '%') AND ([CF] LIKE '%' + ? + '%') AND ([CH] LIKE '%' + ? + '%') AND ([CM] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([CR] LIKE '%' + ? + '%') AND ([CT] LIKE '%' + ? + '%') AND ([CV] LIKE '%' + ? + '%') AND ([CW] LIKE '%' + ? + '%') AND ([DA] LIKE '%' + ? + '%') AND ([DD] LIKE '%' + ? + '%') AND ([DE] LIKE '%' + ? + '%') AND ([DG] LIKE '%' + ? + '%') AND ([DH] LIKE '%' + ? + '%') AND ([DL] LIKE '%' + ? + '%') AND ([DN] LIKE '%' + ? + '%') AND ([DT] LIKE '%' + ? + '%') AND ([DY] LIKE '%' + ? + '%') AND ([E] LIKE '%' + ? + '%') AND ([EC] LIKE '%' + ? + '%') AND ([EH] LIKE '%' + ? + '%') AND ([EN] LIKE '%' + ? + '%') AND ([EX] LIKE '%' + ? + '%') AND ([FK] LIKE '%' + ? + '%') AND ([FY] LIKE '%' + ? + '%') AND ([G] LIKE '%' + ? + '%') AND ([GL] LIKE '%' + ? + '%') AND ([GU] LIKE '%' + ? + '%') AND ([GY] LIKE '%' + ? + '%') AND ([HA] LIKE '%' + ? + '%') AND ([HD] LIKE '%' + ? + '%') AND ([HG] LIKE '%' + ? + '%') AND ([HP] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([HU] LIKE '%' + ? + '%') AND ([HX] LIKE '%' + ? + '%') AND ([IM] LIKE '%' + ? + '%') AND ([IP] LIKE '%' + ? + '%') AND ([IV] LIKE '%' + ? + '%') AND ([JE] LIKE '%' + ? + '%') AND ([KA] LIKE '%' + ? + '%') AND ([KT] LIKE '%' + ? + '%') AND ([KW] LIKE '%' + ? + '%') AND ([KY] LIKE '%' + ? + '%') AND ([L] LIKE '%' + ? + '%') AND ([LA] LIKE '%' + ? + '%') AND ([LD] LIKE '%' + ? + '%') AND ([LE] LIKE '%' + ? + '%') AND ([LL] LIKE '%' + ? + '%') AND ([LN] LIKE '%' + ? + '%') AND ([LS] LIKE '%' + ? + '%') AND ([LU] LIKE '%' + ? + '%') AND ([M] LIKE '%' + ? + '%') AND ([ME] LIKE '%' + ? + '%') AND ([MK] LIKE '%' + ? + '%') AND ([ML] LIKE '%' + ? + '%') AND ([N] LIKE '%' + ? + '%') AND ([NE] LIKE '%' + ? + '%') AND ([NG] LIKE '%' + ? + '%') AND ([NN] LIKE '%' + ? + '%') AND ([NP] LIKE '%' + ? + '%') AND ([NR] LIKE '%' + ? + '%') AND ([NW] LIKE '%' + ? + '%') AND ([OL] LIKE '%' + ? + '%') AND ([OX] LIKE '%' + ? + '%') AND ([PA] LIKE '%' + ? + '%') AND ([PE] LIKE '%' + ? + '%') AND ([PH] LIKE '%' + ? + '%') AND ([PL] LIKE '%' + ? + '%') AND ([PO] LIKE '%' + ? + '%') AND ([PR] LIKE '%' + ? + '%') AND ([RG] LIKE '%' + ? + '%') AND ([RH] LIKE '%' + ? + '%') AND ([RM] LIKE '%' + ? + '%') AND ([S] LIKE '%' + ? + '%') AND ([SA] LIKE '%' + ? + '%') AND ([SE] LIKE '%' + ? + '%') AND ([SG] LIKE '%' + ? + '%') AND ([SK] LIKE '%' + ? + '%') AND ([SL] LIKE '%' + ? + '%') AND ([SM] LIKE '%' + ? + '%') AND ([SN] LIKE '%' + ? + '%') AND ([SO] LIKE '%' + ? + '%') AND ([SP] LIKE '%' + ? + '%') AND ([SR] LIKE '%' + ? + '%') AND ([SS] LIKE '%' + ? + '%') AND ([ST] LIKE '%' + ? + '%') AND ([SW] LIKE '%' + ? + '%') AND ([SY] LIKE '%' + ? + '%') AND ([TA] LIKE '%' + ? + '%') AND ([TF] LIKE '%' + ? + '%') AND ([TN] LIKE '%' + ? + '%') AND ([TQ] LIKE '%' + ? + '%') AND ([TR] LIKE '%' + ? + '%') AND ([TS] LIKE '%' + ? + '%') AND ([TW] LIKE '%' + ? + '%') AND ([UB] LIKE '%' + ? + '%') AND ([W] LIKE '%' + ? + '%') AND ([WA] LIKE '%' + ? + '%') AND ([WC] LIKE '%' + ? + '%') AND ([WD] LIKE '%' + ? + '%') AND ([WN] LIKE '%' + ? + '%') AND ([WR] LIKE '%' + ? + '%') AND ([WS] LIKE '%' + ? + '%') AND ([WV] LIKE '%' + ? + '%') AND ([YO] LIKE '%' + ? + '%'))
 

View 10 Replies View Related

A Complex Query

May 20, 2008

hi how are you please help me in my problem which i can't make it.
 Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category.
now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table.
 is that possible ?
please if any one help can me in my problem.

View 4 Replies View Related

A Complex Query

Feb 10, 2004

I have the following SQL 2000 database table:
NEWS (IDNews, Country, PublishDate, Title)

I have to get a dataset containing only one record for each country, having most recent publish date.
Any suggestions? Thanks.

View 3 Replies View Related

Complex Query?

Feb 8, 2005

I have 2 tables, say table1, and table2. There is a DocID (primary key) in table1. In table2, DocID is the foriegn key. There can be more than 1 DocID.

this is the table structure (sample)

Table 1:
DocID DocName OtherID etc
1 test 2
2 test2 3

Table2:
TblID DocID OtherID
1 1 10
2 1 13
3 1 25

how do I join these two tables, such that I get all the otherID's for each DocID.
ie.,
DocID OtherID
1 2 and 10 and 13 and 25
2 3

i am writing this query to display search results on a search page (with keyword search) and so, if I display the result in more than one row, then the user might think that there is more than document...whereas the case is that there is only one document with more than one other ID's.

is there any way I can do this? display...more than 1otherID in the same row for the same DociD?
Currently, I am using a left outer join of table1 and table2.

An suggestions on how to do this?

View 6 Replies View Related

COMPLEX Sql Query PLEASE HELP!!

Oct 1, 2005

I cant get "order by" to work in this sql query..I use this query:
"SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn"and I want to add this some where to get 12 random records: "ORDER BY NewID()"I tried this: "SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn ORDER BY NewID()"" but get the error:"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"I canĀ“t figure out how I should write the query..Somebody have any ideas??/Radiwoi

View 2 Replies View Related

Complex Query

Apr 11, 2003

Hi,

I need a way to retrieve records from a table with a 30 min interval between the records.

For e.g., Lets say I have the following data in a table :-

userid hitdt
1 4/1/2003 10:00 AM
1 4/1/2003 10:15 AM
1 4/1/2003 10:31 AM
1 4/1/2003 11:10 AM
1 4/1/2003 11:30 AM
1 4/1/2003 11:41 AM

I need a query which would return me the following recordset :-

userId hitId
1 4/1/2003 10:00 AM
1 4/1/2003 10:31 AM
1 4/1/2003 11:10 AM
1 4/1/2003 11:41 AM

Is there a way to do this without using a cursor ?

Thanks

View 7 Replies View Related

Complex Query

Jun 10, 2005

Code:

ID GroupID User
1 101 Tom
2 101 Mark
3 101 Clark
4 102 Tom
5 102 Mark
6 103 Tom
7 103 Clark
8 104 Tom
9 104 Clark
10 105 Tom
11 105 Bred



the users of Group 101 are Tom,Mark,Clark
the users of Group 102 are Tom,Mark
the users of Group 103 are Tom,Clark
the users of Group 104 are Tom,Clark
the users of Group 105 are Tom,Bred

I want to show Tom that

Both You and Clark are together in 3 groups
Both You and Mark are together in 2 groups
Both You and Bred are together in 1 group

View 5 Replies View Related

Complex Query Help

Jan 27, 2007

I might have to redesign the tables for this, but I'll ask anyway. I have a table with the following fields:

Email - VarChar
Seminar - Int
PeckingOrder - Int.

As I add addresses to the table, each one has a Seminar, and then each Seminar has a Pecking Order Value. If an email address shows up for more than one seminar, it can have multiple records. IE:

email1 - 1523 - 424
email1 - 1526 - 124
email1 - 1524 - 235
email2 - 1526 - 124
email2 - 1524 - 235

for address, seminar, and pecking order would be sample entries into the table. Give or Take 1000 records in the table at any given time. What I want to pull out is:

Distinct Email Addresses
For each Email Address - The Max(PeckingOrder)
And the Seminar that's associated with Max(PeckingOrder)

For the sample data set above, I'd want to see these two records returned by the query:

email1 - 1523 - 424
email2 - 1524 - 235

I can't seem to get the Having / Where clause right to pull those two records properly. Anyone have any suggestions?

Thanks,
--Daniel

Edited - Didn't realize the BB removed email addresses.

View 1 Replies View Related

Need Help With A Somewhat Complex Query

Oct 10, 2007

Code:


SELECT
goto_last_name as 'Manager',
advisor_name,
advisor_ao_number as 'AO',
COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors',
SUM(Weekly_TOS_GDC) as 'TOS GDC'
FROM weekly_condensed_tb
WHERE advisor_platform_number = 1
AND report_date BETWEEN CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28
AND CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME)
GROUP BY goto_last_name,advisor_ao_number,advisor_name
HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1



In the query above i select a range from the date - 28 days and do a sum on the TOS GDC column. What i am trying to do is have another query where the range will be - 56 and maybe - 86 so i get the SUMS for that as well. I need to display this in SQL Reporting Services and i can only have one DataSet returned otherwise i will not be able to bind it to one table.

How can i go about this so i return one set of data for 3 different date ranges.

Thanks

View 2 Replies View Related

Complex Query In Sp

Jun 2, 2004

I'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
Background:
1. The innermost query (using the dynamic statements) will run fine on its own.
2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.

Here's the code:

CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
@column nvarchar(100),
@value smallint
AS
DECLARE @SelectString nvarchar(500)

SET @SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
'FROM dbo.v_maindata ' +
'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @column + ' = @value ' +
'ORDER BY dateOccured DESC) DERIVEDTBL ' +
'GROUP BY thedate ' +
'ORDER BY thedate DESC) DERIVEDTBL'

EXEC sp_executesql @SelectString, N'@column nvarchar(100), @value smallint', @column, @value
GO

-------------------------------------------------------------------------
Owen Eustice
MNC-I Webmaster
Victory Base South

View 5 Replies View Related

Need Help On This Complex Query

Aug 11, 2004

Result of the select from dbUsers is

db_name User Group
DB1 U1 db_datareader
DB1 U1 db_datawriter
DB1 U1 db_ddladmin

Db2 U1 db_dataReader
Db2 U1 db_datawriter

Db2 U2 db_dataReader
Db2 U2 db_datawriter

I somehow want it in a way so that the Db_name and USer_name are not repeated .

IS it possible ?

thanks

View 3 Replies View Related

Complex Query - Need Help

Oct 29, 2004

Hi,

I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ..........., Sum of Amount for December, Total for All months, Average for all months.

I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?

Thanks!

View 12 Replies View Related

Complex Query

Feb 23, 2005

Hi,
I want to create a query with which I must compare the production demands with the production results. The production demands can be get by the join of two tables. The production results can be get from an aggregate of 4 tables. The connection of these two objects rely on two fields that exist in both two objects. In order to show all the production demands I must left join the two fields from the demands object to the two fields exist in the aggegate production object. In MsAccess the only way to do it is to create 2 queries one for the demands and one aggregate for the production and in a third query create two left joins from the demands query to the production query and get the right results. How can I do it with MSSQL Server with a query??

Best Regards,
Manolis

View 6 Replies View Related

DB Query (complex Maybe)

Mar 6, 2007

I have database like below.

BLOG
--POSTER_ID
--POSTED_BY (a FK refer to USERID)
--UPDATED_BY (a FK refer to USERID)


USER
--USER_ID
--NAME

I want to retrieve the info for all posts that posted and updated by different users, also the query returns both user names for every posted and updated action.

I can write two queries to do that

select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.POSTED_BY = U.USER_ID

select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.UPDATED_BY = U.USER_ID

Then write some code to combine the result for the posted and updated user name. But this is not efficient.

I am wondering how can I write one query to archeive that?

Thanks.

View 9 Replies View Related

Complex Query

May 22, 2007

Hi Guys Hope you can help with this - it's certainly got me scratching my head. I'm Querying a Call Centre Database I have a Table of Call Data with a Start Time & End Time of the Call. (CallID,Started,Ended)10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.0001003855355 2007-04-01 00:01:24.000 2007-04-01 00:01:24.00010942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.00010942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.00010942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.00010942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.00010942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.00010942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000I'm trying to produce Stats that tell me how many Calls were live in any one given minute. Ultimately I will be producing a Line Graph of No of Calls Connected grouped by Minute. I've gone as far as creating a temp table with every minute in a month with the following query maybe to join to but not sure if this will help me. WHILE(@cnt <= 43200)BEGINSELECT @MaxDate =DATEADD(mi,1,MAX(DTBlock))FROM AprilMinutesINSERTINTO AprilMinutes VALUES(@MaxDate,NULL)SET @cnt = @Cnt +1 ENDWhich produces a nifty little table with 01/04/2007 00:09:0001/04/2007 00:10:0001/04/2007 00:11:0001/04/2007 00:12:0001/04/2007 00:13:0001/04/2007 00:14:0001/04/2007 00:15:0001/04/2007 00:16:00 If one individual Call Spans 2 minutes I'll count it as 1 in the first minute & 1 in the second minute. Overall I'm trying analyze how many telephone lines we need Any Help much, much appreciated Thanks GW

View 14 Replies View Related







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