Hi. I'm doing a statistics of usage as a T-SQL on a MSSQL server. I would need to systematically 'stick' tables together.
lets say I have a @temptable:
Code:
DECLARE @temptable TABLE (YearM int, Period int)
and I have created another table
Code:
DECLARE @temptablecounts TABLE (YearM int, Period int, Count int)
Now I would like to make something like
Code:
@temptable = @temptable join @temptablecounts on YearM, Period
so I would get a table which looks like this:
Code:
@temptable (YearM, Period, Count)
That would I need to do a few more times, so finally I would get a table like:
Code:
@temptable (YearM, Period, Count, Count, Count ...) etc.
Is it somehow possible? Exists any other way to do what I need? Please help
I have a very simple Join Query like Select A, B , C From Database1.dbo.Table1 Inner Join Database2.dbo.Table2
Now problem is that Database name for the project freuqently changes though Table1 and Table2 names remain constant. How can I store Datbase name at a central place in a table or as a constant so that I can use them in above query. It will allow me to change them at one place without making changes in all the sps where they are used
Hello folks,Old programmer learning new tricks.I think similar issues have been posted, but I would like to ask some helpwith the following code...select [t1.*],[t2.userid] as [userid2],[t2.approved],[t2.type]from [tblMembers] as t1 left outer join [tblProfiles] as t2on [t1.userid]=[t2.userid]where ...I get the following error...[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name't1.userid'.....but there is a [userid] in both tables. Do I have to make them keys orsomething? And I need to do work (add and update) on both tables after thevalues are returned.Also, how bad is my syntax? If you could give advice on removing extraneousbrackets and I would like to not have to use the "as" clause. I really justwant to keep it simple. Whatever works though.Thanks
Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.
There is a MAIN table: ItemNo int identity(1,0), ItemType tinyint
There is a WETPAINT table: ItemNo int, Color varchar(20)
There is a DRYPAINT table: ItemNo int, Color varchar(20)
Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType
So I created a table function called getTable:
CREATE FUNCTION [dbo].[gettable] ( @ItemType int = 1 ) RETURNS @thistable TABLE ( Color varchar(20)
) AS BEGIN if @ItemType = 1 insert into @thistable (color) select color from WETPAINT if @ItemType = 2 insert into @thistable (color) select color from DRYPAINT RETURN END
This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:
SELECT MAIN.ItemNo, a.Color FROM MAIN INNER JOIN gettable(Main.ItemNo) as a ON a.ItemNo = MAIN.ItemNo
Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.
Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.
PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.
2222 22 22 15 90 1 and this is what I want to retrieve from these 3 tables
stud_num pk2 pk3 age
5432 55 44 23
9876 99 77 23
2222 22 22 15 this my query.... SELECT student.stud_num, student.pk2, student.pk3, student1.age, student2.age AS Expr1FROM student INNER JOIN student1 ON student.stud_num = student1.stud_num AND student.pk2 = student1.pk2 AND student.pk3 = student1.pk3 INNER JOIN student2 ON student.stud_num = student2.stud_num AND student.pk2 = student2.pk2 AND student.pk3 = student2.pk3WHERE (student1.grade = '90') AND (student1.pass = '1') AND (student2.grade = '90') AND (student2.pass = '1') however...it doesnt return any results... can anyone correct my code?
I have two tables in sql server database. Tables are Authors AuthorName varchar (primary key), AuthorImage varchar Threads ThreadID int Primary Key, ThreadAuthor varchar, ForumID int (Foreign Key), ThreadReplyID int, ThreadPostedDate int I have this query: "SELECT ThreadAuthor, ThreadSubject, ThreadPost, ThreadPostedDate , ForumID FROM Threads WHERE ThreadID = " + threadId + " OR ThreadReplyID = " + threadId + " ORDER BY ThreadPostedDate ASC" but i want to display AuthorImage on my webpage. How to modify the existing query to get the Author's image also. Plz help me out anyone there. Thanks
i've two tables. such as tableA and table B table A has a column named CreatedBY and table B the same column.. now i need to get only the rows which match with table A's createdby column.. suppose tableA has only 2 values. when i make the join with tableB it shows as 4 values
I need to perform a join on six different tables to produce a report. The biggest join I have ever done has been four tables. The way I did that query was to join three tables and then to perform the final select on the three table join. Can anyone give me some advice. Should I use the same approach? Or can I just continue to use the keyword INNER JOIN to perform the joins?
select left(orders.orderdate,11) as date, orderdetails.partnumber, SUM(orderdetails.qty) as total from orderdetails, orders where orderdetails.partnumber is not null and orderdetails.order_id = orders.order_id and orders.orderdate >= {ts'2001-05-01 00:00:00'} and orders.orderdate <= {ts'2001-05-31 23:59:59'} group by orders.orderdate, orderdetails.partnumber order by orders.orderdate, total
But I do not have part that have null orders. My goal is to get
1) Get all part numbers, qty in the month of May. 2) at the end attach all partnumbers that have null ordered values to see which one have no orders.
I have 2 tables. One with col1,col2 and second one with col1,col2,col3. My requirement is to compare col1,col2 of t1 with t2 and update only changed records in t2. How to implement? Please advise.
I've tried an outer left join but cannot get the right data. Basically everything from the timecard table and the leftovers from the default table with descriptions for all.
I've got two tables, one called clientsharedeals and the clientorderdeals. In the first table, I have four fields (Rundate, Accno, Dealid, Nominal) that I need to sum(Nominal), grouping by dealid.
Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).
My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.
Could somebody please explain to me how do we join a table onto itself as that is what I was advised to do but I can't quite get where I want to go.
What I want to do is list values from a table,but those values can be just a quote (what would cost if they decided to go for that option) or those values can represent what was spent and invoiced, what is confusing me is that all of that gets saved in the same table and in same columns, so what was quoted for example for AirFares and what was spent gets saved in the same record but when it is "quoted amount" then ID = 1 but when it is invoiced ID = -1 and that is how we know what was quoted and what was invoiced.
But I need to split that one field into two columns one showing AirFareQuoted and one AirFareInvoiced and i have no idea how to achieve this.
hi, I knew how to join 2 tables but i have a process to select 3 tables. I have a sample table and field below. I want to join Parts & Orders using field Prt_no and Supplier & Parts using field Sup _code
Hey Folks, I am stuck at one place in data migration........ I have the following source tables having no PK / INDEX / Constraint defined on any of them........
T1 ( ID1 INT, STYLE VARCHAR(34) )
T2 ( COL_ID VARCHAR(34), OBJ_ID VARCHAR(34) )
T3 ( OUT_ID VARCHAR(34), ITEM VARCHAR(34), )
And here is the relation..........
Quote: T1.STYLE = T2.COL_ID
T2.OBJ_ID = T3.OUT_ID
T3.ITEM = T2.COL_ID
Here is what I have tried with.........
Quote: SELECT * FROM T3 WHERE ITEM IN ( SELECT COL_ID FROM T1 INNER JOIN T2 ON T1.STYLE = T2.COL_ID INNER JOIN T3 ON T2.OBJ_ID = T3.OUT_ID )
But this query is not helping me out. I mean the number of output records are 0.
Now can any one suggest me a join that includes all the 3 tables, or finetune my query?
hi everybody I have a question and need answer as soon as possible. so i hope i get help. How can i join two tables have no common fields? I tried outer join but i get redundant data(repeated values).
Give me a script to solve the following problem . There are no real wrong answers, but the more efficient and bulletproof the query is the better. Assign rooms to classes based on capacity using the Classes and Rooms tables. Rules: each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.
And this is the tables query:
CREATE TABLE Classes (
ClassID int NOT NULL,
Class varchar (50),
Students int NOT NULL, CONSTRAINT PK_Classes PRIMARY KEY CLUSTERED (ClassID
) )
GO
CREATE TABLE Rooms (
Room int NOT NULL,
Capacity int NOT NULL,
CONSTRAINT PK_Rooms PRIMARY KEY CLUSTERED (Room)
)
GO
INSERT INTO Classes (ClassID, Class, Students) VALUES (1, 'Whats New for SQL Server 2008', 65)
INSERT INTO Classes (ClassID, Class, Students) VALUES (2, 'Introduction to Silverlight 1.1', 52)
INSERT INTO Classes (ClassID, Class, Students) VALUES (3, 'XQuery Deep Dive', 35)
INSERT INTO Classes (ClassID, Class, Students) VALUES (4, 'Ranking and Windowning', 35)
i have 2 tables with a common ID in both tables. one table has more number of rows than the other i want to a query which gives the list of ids or rows which are missing in the other table.
left,right,inner jopins only give me common or common + right/left i want only the missing once.
hi, i am facing an issue. i have got two tables say table1 and table2. table1 has column1,2,3 ( has values a,b,c) where as table 2 has different columns 4,5,6(j,k,l). column4 in table 2 has value which contains %+column1+% and it has two entires for each row in table1.the column4 value in table2 is different for all rows.When i am trying to join the two tables, i am getting values like
1 2 3 4 5 6 a b c j k l a b c j1 k l a1 b c j k l a1 b c j1 k l
but i want to display a b c k l a1 b c k l
is there a way to do it.
My queries were select distinct pc_assign_worklist.pxRefObjectInsName AS [pxRefObjectInsName], pc_assign_worklist.pxUrgencyAssign AS [pxUrgencyAssign], pc_assign_worklist.pyLabel AS [pyLabel], pc_assign_worklist.pyAssignmentStatus AS [pyAssignmentStatus], pc_assign_worklist.pxAssignedOperatorID AS [pxAssignedOperatorID], CONVERT(char (11),pc_assign_worklist.pxCreateDateTime) AS [pxCreateDateTime], pc_assign_worklist.pxCreateOpName AS [pxCreateOpName], pc_index_workparty.MemberIdentifier AS [MemberIdentifier], pc_index_workparty.LastName AS [Last Name], pc_index_workparty.FirstName AS [First Name], pc_index_workparty.pxInsName AS [pxInsName], pc_index_workparty.pzInsKey AS [pzInsKey], pc_index_workparty.pxpartySubscript as "Workparty" from dbo.pc_assign_worklist LEFT OUTER JOIN dbo.pc_index_workparty ON pc_index_workparty.pzInsKey LIKE '%' + pc_assign_worklist.pxRefObjectInsName + '%' where pxAssignedOperatorID = 'dasxkx1'
i also tried left inner join but it always returns the same result seta. I even created a view by inner join of two tables and then did left outer join on the second table . The result is same.
Can somebody tell whether this is achivable or not.
Hi everybody I know how to join two tables with [INNER JOIN] or [LEFT/RIGHT OUTER JOIN] Keywords ,But when i have more than two tables , always i mixed up and have difficulty to build the query specially when i have to use LEFT or RIGHT OUTER JOIN Keywords!!! and that's why i always use SQL Server Query Builder and let it to make the query string for me. i couldn't understand how SQL SERVER build the [FROM] section of the query ,really what method (Algorithm) does it uses to Build the query String??? could anyone point me or give me some advices? is there any book to this topic?
I have have 3 tables TableA, TableB and TableC. TableA holds the keysto TableB and TableC. I need a query which will display the detailsfrom TableB and TableC depending on the key in TableA.For eg.TableA - columns {id, relatedkey, recordType} ===recordType will holdvalues like TableB or TableCTableB - columns{id, column1}TableC - columns{id, column1}the query should match the related key to the id of TableA or table Bbased on recordType and show the column1 value with the TabelA id sooutput for this should beid recordType column11 TableB value of TableB column12 TableC value of TableC column1Please help.CheersNick
hello,I'm need some help with some sql to return some data...obviously ;)I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all thenames of the reps that are assigned to a particular manager.Here is a simplified exmaple of table setup:tblRep-------repid repname supid----------------------1 joe 5002 ann 501tblSup-------supid supname mgrid----------------------500 bill 1000502 tom 1001tblMgr-------mgrid mgrname---------------1000 andy1001 rogerThanks.
I've tried an outer left join but cannot get the right data. Basically everything from the timecard table and the leftovers from the default table with descriptions for all.
