I am trying to join two fields in a query in SQL 2000. For example.
Update myTable SET field_1 = @field_1_value , field_2 = @field_2_value, field_3 = @field_1_value + ' x ' + field_2_value
Is this even possible.
I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.
Thanks in advance,
Scotty_C
I am trying to join two tables and the datetime fields need to be part of the join. They contain times in the fields too. I just need them to join on the date part though. Is there maybe a way to just return the mm/dd/yyyy format and join on that? Any and all help is appreciated.
Thanks,
Chris
I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.
View 2 Replies View RelatedQuestion.I have a new table that I am adding to a script that I wrote. Thistable has 3 fields, the first 2 fields are used in the on statement asbeing = other fields in the script.The first field always has data in it, but the 2nd field is sometimesnull.So my problem is if both fields have data in them and they both matchto the data in the fields that I am linking them to, then it returnsthe 3rd field without a problem. However if the 2nd field is null thenit is returning a null for the 3rd field. I have checked and the fieldthat I am linking to is null also.So if I haveselect t1.field1, t1.field2, t2.field1, t2.field2, t2.field3from table1 t1join table2 t2on t1.field1=t2.field1 and t1.field2=t2.field2with 2 records in each tabletable1: record1: data, datarecord2: data, nulltable2: record1: data,data,datarecord2: data,null,datawhat I get from the script isrecord1: data, data,data,data,datarecord2: data,null,data,null,nullI would expectrecord2: data,null,data,null,dataI hope this makes sense, I didn't want to post the entire actual scriptas it is about 150 lines long.Thanks in advance.
View 5 Replies View RelatedIf I have a database table with the following columns:
ID
Other_ID
Description
And I want to join the two ID fields to one field in another table that contains the following fields:
ID
Name
How would i do that?
Here is some sample data and what I would like returned
TABLE1
ID Other_ID Description
row 1 1 2 Number1
row 2 3 1 Number2
TABLE2
ID Name
row 1 1 John
row 2 2 Bob
row 3 3 Bill
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like:
John Bob Number1
The only way around it now is that I store Other_Name in Table1.
Thanks.
I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?
My query and my desired results are below:
select item, test_date
from inventory
where cat = 'TELE' and itemised_status > 15
item test_datecert_no
-------------------- ----------------------------------------
05MC0002 2014-06-10 00:00:0005MC0002-06-2014
06MT0001 2014-05-13 09:02:0006MT0001-05-2014
06MT0002 2014-05-13 09:03:0006MT0002-05-2014
06MT0003 2014-05-13 09:03:0006MT0003-05-2014
06MT0004 2014-05-09 14:12:0006MT0004-05-2014
Can someone comment on why joining two tables on CHAR fields of different lengths would generate unexpected results?
I had an issue where I ran an update that used an inner join on two tables. The field I used in the join was char(50) in one table and char(13) in another table. The result gave bad matches. After changing the field types both to varchar(30), the problem was eliminate.
Any comments on this would be appreciated.
Rye Guy
I am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses:
(A = Active, R = Repeat, L = liquidation......)
Here is a sample of the data I am trying to report off of:
CREATE TABLE [dbo].[item_status](
[item_number] [varchar](20) NULL,
[sku] [varchar](100) NULL,
[Field_Name] [varchar](50) NULL,
[Old_Value] [varchar](150) NULL,
[New_Value] [varchar](150) NULL,
[Change_Date] [smalldatetime] NULL
) ON [PRIMARY]
[code]...
I have tried join to the same table - but I am still unable to get it to work.
I know how to join 2 tables, but I have a third I need to insert. For some reason, this doesn't work:
Code:
$rows = ff_select(
"select ".
"u.email as email, ".
"c.user_id as user_id, ".
"u.name as name, ".
"r.age as age ".
"from #__comprofiler as c ".
"left join #__users as u on c.user_id = u.id ".
"left join #__rnr_contest as r on c.user_id = r.userid ".
"where (r.age != chicken) and (r.age != nystrip) and (r.age != regrets) and (u.block = 0) and (c.cb_contactmethod LIKE '%Email%') and (u.usertype != 'Super Administrator') and (u.email != 'please@change.com') and (u.username != 'guest') and (u.username != 'piedmont') ".
"order by email"
anyone see why? It tells me that "chicken" is not a column which is weird because I don't think it's listed as a column in my query... is it?
Hi All,
First off sorry for my complete lack of experience with SQL, but I've just started a new position which involved learning SQL. I'm currently looking for a course but in the mean time I just have to try and fumble by doing basic things.
I'm trying to write my first basic select / join statement, and I just can't seem to get it working.
Any help would really be appreciated. It's T-SQL we use btw.
Thanks :)
USE MLSDHeat
GO
select * from dbo.CallLog
where callstatus between 'open'and 'pending'
right outer join dbo.Asgnmnt
on callid = callid
order by callid
--the above returns error Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'right'.
CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60))
RETURNS @copdmailinglist TABLE
(
list_ varchar(60) ,
title_ varchar(255) ,
desc_ varchar(255),
message_id int ,
txt varchar(255) ,
cnt int ,
cnt_txt varchar(255)
)
--Returns a result set that lists all the copds
AS
BEGIN
WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS
(select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc
left outer join ListManager.dbo.get_mailingidcopd_('') gmc
on gmc.name_ = osc.title_
where list_ = @list_
)
-- copy the required columns to the result of the function
INSERT @copdmailinglist
SELECT list_ , title_ , message_id , txt , cnt , cnt_txt
FROM ListManager.dbo.[List Copd]
RETURN
END
GO
i m getting error that Incorrect syntax near the keyword 'WITH'.
can anyone tell me how to join functions in sql?
thanks.
Select LOCGeneralHave.LSCH, LOCSubClassHave.LSCH from LOCGeneralHave ,LOCSubClassHave Where (LOCGeneralHave.LCNT <> '0' andLOCSubClassHave.LCNT = '0')This query seems to be ignoring the 'and' part of the clause.Basically I want select from table1, table2 where LCNT in table1 is not0 andLCNT in table2 is 0.I have verified the LCNT's numbersThis query returns 2 columns with 1700 rowsIt needs to only find a few rows.What am I missing, any ideas, thanks for any help.
View 4 Replies View RelatedOk, What I want to achieve is plain stuff, I want to join 2 views on a common key.It all works well with the SQL2000 Query Analyzer, but not trough ADO.NET or should I say my webapplication.With that I mean that my query return rows when executed from SQL2000 Query Analyzer, But not when used in my application or Executed from the Visual Studio Server Explorer.I have struggled with this one for several hours, I cant get this one right.So lets bring in the one who actually know what his doing View1: 1 select
2 cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2)) AS Rabatt
3 ,cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2)) AS MVA
4 ,cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2)) AS Belop
5 ,PS.*
6 ,K.Kunde_ID
7 FROM
8 tbl_ProduktSalg AS PS
9 INNER JOIN
10 tbl_Ordre AS O
11 ON
12 O.Ordre_ID = PS.Ordre_ID
13 INNER JOIN
14 tbl_Kunde AS K
15 ON
16 K.Kunde_ID = O.Kunde_IDView2: 1 SELECT
2 PS.Ordre_ID
3 ,SUM(cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalRabatt
4 ,SUM(cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2))) AS TotalBelop
5 ,SUM(PS.Pris) AS TotalPris
6 ,SUM(cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalMVA
7 FROM
8 tbl_ProduktSalg AS PS
9 GROUP BY
10 PS.Ordre_ID
MyQuery/SPRC: 1 create procedure %PROC% (@Kunde_ID int, @Ordre_ID int)
2 as
3 begin
4 SELECT
5 v_PSD.*
6 ,v_OTS.TotalRabatt
7 ,v_OTS.TotalBelop
8 ,v_OTS.TotalPris
9 ,v_OTS.TotalMVA
10 FROM
11 v_ProduktSalgDetaljer AS v_PSD
12 INNER JOIN
13 v_OrdreTotalSum AS v_OTS
14 ON
15 v_OTS.Ordre_ID = v_PSD.Ordre_ID
16 WHERE
17 v_PSD.Kunde_ID = @Kunde_ID
18 AND
19 v_PSD.Ordre_ID = @Ordre_ID
20
21 end
22
Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.
A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.
Thanks, Dave
I have four tables:
a, b, c and d
table a is related to table b by a foreign key. table b is related to c and so on.
I used the sql statement below to join the tables:
$result = mysql_query("SELECT a.colum1, a.column2,
b.column, c.column, d.column FROM a
JOIN b ON a.pkey = b.foreign key
JOIN c ON b.pkey = c.fkey
JOIN d ON c.pkey = d.fkey ")
or die(mysql_error());
[Code] .....
I succeeded in printing out the first record where the four tables are joined, but not other instances.my print out is this:
a.column1
a.colum2
b.colum
c.colum
d.colum
But there are about ten instances where the joining conditions are met. How do I print out all the records that have met the condition?
Two tables:T1 (c1 int, TestVal numeric(18,2), ResultFactor numeric(18,2))--c1 isthe primary key.T2 (x1 int, FromVal numeric(18,2), ToVal numeric(18,2), Factornumeric(18,2))--x1 is the primary key. T2 contains non-overlappingvalues. So for eg., a few rows in T2 may look like.1, 51, 51.999, 512, 52, 52.999, 52........32, 82, 82.999, 82........T2 is basically a lookup table. There is no relationship between thetwo tables T1 and T2. However, if the TestVal from T1 falls in therange between FromVal and ToVal in T2, then I want to updateResultFactor in T1 with the corresponding value of Factor from the T2table.------Example for illustration only---------------Even though tables cannot be joined using keys, the above problem is avery common one in our everyday life. For example T1 could beemployees PayRaise table, c1=EmployeeID, with "TestVal" representingtest scores (from 1 to 100). T2 representing lookup of the ranges,with "Factor" representing percent raise to be given to the employee.If TestVal is 65 (employee scored 65% in a test), and a row in T2(FromVal=60, ToVal=70, Factor=12), then I would like to update 12 intable T1 from T2 using sql;. Basically T2 (like a global table)applies to all the employees, so EmpID cannot serve as a key in T2.---------------------------------------------------------Could anyone suggest how I would solve MY PROBLEM using sql? I wouldlike to avoid cursors and loops.Reply appreciated.Thanks
View 1 Replies View RelatedSorry for the akward title, not sure how to say this. I have a Person table and a addresses table. Each person may have many address records as shown below:
Person
-----------
PersonID | AutoNum
fName | varchar
lName | varchar
...etc
Addresses
---------------
addressID | AutoNum
personID | int (FK)
address1 | varchar
city | varchar
state | varchar
isPrimary | bit
...etc
What I'm trying to do is select all from Person and the city and state of each person's primary address. A little voice keeps saying subquery...but I can't figure it out. So far I have the SQL below, but if there is no address or no address where isPrimary = 1, it fails to return the person record. I need the person record regardless of if they have a primary address. Does that make sense?
Doesn't return all Person records:
SELECT Person.*, Address.City, Address.State
FROM Person LEFT OUTER JOIN Address
ON Person.PersonID = Address.PersonID
WHERE (Address.isPrimary= 1)
ORDER BY Person.lName, Person.fName
I am having the following situation - there is a view that aggregates and computes some values and a table that I need the details from so I join them filtering on the primary key of the table. The execution plan shows that the view is executed without any filtering so it returns 140 000 rows which are later filtered by the join operation a hash table match. This hash table match takes 47% of the query cost. I tried selecting the same view but directly giving a where clause without the join €“ it gave a completely different execution plan. Using the second method is in at least 4 folds faster and is going only through Index Seeks and nested loops.
So I tried modifying the query with third version. It gave almost the same execution plan as the version 1 with the join operation.
It seams that by giving the where clause directly the execution plan chosen by the query optimizer is completely different €“ it filters the view and the results from it and returns it at the same time, in contrast to the first version where the view is executed and return and later filtered. Is it possible to change the query some how so that it filters the view before been joined to the table.
Any suggestions will be appreciated greatly
Stoil Pankov
"vHCItemLimitUsed" - this is the view
"tHCContractInsured" - this is the table
"ixHCContractInsuredID" - is the primary key of the table
Here is a simple representation of the effect:
Version 1:
select *
from dbo.vHCItemLimitUsed
inner join tHCContractInsured on
vHCItemLimitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID
where tHCContractInsured.ixHCContractInsuredID in (9012,9013,9014,9015)
Version 2:
select *
from vHCItemLimitUsed
where ixHCContractInsuredID in (9012,9013,9014,9015)
Version 3:
select *
from dbo.vHCItemLimitUsed
where ixHCContractInsuredID in
(select ixHCContractInsuredID
from tHCContractInsured
where ixHCContractInsuredID in (9012,9013,9014,9015))
In database DB1, I have table DB1.dbo.Suppliers1. This table has an ID column of type INT named SUPPL1_ID
In database DB2, I have table DB2.dbo.Suppliers2. This table has an ID column of type INT named SUPPL2_ID
I would like to update DB2.dbo.Suppliers2 based on values from DB1.dbo.Suppliers1 joining on SUPPL1_ID = SUPPL2_ID.
How can I do this in SSIS?
Assumptions:
linked servers are not an option, as I want the SSIS package to be portable and not dependent on server environments.
TIA.
-El Salsero
I am learning the Optimizer from the book "Querying Microsoft SQL Server 2012" for certificate exam 70-461. I really cannot understand how it explains the number of possible ways to execute a query joining three tables. the pseudo-query is:
SELECT A.col5, SUM(C.col6) AS col6sum
FROM TableA AS A
INNER JOIN TableB AS B
ON A.col1 = B.col1
INNER JOIN TableC AS C
ON B.col2 = c.col2
WHERE A.col3 = constant 1
AND B.col4 = constant2
GROUP BY A.col5;
The book says:"Start with the FROM part. Which tables should SQL Server join first, TableA and TableB or TableB and TableC? And in each join, which of the two tables joined should be the left and which one the right table? The number of all possibilities is six, if the two joins are evaluated linearly, one after another."
Q1: How could it be six possibilities? From my understanding, lets say, if the SQL Server has to join A and B first, and then join C, in this case I can think of 4 possibilities, which are:
1. When A Join B, Left: A, Right: B.
  When Join C, Left: result of A join B, Right: C
2. When A Join B, nbsp; Â
When Join C, nbsp;When A Join B, nbsp; Â
When Join C, nbsp;When A Join B, nbsp; Â Â
When Join C, "line-height:13.5px;">
Q2:Â The section following the previous question says there are 4 different types of join.."This already gives four options for each join. So far, there are 6 x 4 = 24 different options for only the FROM part of this query."
How can it be 6 x 4? My understanding is 4 is only for 1 join, but in our case, there are 2 joins, so it should be 6 x 4 x 4.
I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?
select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id
There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.
Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
I am having trouble figuring out how to complete this any help is appreiciated.
I have Mon , Tues , Weds , Thurs , Fri as bit fields I need to write a query for a report to see if the value is true, and display M, T, W, Th, F in a column Days if they are scheduled that day
example:
Mon and Wed I got on the bus the checkboxes are set to True, I want one column in the report Days: to display M, W
Hello all,
How can i write a sql statement that can give me the name of the fileds in a specified table.
Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.
any help.
how can i write a SQL query that will pull all records that are equal to NULL??
View 1 Replies View RelatedCan anyone please help me to find the diffence between two fields.
Field1 - 16:12:27:123
Field2 - 16:12:36:750
I need to find the difference between field2 and field1??
I am trying to do a select query like below...
SELECT INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM,
INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND,
INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM INVENTORY_ITEM_TAB INNER JOIN
INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR
But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.
how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.
I have 2 tables.
Clients
Contacts (multiple contacts for one client)
I'm trying to do a search that pulls a contacts where the search matches either the First Name, last Name or the clients name. If it does return clients, I'd like it to also return all the contacts associated with it.
I have two problems:
1. The query is not bringing up a lot of clients. In many cases a letter brings nothing back. Like G and H even though A and B return results.
2. If it finds a client it only returns one contact. I'd like it to return all contacts for the client.
Here's my query:
SELECT addressbook.clientid, clients.clientname, addressbook.addressid, addressbook.fname, addressbook.lname FROM clients, addressbook where clients.clientid = addressbook.addressid
AND (addressbook.fname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR addressbook.lname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR clients.clientname LIKE
'" . strtoupper($_GET['txtsearch'])."%')
I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....
This is what I have so far -
SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @guid then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4
I'm not a T-SQL expert so I'm calling on the pros. I want to design a stored procedure that updates only fields in a given record for which a non-null parameter is specified.
Example:
Create dbo.MySproc
@CustomerID int,
@CustomerFirstName varchar(50)=NULL,
@CustomerLastName(50)=NULL
AS
UPDATE Customers
SET
CustomerFirstName= @CustomerFirstName , //update only if non-null
CustomerLastName= @CustomerLastName //update only if non-null
WHERE CustomerID = @CustomerID
RETURN
So say when calling sproc if @CustomerFirstName is specified but @CustomerLastName is not, the field CustomerFirstName would be updated but field CustomerLastName would retain original data.
Thanx in advance!
An ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this:
set rs=server.createobject("ADODB.Recordset")
rs.open "select * from mytbl",application("mycon"),1,3
And the error is something like this:
"you can't query more than the maximum field "
I apologize for the newbie sort of question, but I could not find an answer in an SQL book nor via Google.
I wish to search for a text string in ALL fields of a table. This will be used to provide a simple search box in a web application.
So far, the only method I've found to accomplish this is follows:
SELECT *
FROM Inventory
WHERE SerialNumber LIKE '%searchstring%' OR UserName LIKE '%searchstring%' OR Location LIKE '%searchstring%' ... etc
My goal is to accomplish something like the following. This, of course, does not execute properly since * can only be used following SELECT, but you can get an idea of the target behavior:
SELECT *
FROM Inventory
WHERE * LIKE '%searchstring%'
I'm using MSDE with Visual Basic .Net. Any suggestions on how to accomplish this?
Thanks for all help,
Kieran
I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.
For example
Code:
SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'
FROM Table1
However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.