SQL Looping, Join, And Max Select
Nov 12, 2007
I'm having trouble finding the correct way to proceed. My object is to have a selection set of the most recent log entry for each user. I want to display this info in an ASP.Net grid. I have a user table related to a datetime stamped log table. I have the stored proceedure that finds the latest log for a specific user, but I'm having a problem constructing a statement that will produce records for all users.
This is (basically) what I use to get a single User's data.
Code Block
SELECT First, Last FROM UserDetails INNER JOIN UserStatusLog ON UserDetails.UserID = UserStatusLog.UserID WHERE DateTimeStamp IN
(SELECT max(DateTimeStamp) FROM serStatusLog WHERE UserID = @UserID)
I'm using SQL Server 2005 Express and somewhat of a newbie to SQL.
View 10 Replies
ADVERTISEMENT
Jan 24, 2008
I am trying to figure out how to do looping in SQL.
I have table like below:
ID System Material
1 Floor Wood
1 Floor Carpet
1 Wall Dry wall
1 Wall Paper
2 Floor Wood
2 Floor Carpet
I 'd like to end up with results like:
ID System Material
1 Floor Wood, Carpet
1 Wall Dry Wall, Paper
2 Floor Wood, Carpet
Any suggestions?
Thanks,
View 1 Replies
View Related
Aug 9, 2013
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
View 4 Replies
View Related
Aug 20, 2015
The select command below will output one patient’s information in 1 row:
Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,
code]...
View 6 Replies
View Related
Mar 28, 2008
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
View 5 Replies
View Related
Oct 4, 2000
What is the difference from performance point of view, when you select from 3 different tables to show fieldnames across the 3 tables based on one common key, OR using Join between the 3 tables.
Thanks
View 1 Replies
View Related
Jun 14, 2008
I have three tables.
Quote
QuoteID, QuoteNumber,
Transportation
TransportationID, QuoteID, Item, Description, Cost
OptionalCharges
OptionalChargesID, QuoteID, Item, Description, Cost
What I want to do is SUM(Cost) for the Transportation and the Optional Charges table. If I do a normal INNER JOIN, with the SUM for Cost on the table Transportation and Optional Charges, it will SUM each twice. For example if Transportation has three rows with a cost of 10, 20, 30, it will SUM a total of 120 instead of 60.
So I came up with:
Select
Quote.QuoteID, QuoteDate, t.TransportationTotalCost
FROM
Quote.Quote
INNER JOIN
(
SELECT
QuoteID, SUM(COST) AS TransportationTotalCost
FROM
Quote.Transportation
GROUP BY
QuoteID
) t
on Quote.QuoteID = t.QuoteID
WHERE Quote.QuoteID = 135
Which gives me the total for the Transportation Table, but how do I go about adding in the Optional Charges Table?
Thanks,
marly
View 2 Replies
View Related
Jun 22, 2008
Hi,
My SQL is a little rusty, and I need a little help for a client that I'm helping.
I'm simply trying to do create the following output:
HEADINGS: Sale Order | Customer Number | Customer Name | # of Pallets | (etc)
DATA: 456188 | 12355890 | Acme Customer | 4 | other stuff I have figured out that is irrelevant
This data comes from 3 tables:
1) SOE_HEADER
SALE_ORDER_NO | CUSTOMER_NO | (etc)
456188 | 12355890
2) CUST_NAME
CUSTOMER_NO | CUSTOMER_NAME | (etc)
456188 | Acme Customer
3) PALLETS_SALES_ORD
SALE_ORDER_NO | PALLET_ID | (etc)
456188 | 12345
456188 | 67890
456188 | 13579
I have 2 queries that independently pull the right data. 1 query joins the customer info to pull in the customer name, and the 2nd query calculates the # of pallets per sales order.
BUT I CAN'T GET THEM TO WORK WHEN I DO A JOIN!!! Can someone please help me? Here are the queries as I currently have them. This returns zero rows, but when I run my queries independently, the both return multiple rows with the correct data. PLEASE HELP ME!
SELECT
SOE.INSIDE_ROUTE,
SOE.SALESMAN_NO,
SOE.SALE_ORDER_NO,
CUST.CUST_NAME,
SOE.CUSTOMER_NO,
(SOE.SALES_AMT/100) AS SALES,
(SOE.COST_GOODS_SOLD/100) AS COGS,
(SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN,
COUNT(PALLET.SALE_ORDER_NO) AS NumOccurrences
FROM
SOE_HEADER SOE,
CUST_NAME CUST,
PALLET_SALES_ORD PALLET
WHERE
SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO
AND SOE.CUSTOMER_NO = CUST.CUSTOMER_NO
GROUP BY
PALLET_SALES_ORD.SALE_ORDER_NO
HAVING (COUNT(PALLET_SALES_ORD.SALE_ORDER_NO) > 1 )
THANKS. This will be a big help for the work I need to get done tomorrow, and I can't take it anymore. I've tweaked this all weekend, to no avail.
View 17 Replies
View Related
Nov 28, 2006
Hello,
I'd appreciate any help with the following problem.
I'm trying to update a table using an inner self join.
I've a list of historical records with start dates, and I need to add end dates to the records, using the start date of the next record.
The table I'm using looks like this
CREATE TABLE [dbo].[IbesEstimateHist](
[IbesEstimateHistId] [int] IDENTITY(1,1) NOT NULL,
[IbesEstimateId] [int] NULL,
[EstimateDate] [datetime] NULL,
[EstimateEndDate] [datetime] NULL CONSTRAINT [DF_IbesEstimateHist_EstimateEndDate] DEFAULT ('9999-12-31 00:00.000'),
[Value] [decimal](13, 4) NULL,
CONSTRAINT [PK_IbesEstimateHist] PRIMARY KEY CLUSTERED
(
[IbesEstimateHistId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
and here's some example data
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value])
values(1,'2006-01-01','9999-12-31',100)
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value] )
values (1,'2006-02-01','9999-12-31',100)
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value])
values (1,'2006-03-01','9999-12-31',100)
These are three historical records for the same estimate, I want to set the end dates of the earlier records to the start date of the next record that was recieved.
This is the SQL that I've tried using but I can't seem to get it right
select esth1.IbesEstimateId, esth1.EstimateEndDate,min(next.estimatedate)
from IbesEstimateHist esth1
inner join
(
select esth2.EstimateDate as estimatedate, esth2.IbesEstimateId
from IbesEstimateHist esth2
) as next
on esth1.IbesEstimateId = next.IbesEstimateId
and esth1.EstimateDate < next.estimatedate
group by esth1.IbesEstimateId, esth1.EstimateEndDate
I'd be grateful for any help, thanks.
Sean_B
View 3 Replies
View Related
Jan 29, 2007
hi all,
i have a doubt regarding a select operation performed on two tables with join statement.
The table structure is as follows
table name- application
---------------------- ------------------------------------------
appid appname version
------------------------------------------------------------------
1 Test 10
2 Test 11
3 Sample 5
table name- app_users
-----------------------
app_users_id user_id appid version date_downloaded
------------------------------------------------------------
1 250 1 10 1/29/2007
Now i want a grid which should show data like this
AppName LastDownloadedVersion Date_Downloaded
----------------------------------------------------------
TestV11 10 1/30/2007
SampleV5 -- ------
Here the value for appname should be created by appname+'V'+latest version of that appname(eg. Application name- Test Ltest version- 11 thus appname becomes 'TestV11')
Could anybody help me to solve this problem..
Thanx in advance..
View 1 Replies
View Related
Aug 5, 2007
Hi:
I have a record that has location1, price1, location2, price2
How would I do an inner join or "how would I " get the name of the location?
location table -- locationid, locationname
producttable -- location1, location2 is the locationid in location table
View 1 Replies
View Related
Dec 26, 2006
My database with 300.000 records , uses 350 MB RAM when I send this query.
is there any way I can avoid this?
SELECT * FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS,(_NAME,ESTATEOTHERPROPERTIES),'FORSALE') as KEY_TBL
ON KEY_TBL.[KEY]= ADTBL._ID
Where _DELETIONSTATUS=0
View 1 Replies
View Related
May 30, 2007
I need to Update a table with information from another table. Below is my psuedo code - need help with the syntax needed for Sql2000 server.
JOIN tblStateLoc ON tblCompanies.LocationID = tblStateLoc.LocationIDUPDATE tblCompaniesSET tblCompanies.StoreType = tblStateLoc.StoreTypeWHERE tblCompanies.LocationID = tblStateLoc.LocationID
View 2 Replies
View Related
May 26, 2006
My environment:XP Home, VWD, SQLEXPRESS.A purely local setting, no network, no remote servers.
I try to do a JOIN query between tables in the membership ASPNETDB.mdf and one table in a self created 3L_Daten.mdf.
After dragging the tables into the Query Design window and connecting them VWD creates this query (here I added the control declaration):
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAspNetDB %>" SelectCommand="SELECT aspnet_Users.UserName, aspnet_Membership.Password, aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Roles.RoleName, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Age, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Sex, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Area FROM [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten INNER JOIN aspnet_Users ON [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.User_ID = aspnet_Users.UserId LEFT OUTER JOIN aspnet_Roles INNER JOIN aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId LEFT OUTER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId"></asp:SqlDataSource>
THIS WORKS, BUT:
As you can see the database 3L_Daten.mdf is inserted with its full path, which is not feasible for deployment reasons.
My question: How can I address both databases purely by their database names ? Both have been created within VWD and lie under App_Data.
(I tried almost everything, I practiced with the SQL Server 2005 Management Studio Express Edition, I tried linked servers, all without success).
Thank you for your consideration.
View 3 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 20, 2006
In my new job I have to administer an existing SQL-database with approx. 50 tables. In this database are no joins :confused: defined between the tables. We use a Visual Basic 6 application to create a GUI and within this VB6 app. there are several SELECT statements to retrieve the required data. In these SELECT statements are all the INNER and OUTER JOINS between the tables defined.
My question: is this a correct way to work with or is it better to create all the JOINs between the tables on the database itself? Or should I create different views and define the JOINs in there? My main concern is the speed to retrieve data and second the required time to administer this database.
View 3 Replies
View Related
Apr 26, 2006
Hi all. I'm selecting all customers and trying to count alll the orders where at least one item has the itemstatus of "SHIPPED" on their order. Each customer will have only one order. I'm trying to see if I can do this in one query. Is it possible?? Is it something like below?
SELECT customers.id,COUNT( orders.id) AS 'total',
from customers
LEFT JOIN orders ON customers.id=orders.id AND orders.itemstatus="SHIPPED"
View 2 Replies
View Related
Dec 22, 2014
how can i join these 2 queries to produce 1 result
Query 1:
select R.Name, T.Branchid, t.TradingDate,t. TransactionDate,
convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber,
t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt
from TransactionHeader T
join SalesRep R on
R.SalesRepid = T.SalesRepid
where T.SalesRepid is not null
Query 2 :
select C.TradingName,C.AccountNo
From Sale S
Join ClMast c on
C.Clientid = s.CustomerAccountID
The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno..Field Saleid is present in Transactionheader Table and Sale table
View 5 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
Jul 23, 2005
The query:SELECT BTbl.PKey, BTbl.ResultFROM BTbl INNER JOINATbl ON BTbl.PKey = ATbl.PKeyWHERE (ATbl.Status = 'DROPPED') AND (BTbl.Result <> 'RESOLVED')Returns no rows.If I do:SELECT BTbl.PKey, BTbl.ResultFROM BTbl INNER JOINATbl ON BTbl.PKey = ATbl.PKeyWHERE (ATbl.Status = 'DROPPED')Returns:PKeyResult125127RESOLVEDI want the first query to return the row with PKey: 125 because it'sresult field does not equal 'RESOLVED'Any ideas what I'm doing wrong?My tables:CREATE TABLE [dbo].[ATbl] ([PKey] [int] NOT NULL ,[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[BTbl] ([PKey] [int] NOT NULL ,[Result] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO
View 2 Replies
View Related
Jul 20, 2005
Hi everyoneHave a problem I would areally appreciate help with.I have 3 tables in a standard format for a Bookshop, egProductsCategoriesCategories_Productsthe latter allowing me to have products in multiple categories.Everthing works well except for one annoying little thing.When an individual product (which is in more than one topcategory) is addedto the Shopping Cart it displays twice, because in my select statement Ihave the Category listed. I realise I could remove the TopCategory from thestatement and that makes my DISTINCT work as I wanted, but Id prefer to havethe TopCategory as it saves me later having to another SQL query (Im alreadydoing one to allow me not to list category in the Statement .... but If Ican overcome this one ... then I can remove this as well).Here is my table structure (the necessary bits)productsidProduct int....categoriesidcategory intidParentCategory inttopcategory int...categories_productsidCatProd intidProduct intidCategoryWhen I run a query such asSELECT DISTINCT a.idProduct, a.description,a.descriptionLong,a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,c.topcategoryFROM products a, categories_products b, categories cWHERE active = -1 AND homePage = -1AND a.idProduct = b.idProductAND c.idcategory=b.idcategoryAND prodType = 1 ORDER BY a.idProduct DESCThis will return all products as expected, as well as any products which arein more than one TopCategory.Any ideas how to overcome this would be greatly appreciated.CheersCraig
View 14 Replies
View Related
Feb 14, 2008
Hi again,
I have this SQL (part of a stored procedure) where I do LEFT JOIN. SELECT callingPartyNumber, AlertingName, originalCalledPartyNumber, finalCalledPartyNumber,
dateTimeConnect,
dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
clientMatterCode
FROM CDR1.dbo.CallDetailRecord t1
LEFT JOIN CDR2.dbo.NumPlan t2 ON t1.callingPartyNumber=t2.DNorPattern
WHERE
(t1.callingPartyNumber LIKE ISNULL(@callingPartyNumber, t1.callingPartyNumber) + '%') AND
(t1.originalCalledPartyNumber LIKE ISNULL(@originalCalledPartyNumber, t1.originalCalledPartyNumber) + '%') AND
(t1.finalCalledPartyNumber LIKE ISNULL(@finalCalledPartyNumber, t1.finalCalledPartyNumber) + '%') AND
(t1.clientMatterCode LIKE ISNULL(@clientMatterCode, t1.clientMatterCode) + '%') AND
(@callerName is NULL OR t2.AlertingName LIKE '%' + @callerName + '%') AND
(t1.duration >= @theDuration) AND
((t1.datetimeConnect) >= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), t1.datetimeConnect)) AND
((t1.dateTimeDisconnect) <= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), t1.dateTimeDisconnect))
The problem is that if the t2 has more than one entry for the same DNorPattern, it pulls the record more than once. So say t1 has a callingPartyNumber = 1000. t2 has two records for this number. It will pull it more than once. How do I get the Unique value.
What I am trying to get is the AlertingName (name of the caller) field value from t2 based on DNorPattern (which is the phone number).
If this is not clear, please let me know.
Thanks,
Bullpit
View 24 Replies
View Related
Jul 11, 2005
I want to insert into a table the result of a select which contains a join. Is this possible in any way on mysql 3.23.58?
I tryed the following code (in both ansi 92 and non ansi 92 forms)
insert book_edition (ISBN, book_title, publisher, book_image, author_name)
select i.isbn, i.imageurl, i.author, i.title, i.publisher
from ImportDataUniqueISBN i
left outer join book_edition b
on i.isbn = b.ISBN
where b.ISBN is null
I get the following error:
INSERT TABLE 'book_edition' isn't allowed in FROM table list
If executed without the join statement it works well.
Tks!
View 5 Replies
View Related
Jul 31, 2007
I've got myself in a bit of a pickle and would appreciate some help. After hitting Google and blazing the SQL books online I find myself on the trusty devshed forum.
Here's the statement:
Code:
UPDATE enquiries SET enq_dbtype =
(SELECT d.prop_dbtype
FROM enquiries as a JOIN enquiryproperties as b ON a.enq_id = b.enq_id JOIN sqlcluster.prop_file_db.dbo.property as d ON b.prop_id = d.prop_id
WHERE enq_datetime BETWEEN '12/19/2006' AND '05/15/2007')
The issue:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
As you can see, the table JOINs in the SELECT mean that there are too many rows are returned. Any ideas how I can limit the number of rows returned? There can be multiple entries in the enquiryproperties table for each enquiries row. The SQL statement needs to be limited so only one row is returned for each enquiries entry.
GROUP BY and DISTINT won't help me.... any ideas.
THanks... Will - BuR
View 1 Replies
View Related
Apr 11, 2008
Hi
I have a tables like below
TblA
ID(unique)---SessionID(unique)----Ref
TblB
VisitID(unique)---SessionID(Multiple)----Page--Pdcode
My question : How Do I bring all rows from TblB and matching single row from TblA (I have more than one sessionID in TblB and only one Unique SessionID in TblA).
The Select query I was using is
SELECT PageViews.ID AS ID, PageDetailView.VisitID, PageViews.PageAccessed, PageDetailView.PageAccessed AS Expr1, PageViews.QueryString,
PageDetailView.QueryString AS Expr2, PageViews.Referer, PageViews.SessionID, PageDetailView.SessionID AS Expr3, PageDetailView.PdtID,
PageDetailView.Pcode, PageDetailView.CustID, PageDetailView.OrdTot, PageViews.[Date]
FROM PageViews RIGHT OUTER JOIN
PageDetailView ON PageViews.SessionID = PageDetailView.SessionID
ORDER BY PageViews.ID DESC
View 9 Replies
View Related
May 15, 2008
I have two seperate tables that I need to get the data into one select statement. Here is the two separate select statements that I have created.
Select AGEGROUP, [YEAR], SUM(CASES) as Cases FROM tbHosp a
LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID
LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID
LEFT JOIN tbSEX g on g.SEXID = a.SEXID
where a.AgeGroupID in (1,2,3)
and a.YEARID in (1,2,3)
Group BY AgeGroup, [YEAR]
ORDER BY AgeGroup, [YEAR]
Select AGEGROUP, [YEAR], SUM(POPULATION) as [Population] FROM tbPopulation a
LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID
LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID
LEFT JOIN tbSEX g on g.SEXID = a.SEXID
where a.AgeGroupID in (1,2,3)
and a.YEARID in (1,2,3)
Group BY AgeGroup, [YEAR]
ORDER BY AgeGroup, [YEAR]
Results - First Select with tbHosp
AgeGroup YEAR CASES
<1 2001 9
<1 2002 32
<1 2003 10
1-4 2001 13
1-4 2002 11
1-4 2003 23
5-9 2001 13
5-9 2002 14
5-9 2003 34
Second Select with tbPopulation
AgeGroup YEAR POPULATION
<1 2001 40686
<1 2002 39768
<1 2003 40438
1-4 2001 174346
1-4 2002 170191
1-4 2003 167223
5-9 2001 247071
5-9 2002 242548
5-9 2003 237816
What I am trying to do is get one result set with Population and Cases together like the following
AgeGroup YEAR CASES POPULATION
<1 2001 9 40686
<1 2002 32 39768
<1 2003 10 40438
1-4 2001 13 174346
1-4 2002 11 170191
1-4 2003 23 167223
5-9 2001 13 247071
5-9 2002 14 242548
5-9 2003 34 237816
Any help would be greatly appreciated.
Thanks!
View 12 Replies
View Related
Jul 26, 2013
I am currently working with 2 tables:
tbl_users: UserID,Username,ClientFK
tbl_clients: ClientID, ClientName
Now I want to get all Users with same Client but my parameter of my stored procedure is @Username.
This code works fine:
SELECT
UserID,
ClientFK,
WebLogin,
WebPassword,
WindowsUsername,
BasePriority,
IsAdmin,
DateCreated,
Enabled
FROM tbl_User
WHERE ClientFK = (SELECT [ClientFK] FROM tbl_User WHERE [WindowsUsername] = 'Livermorium')
But i don't want to have 2 selects and prefer a left join.
Is it possible to write a better select statement?
View 10 Replies
View Related
Mar 7, 2007
Will writes "I have an employees table, which gives all emp. ids. I have a second table, time_log with tasks each employee has logged:
empID date log_time duration etc
===================================
and a 3rd table - a pivot table with a single column named "i" containing consecutive integers from 0 - 1000
I need to know for each date in a series, e.g seven consecutive days, how much time each has logged. easy if everyone has logged a task for every day, but I need to include every day where they have not logged a task.
so, a cartesian join on all the dates in a series(produced using dateadd on p.i and the pivot table)
SELECT dateadd('d',p.i, #02/19/2007#), e.empID
FROM pivot1 p, employees e
WHERE i<no_dates
However I need to do a left join with time log where the date and employee ids are the same, and I have summed the durations for each date. The following query does this, but does not include dates and times where nothing has been logged.
SELECT empID, log_date, sum(duration)
FROM time_log
GROUP BY empEIN, log_date
GIVING, EVENTUALLY, ALL DATES AND EMPIDS AND THE TOTAL AMOUNT OF TIME THEY HAVE LOGGED FOR EACH DAY."
View 1 Replies
View Related
Jul 23, 2005
Which way of retrieving a record is more effecient?:Select tbl1.field1, tbl2.field1from table1 tbl1 inner join table2 tbl2on tbl1.id = tbl2.idwhere someid = somevalueand someid = somevalueorSelectfield1 = (Select field1 from tabl1 where someid = somevalue),field2 = (Select field2 from table2 where someid = somevalue)
View 3 Replies
View Related
Sep 6, 2005
Hi All,I'm confused by how to replace a SELECT statement in a SQL statementwith a specific value. The table I'm working on is a list of words (acolumn called "word") with an index int pointing to the sentence theycome from (a column called "regret"). I also have a table of stop words(called "GenericStopWords") that contains the words I do not want toconsider. That table has a single column called "word".I started off using a SQL function (called "GETALLWORDS") whichreturned the words of a string. This is how I used it:INSERT INTO WordsSELECT wrds.WORD, 1 FROMGETALLWORDS('I could be bounded in a nutshell and count myself a kingof infinite space', default) AS wrdsLEFT OUTER JOINGenericStopWordsON wrds.WORD = GenericStopWords.wordWHERE GenericStopWords.word IS NULLThis statement inserts every word in the sentence that is not a stopword. So, for example, ('bounded', 1) is added but ('could', 1) is notadded.I've now decided to move the function that breaks a string into wordsout of the SQL layer of my application and into the JavaScript whichruns under ASP on the web server. So now I want to call somethinganalogous to the above SQL statement, but word by word. What I'd likeis something like:INSERT INTO WordsSELECT 'bounded', 1 FROM ???LEFT OUTER JOINGenericStopWordsON 'bounded' = GenericStopWords.wordWHERE GenericStopWords.word IS NULLDoes that make sense? I want a SQL statement that will insert the tuple('bounded', 1) into the table Words if (and only if) 'bounded' does notappear in the table GenericStopWords. It's easy to say procedurally, Icannot see how to write it in a relational style in SQL.Thanks in advance for any help you can give.Cheers,Tim.
View 3 Replies
View Related
Jan 3, 2007
Hello All,
I have a question about a Select over 2 Tables,
with the Following Scenario (Not all Products (ARTICULOS) haves CARAC's on the CFG_CARAC_ARTICULOS table):
Picture of the tables here:
http://www.pci-baleares.com/pantallazoSql.jpg
We have per example 7 Slots (Motherboard, CPU, VGA Card, RAM, TOWER, etc...)
When we fill the Slot with a CPU-> Then we open the Slot for VGA CARD, we do the Followin Select:
SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO
Ok it brings up ALL Graphic Cards because they dont depends on CPU
Now we go to the Motherboard Slot
And we make the following Select to obtain the compatible Motherboards:
SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO
WHERE
((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E')) AND
((ID_CARAC = 1) AND (VALOR = '775'))
We check the motherboards if they support PCI-E (because we selected a Graphic card of that, and SOCKET 775 because the CPU)
But SQL return 0 Rows, if we do the following Select:
SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO
WHERE
((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E'))
OR
SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO
WHERE
((ID_CARAC = 1) AND (VALOR = '775'))
It return Rows, it happens just if the Where clause haves more as 1 specifications...
Any solution for it? It drives me crazy :D
Thanks and regards
Marc Hägele
View 3 Replies
View Related
Mar 28, 2006
Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.
declare @inv table (item varchar(50), supplierid int)
declare @supplier table (supplierid int, weight int)
set nocount on
insert into @inv values ('item1', 1)
insert into @inv values ('item1', 2)
insert into @inv values ('item2', 2)
insert into @inv values ('item2', 3)
insert into @supplier values(1, 30)
insert into @supplier values(2, 20)
insert into @supplier values(3, 10)
-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3
select item, ps2.supplierid from @supplier ps2 join
(select item, min(ps.weight)'weight'
from @inv inv join @supplier ps on inv.supplierid=ps.supplierid
group by item) iw on ps2.weight=iw.weight
Is there a better alternative to this?
Thanks in advance,
Mike
View 3 Replies
View Related
May 11, 2007
I think it is quite often when you need to view some records, which refer (by key) to data in other tables. For instance, a user belongs to a group but it is preferable to show group name in the user data rather than group id. The options are
1) LEFT OUTER JOIN:
SELECT users.id, groups.name FROM users LEFT OUTER JOIN groups ON users.[group] = groups.id
2) A Subselect:
SELECT id, [group] = (SELECT [name] FROM groups WHERE id = users.[group]) FROM users
Which is better and why?
View 2 Replies
View Related
