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
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 RelatedThe 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]...
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
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
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.
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
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..
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
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
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.
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
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.
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"
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
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.
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 RelatedHi 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 RelatedI'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.
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
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!
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
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
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!
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?
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."
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 RelatedHi 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 RelatedHello 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
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
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?
I've been trying to get this to work right.
The db table has 3 fields: id, vwr, and reqType.
I need all DISTINCT vwr's from the table. (vwr's can repeat)
This gives me all rows, not distinct...
select distinct d.id, d.vwr, d.reqType from tblVWR AS dinner join tblVWR ton d.vwr = t.vwr
Any suggestions?
Thanks,
Zath
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