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
ADVERTISEMENT
Dec 13, 2006
Greetings SSIS friends,
I have the following problem in SSIS and it's driving me nuts!!!!!
My situation is as follows :
Data Source 1 & Data Source 2are joined using a merge join. This bit works fine.
The output of the above join is then joined to a third data source but this time, I only get 63 rows coming through instead of 77097 even though the join key in the second merge join component is the same as the first one!!!
I thought I was going mad so I decided to see if the same problem occurs if I was to do this with T-SQL. I created 3 temporary tables for each of my data sources.
I did an inner join between tmpTable_Source1 and tmpTable_Source2, I then stored the result in tempTable4
The final inner join was performed between tempTable4 and tempTable3 and the result produced 77097 and not 63 rows.
What the hell is SSIS playing at?! The merge keys I used in T-SQL is the same one I used in my SSIS package!!!!
View 4 Replies
View Related
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
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
View Related
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
Oct 30, 2005
Hi,
Pbbly most of you know this:
Intra-query parallelism caused your server command (process ID #57) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
I've seen MS KnowledgeBase for that (http://support.microsoft.com/default.aspx?scid=kb;EN-US;837983)
But I have some probs with it:
1. I don't have permissions for this kind of queries
"USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO"
2. I don't know what is an "intra-query parallelism"...
I'm attaching the query I use. The query runs OK for some parameters but gets this error msg on other parameters.
I'm using a single CPU on 2003 STD Edition.
Query:
create table #mytemp_table
(client_id int not null, commission_value int null)
insert into #mytemp_table (client_id, commission_value)
select client_id, sum(transfer_value) from users_transfers where transfer_type in (1,3) and is_paid = 1 group by client_id
select
u.name [Vendor],
u.email,
sum(cost) as Payment,
cmpd.vendor_id,
ua.name [Agent],
vd.join_date,
commission_value [paid],
allow_sign
from
reports ra
left outer join codes ctd on ra.affiliation_code_show = ctd.affiliation_code_show
left outer join traffic cmp on cmp.campaign_id = ctd.campaign_id
left outer join traffic_details cmpd on cmp.campaign_id = cmpd.campaign_id
left outer join userssb u on cmpd.vendor_id = u.client_id
left outer join userssb ua on cmpd.agent_id = ua.client_id
left outer join users_details vd on u.client_id = vd.client_id
left outer join #mytemp_table com_paid on com_paid.client_id = u.client_id
left outer join allow_db asd on asd.client_id = u.client_id
where
[conditions]
group by
[all the group fields]
having sum(cost) > 2999
order by u.name
drop table #mytemp_table
View 1 Replies
View Related
Nov 27, 2001
Here is the working query, shortened for the example:
SELECT a.SalesMan,a.CustomerName,b.Entry_Comments,b.Entry _Date
FROM MyMaster a LEFT OUTER JOIN MyDetail b ON a.id = b.id WHERE blah ORDER
BY blah
This works fine and I get all my detail reocrds for each master. Now I need
to be able to select only a single most recent b.Entry_Date. How can I do this, Ive played with MAX but cannot get the sytax correct?
Thanks,Adrian
View 4 Replies
View Related
Feb 28, 2008
i have select query....
select distinct duo.messageid_ from [detected unique opens] duo
left outer join (select MailingID, count(*) as cnt
from lyrCompletedRecips
where mailingid = duo.messageid_
and FinalAttempt is not null
AND FinalAttempt >= '1945-09-10 00:00:00'
group by MailingID) ad
on ad.mailingid = duo.messageid_
i m getting error like:
The column prefix 'duo' does not match with a table name or alias name used in the query.
can anyone tell me what's the reason?
thanks.
View 3 Replies
View Related
Sep 7, 2003
table_a has patient_id, tran_id and other fields a,b,c
table_b has patient_id,tran_id, key_id
and other fileds d,e,f
table_a patien_id + tran_id is unique
table_b patient_id + tran_id is not unique, could be duplicated.
I have to create a query which will retrieve fields from table a a,b,c and fields d,e from table b where
table a. patient_id + tran_id =
tableb.patient_id + tran_id and table_b.key_id is the min key_id for that patient_id + tran_id.
I should retrieve just one record.
How would I be able to do that?
Please help!!
Thanks
View 2 Replies
View Related
Aug 26, 2005
Hey all,
I have 3 tables: 1 table with Module information (1 quiz-like part of an Exam), 1 table with the relationships between Modules and Exams (ratios) and 1 table with User information, that contains the Exam number, Module number, and score. I am trying to get a full list of modules' names for 1 exam, and the user's score for those modules they taken. All this would pertain to 1 single Exam. I am only getting records returned for those modules that the user has taken; I want the full list with NULL values for the scores of modules the user has not taken. Hope that made sense, here is my attempt:
Code:
SELECT ExamModules.Name, ExamUsers.Score
FROM ExamToModule LEFT OUTER JOIN
ExamModules ON ExamToModule.ModuleID = ExamModules.ID LEFT OUTER JOIN
ExamUsers ON ExamToModule.ModuleID = ExamUsers.MID AND ExamToModule.ExamID = ExamUsers.EID
WHERE (ExamToModule.ExamID = 1) AND (ExamUsers.UserName = 'dizzle')
In this case the user is Dizzle and the Exam's ID (primary key) is 1. I've changed LEFT to RIGHT and FULL, they all return the same few records. Help?
View 4 Replies
View Related
Jun 27, 2007
I need a little help and I hope I can write a clear description of the problem. I have to tables, customers and custrate. The customer table simply has a custid of other misc info. The custrate contains an entry for each rate for any particular customer (1 customer to many rates). However the rates are suppossed to come in pairs. For example, if a customer has a rate id of 120 then the customer should also have a corresponding custrate record with a rate_id of 200. If the customer has a rate_id of 130, then the customer should have a corresponding custrate record containing rate_id 300, and so on and so forth. How do I find the customer id with are missing the corresponding rate? Here's an example or what I'm trying to explain.customerid | name1 | fred 2 | tom3 | eric 4 | fredcustratecust_id | rate_id1 | 1201 | 2002 | 1302 | 3003 | 1204 | 130I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!
View 8 Replies
View Related
Oct 16, 2006
This is a very complex query and i have tried everything with no sucess.
I'm having 3 Tables,
Orders which is having fields like CustomerID, ManufactureID, MerchID :- These all ID Fields (around 6) are foreign key of Contacts and Address Tables.
Address Table is having AddrID(Primary Key), ShortName
Contacts is having ContID (Primary Key), AddrID (Foreign Key), PersonName
I want to retreieve info in a single query which can return a single row with following columns :-
OrderNo, CustomerName, ManufactureName, MerchName etc. (all 6 columns) by joining these 3 tables
can anybody help in this.
View 5 Replies
View Related
Apr 23, 2008
If i want to do another join with this select join query, how would I do this I tried many different way, but keep failing or not getting the right answer.
I want to Inner Join tbSucess with the query below. Means that only return the result if the history does exists in tbSucess.
And
In a seperate select query.
I, I want to get only the records which are not found in tbSuccess . So how would I join those two.
Select r.History, LastName, FirstName, Convert(varchar(10), Pay, 101) as Pay,
from #rpt r inner join #history h on r.History = h.History
order by r.History, pay
View 8 Replies
View Related
Feb 1, 2008
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
View 2 Replies
View Related
Mar 19, 2008
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email
FROM #temp_pregovb, [Survey Return]
INNER JOIN SelectedInvited ON
[Survey Return].SelectedID = SelectedInvited.SelectedID
--*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact]
ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID
WHERE
[panelist contact].email=#temp_pregovb.email
AND SelectedInvited.CellRef IN (
SELECT surveycell
FROm [Survey Cells]
WHERe SurveyRef='5')
View 3 Replies
View Related
Mar 3, 2008
Hi guys, I'm still new around here and still a noob for sql. Can you give me some example for some joins from the easy ones to the most complex and all of it kinds, I heard that there's a lot of different kind of join in sql. You can give me some link or maybe some code examples for me, I appreciate all kinds of help . Thanks for all your help. Regards.
View 3 Replies
View Related
Nov 3, 2004
I have a couple tables that I am exporting data from.
I have one table (call it TABLE1) that contains abbreviations instead of actual words.
For example instead of having the full word "New York City", the column would just contain "NYC".
In another table (call it TABLE2) it has all the abbreviations and the actual words.
So in TABLE2 one column name is FieldName, another Abbreviation and another is Value.
So on a web site, to display that actual name I do something like this
Code:
SELECT CityBoxlabel AS City
FROM TABLE1 INNER JOIN
TABLE 2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') = CityBox.[Value]
WHERE CityBox.FieldName = 'City'
This is working great except that some of these columns contain more than one abbreviation such as "NYC,WDC"
which would stand for "New York City" and "Washington DC"
The items that have more than one abbreviation are not being pulled across because my query is looking for an "NYC,WDC" in TABLE2 to INNER JOIN on but it won't be in there. But "NYC" and "WDC" by themselves are in there.
I tried messing with the INNER JOIN statement by saying something like
TABLE2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') IN CityBox.[Value] INNER JOIN
But it wasn't allowing that.
I can write a program to do this, but I would rather keep it in T-SQL.
Hope this wasn't too confusing!
Any help is greatly appreciated!!
View 5 Replies
View Related
Jul 20, 2005
hi thereanyone had an idea to join following 2 queries to 1????----- QUERY 1 ---------------------------------------------SELECT TMS_CaseF_2.Name AS TCDomain_0,TMS_CaseF_3.Name AS TCDomain_1,TMS.CaseF.Name AS TCFolder_2,TMS_CaseF_1.Name AS TCFolder_3,TMS.TestCase.Name AS TCName_4,TMS_TestCase_1.Name AS TCName_5,TMS.LogFolder.Name AS PlannedLogFolder_6,TMS.Log.Name AS PlannedLog_7,TMS.CaseResult.RecordedCaseName AS TCRecordedName_8,TMS.TestPlan.Name AS Plan_9FROM((((((((((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.Log)INNER JOIN TMS.TestCase ON TMS.CaseResult.TestCase =TMS.TestCase.UID)LEFT JOIN TMS.CaseF ON TMS.TestCase.Parent = TMS.CaseF.UID)LEFT JOIN TMS.TestCase AS TMS_TestCase_1 ON TMS.TestCase.Parent =TMS_TestCase_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_1 ON TMS_TestCase_1.Parent =TMS_CaseF_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_2 ON TMS_CaseF_1.Parent =TMS_CaseF_2.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_3 ON TMS.CaseF.Parent =TMS_CaseF_3.UID)INNER JOIN TMS.TestPlan ON TMS.TestCase.TestPlan = TMS.TestPlan.UID)WHERE (((TMS.LogFolder.Name) Like 'TR1%')AND ((TMS.Build.Name)='Planning_VD10A'))ORDER BY TMS.CaseF.Name,TMS_CaseF_1.Name,TMS.TestCase.Name,TMS_TestCase_1.Name;------------------------------------------------------------------ QUERY 2 ---------------------------------------------SELECT TMS.CaseResult.RecordedCaseNameFROM ((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.LogWHERE (((TMS.LogFolder.Name) Like 'VD%')AND ((TMS.Build.Name)='VD10A IT_APP'));
View 1 Replies
View Related
Apr 27, 2008
Table A
Registation_key discharge_dt
1001 7/15/07
1002 6/4/07
1003 7/30/071
Table B
REgistration_key ID end_dt value
1001 879 07/11/07 1
1001 888 07/15/07 2
1002 777 06/01/07 5
1002 778 06/01/07 2
1002 779 06/30/07 6
1003 234 07/31/07 3
1003 234 07/31/07 1
1003 235 08/01/07 7
I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B
option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID
option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID
option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id
so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.
so the output should look like
Table B
Registration_key ID end_dt discharge_dt value
1001 888 07/15/07 7/15/07 2
1002 778 06/01/07 6/4/07 2
1003 234 07/31/07 8/1/07 7
How can I do this conditional join ?
View 6 Replies
View Related
Jan 20, 2005
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
Thanks in advance
greg
View 8 Replies
View Related
Aug 12, 2012
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
[code]...
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
View 2 Replies
View Related
Mar 23, 2015
I have Two tables @master and @child
Master Table :
MasterID EntryNumber BranchId IsstockIn
1 1 1 1
2 1 1 0
Child Table:
CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4
My Query:
Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)
My Result:
EntryQty
10
20
-5
-4
Expected Result:
10
20
View 6 Replies
View Related
Aug 25, 2005
Here is the situation
Table 1 : tbl_documents
docIDdocName
1aaa
2bbb
3ccc
Table 2 : tbl_Rating
ratIDratingdocID
131
251
321
432
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0
NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”
PLs help me ?
Thx
View 3 Replies
View Related
Feb 7, 2006
I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:
Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey
-- Brian
View 3 Replies
View Related
Sep 11, 2007
Hi,
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30
After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
View 5 Replies
View Related
May 13, 2015
I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn.Â
select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+
[Code] .....
I think I probable need to include a group by but can't figure out the correct syntax.
View 12 Replies
View Related
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
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
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
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
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
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
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
