Hello,
I have two union statements that I need to join to each other. Then I need to join that result to another table. So-
[(TBL-A UNION TBL-B)
JOINED TO
(TBL-C UNION TBL-D)]
JOINED TO
TBL-E
Is this a subquery thing, or something else? I have not been able to figure out how to make it work! The unions I have made work fine though, on their own.
I am having difficulty with joining these two statements and I just can't seem to get a way. Here is the query:
select Name,Surname,position
from dbo.Employee, dbo.Stafflink
where dbo.Employee.ID = dbo.Stafflink.empID
group by Name,Surname,Position
select Employee.Name as ManagerName, Employee.Position as ManagerPosition
FROM dbo.Employee, dbo.Stafflink
WHERE dbo.Employee.ID = dbo.Stafflink.ManID
How can I join both of these statements?
I only want to count the titleids that are on loan can I join these two statements or write the stored procedure a different way? I hope this makes sense.
select count(libraryrequest.titleid) as [Presently on Loan], libraryrequest.titleid, media.[description]as Media
from libraryrequestjoin titles on titles.titleid = libraryrequest.titleidjoin resources on resources.titleid = titles.titleidjoin media on media.mediaid = resources.mediaidgroup by libraryrequest.titleid , media.[description]
select distinct requestors.Requestorid, titles.title, resources.quantityowned,requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip, libraryrequest.shipdate,libraryrequest.duedate, libraryrequest.returndate
from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.requestorid = Requestors.requestoridjoin resources on resources.titleid = titles.titleid
Hi ,
If any one could help me in Joining these two select statements:
How Can I have Order by and Having Clauses in my final select list ?
***********
SELECTMIN(DISTINCT x.ProductionDate) as ProductionDay,COUNT(DISTINCT v.Number) as Timelost
FROMmaster..spt_values AS v
INNER JOIN
(
SELECTDATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute,
DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute,
Convert(varchar(10),StartDate,102) as ProductionDate
--,COUNT(AlarmMessage) As Occurances
FROM RPMS.dbo.List_PaintShopAlarms
WHERE --Convert(varchar(10),StartDate,102) = @vcProductionDay and
Convert(varchar(10),StartDate,102) = Convert(varchar(10),EndDate,102)
AND (AlarmMessage LIKE '7FM%' OR AlarmMessage LIKE 'CONV%' OR AlarmMessage LIKE 'EMG%' )
)
AS x ON x.StartMinute <= v.Number
AND x.EndMinute > v.Number
WHEREv.Type = 'p' AND Number < 1440
and (x.ProductionDate BETWEEN Convert(varchar(10),@dtProductionDayStart,102) AND Convert(varchar(10),@dtProductionDayEnd,102))
group by x.ProductionDate
order by x.ProductionDate asc
***********
and
***********
SELECT Convert(varchar(25),[StartDate],102)as ProductionDay
,COUNT(AlarmMessage) As Occurances
FROM [RPMS].[dbo].[List_PaintShopAlarms]
WHERE
([AlarmMessage] LIKE '7fm%' or [AlarmMessage] LIKE 'Conve%') AND
Convert(varchar(25),[StartDate],102) BETWEEN Convert(varchar(25),@dtStartDate,102) AND Convert(varchar(25),@dtEndDate,102)
and DATEPART(dw, [ProductionDay]) <> 1
GROUP BY Convert(varchar(25),[StartDate],102)
HAVING SUM([Duration]) < 86400
ORDER BY Convert(varchar(25),[StartDate],102) asc
*************
Hi,
If any one could suggest if Is it possible to make a JOIN of two different select statements.
SELECT PartNumber,SUM(Value) FROM dbo.List_Parts where PartNumber like '%TX'
To be Joined with
SELECT PartNumber,COUNT(PartNumber) FROM dbo.List_Orders where Part Number like '%TX'
The result should be:
PartNumber , SUM(Value),COUNT(PartNumber)
Wondering if there is a physical or realistic limitation to the numberof UNION statements I can create in a query? I have a client withapprox 250 tables - the data needs to be kept in seperate tables, but Ineed to be filtering them to create single results sets. Each tableholds between 35,000 - 150,000 rows. Should I shoot myself now?lq
View 15 Replies View RelatedI'm new to SQL stored procedures, but I'm looking to be able to select order by statement.
declare @OrderBy
@OrderBy = 'first_name'
Select first_name,last_name from table1
Union
Select first_name,last_name from table2
if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end
You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help
I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code. Each block is the same except for the date. How could I rewrite these 30 union statements into a more concise query?
Each client (people_id) has a distinct agency_id_number. So the code below totals the number of distinct clients/day and groups them by programs and facilities.
Sample data that you can use to test this report:
IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census
create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)
[Code] ....
Good day all.
Let's see if I can stump you with this one.....
Code Snippet
SELECT Account.New_DistributorAccountNumber, Account.New_NameonBill, ActivityPointer.Subject, ActivityPointer.CreatedOn AS ExprX
FROM Account INNER JOIN
ActivityPointer ON Account.AccountId = ActivityPointer.RegardingObjectId
UNION
SELECT Account_1.New_DistributorAccountNumber AS Expr1, Account_1.New_NameonBill AS Expr2, ActivityPointer_1.CreatedOn AS Expr19,
ActivityPointer_1.Subject AS Expr20
FROM Account AS Account_1 INNER JOIN
Contact AS Contact_1 ON Account_1.AccountId = Contact_1.AccountId INNER JOIN
ActivityPointer AS ActivityPointer_1 ON Contact_1.ContactId = ActivityPointer_1.RegardingObjectId
gives the error mentioned above. Each select statement, when run separately, works fine. Any clues?
Thank you in advance
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
Hi all,
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
Any idea why this could happen ?
I want to make some joining in my table but I dont know what kind of joining is appropriate. Here is my tables sample:table1 -> tableid, useridtable2 -> userid, firstname, lastnametable3 -> userid, firstname, lastname Now, I want to to get data from table1 with the data from table2 and table3 where userid of table1 = table2, table3.
View 2 Replies View RelatedMay I know how to issue JOIN statement to join two ID from different DB ?
example:
dbA - tableA - a.ID
dbB- tableB - b.ID
Thanks.
Hi. I'm new to SQL, and need to join 2 tables... any hints???
table1:id (int)title(varchar(50))body(text)
table2:id (int)title(varchar(50))body(text)
somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information:
table1:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body"
table2:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body"
I would like to get...
id table title body3 1 "third title" "third body"3 2 "third title" "third body"2 1 "second title" "second body"2 2 "second title" "second body"1 1 "first title" "first body"1 2 "first title" "first body"
Does anyone know how to get this? I am fairly flexible if i need to change things...
cheers, eh!
Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT *, _SubCategories.SubCategoryNameFROM _ProductsINNER JOIN _SubCategoriesON _Products.DesignId = _SubCategories.SubCategoryIdWHERE DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec
View 11 Replies View RelatedHello all,
I have two datatables "customersReached " and "customersGuessed " and I want to combine them into one table only, the problem is that one table exeeded to the other by two fields, so what can I do???????
Mahmoudona
Hello I have two queries that I would like help joining: Query #1SELECT UserName, (SELECT COUNT(*) AS Expr1 FROM Jokester WHERE (InvitedBy = aspnet_Users.UserName)) AS invsFROM aspnet_Users Query #2 SELECT UserName, SUM(Hits) AS HitsFROM (SELECT UserName, (SELECT COUNT(*) AS Cnt1 FROM (SELECT COUNT(*) AS Cnt2 FROM Hits WHERE (JokeId = j.JokeId) GROUP BY UserId) AS T1) AS Hits, (SELECT AVG(CAST(Rating AS numeric(12, 2))) AS Rating1 FROM Ratings WHERE (JokeId = j.JokeId)) AS Rtng FROM Jokes AS j) AS t2WHERE (Hits >= 1) AND (Rtng >= 3)GROUP BY UserName They both return correct values, I just want to join them on UserName Thank you, Louis
View 8 Replies View RelatedI've been trying to think about how I can do this. I have forums that I have written built around SQL Server. Basicly you have:
-A users Table
-A Posts Table
-A Replies Table.
Posts and replies have very similar structures. I'd like to be able to merge them and pick out the earliest post for said forum.
1 - is there a way to merge them so that the post date for both the replies and posts tables is contained in 1 column. If not is there a better alternative.
I'd also like to add indexing to the posts so I can do paging. Is there a way for me to add an index number to them while I can sort them anyway i want.
Right now I have two Queries that I've created:
What I'm wanting to do is graft everything from Query1 onto Query2 but only where Query1.UserID = Query2.UserID . Any suggestions on how I would go about doing this?
--Query 1
(
SELECT
PC1.ID,
PC1.UserID,
PC1.ModuleID,
PC1.ModifiedDate
FROM
Projex2_0_0_Cores PC1
WHERE
PC1.ModuleID = 369
)
--Query 2
(
SELECT
PC2.UserID,
MAX(PC2.CreatedDate) as CreatedDate
FROM
Projex2_0_0_Cores PC2
WHERE
PC2.ModuleID = 369
GROUP BY
PC2.UserID,
PC2.ModuleID
)
I am using MS SQL Server 2005 on Windows XP with SQL Server Management Studio Express CTP. I am having issues with my query on joining 2 tables I created using BETWEEN to restrict the Salary. Table 1 is called Employee and Table 2 is called Job_title. The column Job_title_code is the only column that is in both tables which is how I am joining both tables. Here is my SQL query:
Code:
SELECT Employee.*, Job_title.*
From Employee
INNER JOIN Job_title
ON Employee.Job_title_code=Job_title.Job_title_code
WHERE Salary
BETWEEN 50000 AND 500000;
The results I am getting back are:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Job_title_code'.
I can't figure out how to fix this error. I feel like I have tried everything, so any help will be much appreciated. Thank you.
Hi,
I have a table with fields as partnerid, contractno.
The partnerid field has the Id number which can be a supplier or a customer.
I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.
Data in table
PId ContractNo
20045 1567
435 1567
123 1567
345 1678
1004 1678
I need to display the data in the following format.
PId(Supplier) PId(Customer)
20045 1567
20045 435
20045 123
345 1678
345 1004
But I'm getting the data replicated with all records joined every record.
Give the suggestion.
G'day,
I have got following 4 tables
Table 1
name age city
jack 20 Melbourne
Nick 30 Bendigo
Russ 28 Sydney
Table 2
name age city Company
jack 20 Melbourne AAA
Nick 30 Bendigo BBB
Russ 28 Sydney AAA
Marty 31 Perth AAA
Table 3
name age city Position
jack 20 Melbourne Manager
Nick 30 Bendigo Manager
Russ 28 Sydney Clerk
Marty 31 Perth Manager
Table 4
name age city datejoined
jack 20 Melbourne 09-09-2001
Nick 30 Bendigo 08-05-2001
Russ 28 Sydney 10-12-2000
Marty 31 Perth 11-11-1999
I want a query which extract the name, age and city from Table 2 (where name,age and city equals table1 values) and position from table3 where position is 'manager' else return null and date joined from table 4 only for the managers else return null.
so the result should be
name age city position datejoined
jack 20 Melbourne Manager 09-09-2001
Nick 30 Bendigo Manager 08-05-2001
Russ 28 Sydney null null
my query
SELECT b. name, b.age, b.city,b.company,c.position,d.datejoined
FROM Table1 a, Table2 b, Table3 c, Table4 d
WHERE
a.age=b.age
and a.name=b.name
and a.city=b.city
and b.age*=c.age
and b.name*=c.name
and b.city*=c.city
and b.position='Manager'
and b.age*=d.age
and b.name*=d.name
and b.city*=d.city
THE RESULT IS
jack 20 Melbourne Manager 09-09-2001
Nick 30 Bendigo Manager 08-05-2001
Russ 28 Sydney null 10-12-2000
When I try to join table4 with table i am getting a exception
Ps: as the original code was in SQL SERVER 6.5 I have to use *= for joins not keywords LEFT JOIN or RIGHT JOIN
hope yo guys can help me
regards
Melb
I have tables Companies, CompaniesDetails (the company branches), Addresses and Companies_Addresses.
The addresses table contain street and city while the Companies_Addresses has the keys for both companies and branches ,i.e., they are linked to Companies and CompaniesDetails via CompanyID and CompanyDetailID and to Addresses via addressID.
Companies_Addresses
id (PK)
companyID (FK)
companyDetailID (FK)
addressID (FK)
I am able to get the branch address at the moment with this code but I would like to get the company address as well using a single select statement.
Code:
SELECT DISTINCTAddresses.city as branchCity, Addresses.street as branchStreet
FROMCompanies
LEFT JOINCompaniesDetails AS cd ON companies.companyID = cd.companyID
LEFT JOINCompanies_Addresses AS c ON c.companyDetailID = cd.companyDetailID
LEFT JOINAddresses ON c.addressID = Addresses.addressID
WHERE Companies.name LIKE 'abc'
ANDCompanies.status_indicator like 'Current'
I have a bit of an issue that I can not seem to figure out and was hoping to get some feedback/advice from you all.
First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.
Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.
Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.
Example code:
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id ? users.group.id
The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?
Thanks in advance for any input.
T
Hi,
i have some sql experience and can link tables but the link i am trying to get is not displaying how i need it to
here is the code i am using, which display logical results, but not the ones i need :P
qry = "SELECT * FROM wce_contact INNER JOIN wce_mailer_link ON wce_contact.UNIQUEID = wce_mailer_link.Contactid LEFT JOIN wce_mailer ON wce_mailer.uniqueid = wce_mailer_link.mailerid RIGHT JOIN wce_mailer_attachments ON wce_mailer_attachments.uniqueid = wce_mailer.fileid WHERE wce_contact.uniqueid = '"& Request.QueryString("id") &"'"
Ok i have these tables
wce_contact
This has the contacts name and address
wce_mailer
This holds the details of the mailer and a link to the wce_mailer_attachments, there would be multiple rows in wce_mailer_attachments table which link to 1 row in wce_mailer.
wce_mailer_link
This holds the wce_contact uniqueid, and the wce_mailer uniqueid. there will be many contacts to many mailers
wce_mailer_attachments
This holds an individual row for one attachment, but the uniqueid would be the same for multiple rows, Dependant on how many attachments the users adds. i.e. one mailer could have several attachments, they would all have the same uniqueid.
Basically the results i am getting using the join i built are displaying each attachment as a separate row when i display the mailers assigned to a contacts record. i need them to display in one single row where the uniqueids are the same in the wce_mailer_attachments and they match the only fileid in wce_mailer.
Hope that makes sense.
Thanks for any help
Hi Can anyone help me join these two quries together?
SELECT SUM(SchemeAccount.TotalVar) AS TotalVar, Scheme.Code
FROM SchemeAccount LEFT OUTER JOIN
Scheme ON SchemeAccount.SchemeID = Scheme.SchemeID
GROUP BY Scheme.Code
and
SELECT SUM(Variation.VarAmount) AS VarAmount, Scheme.Code
FROM Variation RIGHT OUTER JOIN
Scheme ON Variation.SchemeID = Scheme.SchemeID
GROUP BY Variation.SchemeID, Scheme.Code
Hi guys can you help me joining three tables?
I used to join all those three but there's a redundant on it's value..
Table1
IDAccount
1A
2B
3C
Table 2
ID Name
1A
2B
3C
Table 3
AccountName
456ABC
456ABC
456ABC
with the following result:(what query should I use?)
IDNameAcctName AccountID2Name2ID3Name3
1AABC 456 2B3C
Hi,
I need to select date for my project.
I used the following
select datepart(yy,getdate())
select datepart(mm,getdate())
select datepart(dd,getdate()) -7
and i get
2008
6
4
How do I get the following result?
20080604
without any "." or "-"?
Thanks
I have 3 columns with text (dates) 06 03 08. I want to join those columns into one column.
Help, cast or concat does not work.
Lisa Jefferson
Hello friends,
Im a begginer in SQL Server and I just to ask for help on how to this this tables
table1
Field1
123
1234
12
154
123
321
123
21
Table2
Field1
test1
test2
test3
test4
test5
test6
desired output
Field1 Field1
123 test1
1234 test2
12 test3
154 test4
123 test5
321 test6
123
21
can i ask for script how to join this table? i try this script but it doest work.
select table1.col1,table2.col1
from table1
left join table2
ON table1.col1 is NOT NULL
Thanks in advance and more power!
I am getting output two times but my table has single data then how to remove duplicate my SQL query is:
select nonregister.email as firstname,nonregister.email ,appliedjobstatuswithresumes.description,appliedjobstatuswithresumes.applieddate from nonregister , appliedjobstatuswithresumes where appliedjobstatuswithresumes.nonregid IN (select nonregid from nonregister where nonregid IN (select nonregid from [appliedjobstatuswithresumes]
[Code] ....
Hi all!
I have a problem joining three tables. My tables are:
rooms: room_id ¦ room_name
computers: computer_id ¦ computer_name ¦ room_id
bookings: booking_id ¦ computer_id ¦ date
I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.
I have gotten a correct result with:
SELECT rooms.room_name, t.computer_name, temp.date FROM rooms LEFT JOIN (SELECT computers.room_id, computers.computer_name, bookings.date FROM computers JOIN bookings ON (bookings.computer_id = computers.computer_id)) AS temp ON (rooms.room_id = temp.room_id)
but I would like to use something shorter like:
SELECT rooms.room_name, computers.computer_name, bookings.date from computers INNER JOIN bookings ON (computers.computer_id = bookings.computer_id) RIGHT JOIN rooms ON (computers.room_ID = rooms.room_ID)
That however gives me a list where every combination of rooms and computers is listed and bookings displayed when there is a match. I don't really get why this doesn't work and I've been staring at it for days. Could somebody help me out? (I'm using PHP and MySQL)
Thanks,
Nick