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
ADVERTISEMENT
Feb 14, 2014
I have four tables (all inner joined) and currently they give me the results i need. However, my boss has now asked me to return all associated accounts as well.
I am currently pulling data from the four tables to make up my results table, and the returned results are based on the loan types in my loans tables having a loan type of '1A'
So if the loan type is 1A I get a result.
However, Mr Smith (for example) may have three loans but only one of them is type '1A'. The other two might be type '5H' and '2'.
What I need to be able to do is return all the associated accounts of any customer that has a type '1A' loan.
This is my code:
Select c.customernumber, l.accountsuffix, c.forename, c.surname, lt.code, l.balance, j.journeynumber from customers c
inner join loanagreements l on c.customerid = l.customerid
inner join loantypes lt on l.loantypeid = lt.loantypeid
inner join journeys j on c.journeyid = j.journeyid
Where j.journeynumber = 93
and lt.code = '1a'
and l.balance >0
View 6 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
Mar 11, 2008
I have two tables - products and productpropertyvalue
I need to select multiple fields from the productpropertyvalue as it corresponds to the product id. The script I am using is
select a.id, a.productname, a.siteprice,
b.propertyvalue
from product a, productpropertyvalue b
where a.id = b.productid and propertyid=590
This allows me to extract only 1 propertyid. I need to make it add 3 other columns for propertyid=589, 617, 615
Any help solving this problem would be appreciated - thanx!
Ron
View 14 Replies
View Related
Jan 18, 2005
I've searched high and low for info on how to do this... If anyone has an idea, I'd really appreciate it.
I have three tables: PEOPLE, PROJECTS, COMMENTS. I want users from the PEOPLE table to retrieve a list of PROJECTS and be able to add/edit COMMENTS on those projects.
The tables look like:
PEOPLE
people_id (primary key)
first_name
last_name
PROJECTS
projects_id (primary key)
project_title
project_summary
COMMENTS
comments_id (primary key)
projects_id (foreign key)
people_id (foreign key)
comment_detail
I'd like to be able to output something like what I have below, but I don't know how to loop over the comments/people within the select statement for the projects.
DESIRED OUTPUT
Project #1
Comment #1 by Person #1
Comment #2 by Person #3
Comment #3 by Person #8
Project #2
Comment #1 by Person #2
Comment #2 by Person #3
Comment #3 by Person #6
Etc...
I've done it before by just listing the projects and then providing a detail page with all the comments, but it's much less confusing to access all the comments from the same page, grouped by project.
Thanks in advance.
View 1 Replies
View Related
Nov 5, 2014
I have a query which returns all parts and labour lines for a particular work order. It returns all parts lines seperately, but the labour lines are repeated for each row. What I want to accomplish for a given work order, is a list of all the parts lines, followed underneath by a list of all labour lines.This is the code from the report:
select
h.worknumber,
--- Select parts lines and charges
wp.description as [charges desc],
case
when wp.charge_to_cust = 1 then wp.sale_price
[code]...
For this example what I'd like to see is 5 lines here - the labour description and charge under charges description, unit price, qty and est_parts_sale etc, and of course, there could be more than 1 labour line.
View 2 Replies
View Related
May 2, 2015
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[code].....
View 0 Replies
View Related
Feb 13, 2008
I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
TIA.
View 7 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
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
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
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
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
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
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
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
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
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
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
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Aug 3, 2006
Help.
I have a table with a composite key (two primary keys) that is a foreign key to another table. I need to select all records in the primary key table where there are no matches in the foreign key table... I have no idea how to do this..
Here is how to join the tables:
Select A.Key1, A.Key2 From PrimaryKeyTable A, ForiegnKeyTable B Where A.Key1 = B.Key1 and A.Key2 = B.Key2
I need to query all records that do not join, so basically the inverse of this query. How do I do this? Please let me know if this is in the wrong forum or you need any clarifcation.
Just FYI, on a single Primary key column I would just simply do the following:
Select A.Key From PrimaryTable A, ForiegnTable B Where A.Key not in ( Select A.Key From PrimaryTable A, ForignTable B Where A.Key = B.Key)
but how to with a composite key?
View 4 Replies
View Related
Jun 6, 2006
Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.name
View 1 Replies
View Related
May 15, 2002
Hi all -
Heres the deal,
I have six tables that are loosly related,
four in one join, two in the other....
in the first join there is a column called commentID -
and is based on a join of the four tables, the results of the
query is based on a where clause
and this needs to be tied into the second set of joined
tables (they are joined on a commentID themselves)
I have both the queries and joins working -
The primary query (4 joined tables) needs to do a lookup on the second (2 joined tables) and add its selected value, into the final result set....
thanks a million for the help!!
take care
tony
View 1 Replies
View Related
Dec 20, 2006
How do I delete from a joined table....
I want to delete all the records from transaction AND order table where status is > 4
sumfing like:
DELETE ORDERS.*, TRANSACTIONS.*
from ORDERS
INNER JOIN TRANSACTIONS
where ORDERS.order_no = TRANSACTIONS.order_no
and status > 4
but doesnt seem 2 like dat???
THANKS :)
View 5 Replies
View Related
Aug 15, 2014
I create a view with 2 joined tables:
with cte as (
select
DataID,
Name,
SubType,
FileType,
MimeType,
VersionID,
Version
from dtree A1, dversdata A2
where A1.dataid=A2.Docid And A1.Subtype='144'
AND
A2.mimetype='application/news-message-id'
update cte set MimeType = 'application/x-outlook-msg', Subtype=749
what I want to do is to update two columns. Both are from different tables and I get an error..
View 1 Replies
View Related
Nov 9, 2005
I have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.
Now I want to the ability to add comments to some (probably less than 1%) of those lines.
The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.
Any thoughts, words of warning?
Thanks
Mark
View 17 Replies
View Related
Feb 1, 2008
I posted Wednesday thinking a SELECT Distinct would solve my problem but it didn't. I have a stored procedure that is used to grab data from 4 tables that I need to join.
The 1st table (Application) holds a job applicant's name and some other data
The 2nd table (Jobs) holds the Job name and test type
The 3rd table (Locations) holds the locations
Then there is a foreign key many to many table (Application_Locations) that holds the applicants UserID and a LocationID. This table may have multiple rows for the same applicant with different locations in each row.
When the procedure is ran I want all the data that I am requesting from the Application table, and all the data that I am requesting from the Jobs table but only the 1st returned result of the Join on the Locations and Application_Locations table. What do I need to do to correct this so that I only display 1 row for each UserID no matter how many locations thay may have applied to. (You will notice that there are some IF statements so only the 2nd and 4th queries in the sproc are the ones that apply )
Here is the SPROC that is currently in place but is displaying a row for each location.
CREATE PROCEDURE sp_AdminListApplicants
@LocationID int,
@FolderID smallint,
@JobID int,
@SortOrder char(1)
AS
IF @JobID <> 9999
BEGIN
IF @LocationID <> 9999
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
AL.LocationID= @LocationID
AND A.FolderID= @FolderID
AND A.JobID = @JobID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
ELSE
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
A.FolderID= @FolderID
AND A.JobID = @JobID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
END
ELSE
BEGIN
IF @LocationID <> 9999
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
AL.LocationID= @LocationID
AND A.FolderID= @FolderID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
ELSE
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
A.FolderID= @FolderID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
END
GO
Miranda
View 5 Replies
View Related
Jul 20, 2005
Hi all,Currently our product has a setup that stores information abouttransactions in a transaction table. Additionally, certain transactionspertain to specific people, and extra information is stored in anothertable. So for good or ill, things look like this right now:create table TransactionHistory (TrnID int identity (1,1),TrnDT datetime,--other information about a basic transaction goes here.--All transactions have this infoPrimary Key Clustered (TrnID))Create Index TrnDTIndex on TransactionHistory(TrnDT)create table PersonTransactionHistory (TrnID int,PersonID int,--extended data pertaining only to "person" transactions goes--here. only Person transactions have thisPrimary Key Clustered(TrnID),Foreign Key (TrnID) references TransactionHistory (TrnID))Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)A query about a group of people over a certain date range might fetchinformation like so:select * from TransactionHistory THinner join PersonTransactionHistory PTHon TH.TrnID = PTH.TrnIDwhere PTH.PersonID in some criteriaand TH.TrnDT between some date and some dateIn my experience, this poses a real problem when trying to run queriesthat uses both date and personID criteria. If my guesses are correct thisis because SQL is forced to do one of two things:1 - Use TrnPersonIDIndex to find all transactions which match the personcriteria, then for each do a lookup in the PersonTransactionHistory tofetch the TrnID and subsequently do a lookup of the TrnID in the clusteredindex of the TransactionHistory Table, and finally determine if a giventransaction also matches the date time criteria.2 - Use TrnDTIndex to final all transaction matching the date criteria,and then perform lookups similar to the above, except for personID insteadof datetime.Compounding this is my suspicion (based on performance comparison of whenI specify which indexes to use in the query vs when I let SQL Serverdecide itself) that SQL sometimes chooses a very non optimal course. (Ofcourse, sometimes it chooses a better course than me - the point is I wantit to always be able to pick a good enough course such that I don't haveto bother specifying). Perhaps the table layout is making it difficult forSQL Server to find a good query plan in all cases.Basically I'm trying to determine ways to improve our table design here tomake reporting easier, as this gets painful when running report forlarge groups of people during large date ranges. I see a few options basedon my above hypothesis, and am looking for comments and/or corrections.1 - Add the TrnDT column to the PersonTransactionHistory Table aswell. Then create a foreign key relationship of PersonTransactionHistory(TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and createindexes on PersonTransactionHistory with (TrnDT, PersonID) and(PersonID, TrnDT). This seems like it would let SQL Server makemuch more efficient execution plans. However, I am unsure if SQL servercan leverage the FK on TrnDT to use those new indexes if I give it a querylike:select * from TransactionHistory THinner join PersonTransactionHistory PTHon TH.TrnID = PTH.TrnIDwhere PTH.PersonID in some criteriaand TH.TrnDT between some date and some dateThe trick being that SQL server would know that it can use PTH.TrnDT andTH.TrnDT interchangably because of the foreign key (this would support allthe preexisting existing queries that explicitly named TH.TrnDT - any thatdidn't explicitly specify the table would now have ambigious columnnames...)2 - Just coalesce the two tables into one. The original intent was to savespace by not requiring extra columns about Persons for all rows, many ofwhich did not have anything to do with a particular person (for instance acontact point going active). In my experience with our product, the enduser's decisions about archiving and purging have a much bigger impactthan this, so in my opinion efficient querying is more important thanspace. However I'm not sure if this is an elegant solution either. It alsomight require more changes to existing code, although the use of viewsmight help.We also run reports based on other criteria (columns I replaced withcomments above) but none of them are as problematic as the situationabove. However, it seems that if I can understand the best way to solvethis, I will be able to leverage that approach if other types of reportsbecome problematic.Any opinions would be greatly appreciated. Also any references to goodsources regarding table and index design would be helpful as well (onlineor offline references...)thanks,Dave
View 3 Replies
View Related
Apr 3, 2008
Two days of downtime does not make a happy DBA.
In our frustration we dropped database "Search" completely as restoring it from backup was giving us other authorization errors. Fortunately we could rebuild the searches on the fly.
So now our service broker activation procedure tells us:
04/03/2008 17:49:51,spid55s,Unknown,The activated proc [dbo].[MessageProcessor_NameChange] running on queue AMG_Search.dbo.NameChangeQueue output the following: 'The database owner SID recorded in the master database differs from the database owner SID recorded in database 'Search'. You should correct this situation by resetting the owner of database 'Search' using the ALTER AUTHORIZATION statement.'
My username created the database. I am dbo. So what exactly am I supposed to change the owner to?
I try this:
ALTER AUTHORIZATION ON DATABASE::[Search] TO MySelf
Where MySelf is my username. And I am told Msg 15110, Level 16, State 1, "The proposed new database owner is already a user or aliased in the database" So what the heck we'll make SA the owner.
And that works.
I DON'T GET THIS PEOPLE. I created the database I am not allowed to own it anymore? We formatted the server. We reinstalled SQL server from scratch. We recreated all the usernames. Was our mistake restoring the old data from backup? Was I supposed to re-run the DDL and key all the data back in again? Is that the reccomended restore procedure? The users will be thrilled to hear this :-)
My days as an Oracle DBA were nothing like this. Security was just not this troublesome...
So if someone older, wiser, and with more experience would explain the above error message and how it occurs I would appreciate it, because something tells me I'm going to be stuck in this place again...
View 7 Replies
View Related
