Error On [insert Into]...[select From]...[join]

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


ADVERTISEMENT

Left Join Insert Query Error

May 8, 2008

I have been working on this for a little bit and have gotten to this point. Below is the query in blue, the error in red. Now, from what I gather the error is telling me I can't insert a duplicate product code into tblProduct. Isn't that what the left join is exactly not doing, as in, the left join is inserting all records from Complete_records into tblProduct where the code is null(does not exist) in tblProduct? Or, is this an issue where the identity number, productID in tblProduct, isn't starting at the next number in turn?

Thanks.

Set Identity_Insert tblProduct on
DECLARE @MaxId int

SELECT @MaxID=MAX(productID)
FROM tblProduct

SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL

INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp

(236752 row(s) affected)
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.tblProduct' with unique index 'IX_tblProductt_productCode'.
The statement has been terminated.

View 18 Replies View Related

SELECT @@IDENTITY During INSERT STATEMENT Error

Mar 9, 2008

The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:


"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"

Compact 3.5 doesnt like the SELECT statement claiming that:

There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]


Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?

I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.

View 9 Replies View Related

SQL 2012 :: Select Permission Error On Insert Statement

Jul 28, 2015

Have run to a select permission error when attempting insert data to a table. received the following error

Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.

Few things to note
- There are no triggers depending on the table
- Permissions are granted at a roll level which is rolled down to the login
- The test environments have the same level of permission which works fine.

View 6 Replies View Related

Select Command - Left Join Versus Inner Join

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

SQL Server 2014 :: Error On Unique Index - Duplicate Key When Insert / Select

Aug 4, 2014

I have an issue where I am getting an error on an unique index.

I know why I am getting the error but not sure how to get around it.

The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.

But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.

It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.

How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.

CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')

[Code] .....

View 1 Replies View Related

Transact SQL :: Select From A Select Using Row Number With Left Join

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

INNER JOIN - INSERT

Aug 13, 2007

Hi,My GIS software has a tool to count the number of points within agrid.This is fine for small recordset, when you get into the tens thousandsitbecomes unfriendly.It must be possible (more efficent??) to do a select statement fromthe two tables and insert the result into a column??Table Property has thousands of records that fall within each recordof Table Ward.Expect the SQL would beSELECT [Property].BedRmNumber FROM [Ward].LAWHERE [Property].LA = [Ward].LASurely this would need a loop.Could anyone help???Thanksclive

View 5 Replies View Related

INSERT-SELECT Depending On The Select:ed Order

Aug 15, 2006

I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.

However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?

Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).

View 3 Replies View Related

Insert Into + Join Question

Aug 31, 2006

declare @errors table (

erroriD varchar (20),
errorMessage varchar (100))

insert @errors select '1', 'unable to resolve address'
----------------------------------------------------------------------------------------------------------------------------------------

declare @addressLookup table (

address varchar (100))

insert @addressLookup
select'water'union all
select'apple'
----------------------------------------------------------------------------------------------------------------------------------------

declare @exception table (

address varchar (100),
errorID varchar (100))
----------------------------------------------------------------------------------------------------------------------------------------

declare @address table (
addressName varchar (100)
)

insert @address
select 'route 50 st'union all
select 'pine heaven st'union all
select 'banana st' union all
select 'apple st' union all
select 'sand st'

----------------------------------------------------------------------------------------------------------------------------------------

insert @exception (address, errorID)
select substring(a.addressName, 1, charindex(' ', a.addressName)), e.errorID
from @address a, @errors e
where substring(a.addressName, 1, charindex(' ', a.addressName)) not in
(select address from @addresslookup al)
and e.errorID ='1'

select * from @exception
----------------------------------------------------------------------

If the addressName is not in the @addressLookUp table. This addressName should be insert into the @exception table with the correct errorID from @errors.
i wrote the code but 'Pine heaven' and 'route 50 'is not showing.

View 5 Replies View Related

Select Or Join

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

SELECT INNER JOIN SUM

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

SQL Select/Join Help

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

Select Min Value In An Inner Join

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

Select With Join

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

How To Select With Inner Join

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

Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: Cannot Fetch A Row

Apr 8, 2008


I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:


Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".

Task failed: Bulk Insert Task

In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');


What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}

Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul

View 1 Replies View Related

Table JOIN With INSERT Statment

May 11, 2008

How to perform a table JOIN with the INSERT SQL statment, just show a join example between 2 tables will do.

View 2 Replies View Related

Merge Join Vs Insert Then Update: What Is The Best ?

May 8, 2008

Hello,

I have to load 2 flat file of 2 Gb each in a table. I found 2 ways to do that:
- Either I read my 2 file in parallels and I use a merge join transformation to merge my data. Then I load theses datas in my destination table
- Or I insert all my first file in the destination table then I read the second file and update my table with the new information.

Based on your experience, what is the best ? I'm running the first way for more than 30 minutes and SSIS is still sorting my datas.... (sort before the merge join)

Thanks in advance,

Knid regards

View 1 Replies View Related

Bulk Insert Statement With JOIN !!!

Sep 1, 2007

Hi,

I have two tables. Employees and departments as follows:

Employees
- Name
- Salary
- Department ID


Departments
-Department ID
-Department Name


Now what I am doing is that I am allowing the user to give me a CSV file with all the employees data to bulk insert it in the table Employees as follows:

Employee Name, Salary, Department Name


The problem is that the users know nothing about the department ID. So what they type in the CSV ffile as you can see above is the department name.

The SQL statement I normally use for bulk insert is:


BULK INSERT CAS.dbo.employees
FROM 'c:list.csv' WITH
(FIELDTERMINATOR = ',', ROWTERMINATOR = '' )

My question is, how can I use the same technique but insert the departments IDs not name as in the CSV file.!!!!

Appreciate your help.
Thanks

View 2 Replies View Related

Table JOIN With INSERT Statment

May 12, 2008

How to perform a table JOIN with the INSERT SQL statment, just show a join example between 2 tables will do.

View 3 Replies View Related

Problem With The Sql Select On Join

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

SELECT, JOIN And UPDATE

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

SELECT ... JOIN On Two Databases In VWD (?)

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

Select MAX In JOIN Query

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

Better To Join Tables In DB Then In SELECT?

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

Select Statement With Join?

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

Join 2 Select Statements

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

Select Query - Join

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

Select JOIN Problem

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

SELECT DISTINCT With JOIN

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

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 View Related

INSERT INTO... SELECT... Cannot Insert Duplicate Key...

Jul 20, 2005

I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?

View 2 Replies View Related







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