I was wondering what is the best way to have multiple joins?
Here are the two statements I've been trying to combine
--------------------------------------------------------------------
SELECT CASE when
t1.Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(t1.Cust_DB_Shipment_Key,1,5,''))
End,
t1.Airway_Bill_No,
t1.Shipper_Reference,
t1.External_Product_Cd,
t1.Chargeable_Weight,
dt.CountTrackingNumber,
dt.SumProductCharge,
t1.Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping t1
Inner Join
(
SELECT Cust_DB_Shipment_Key,
sum(PRODUCT_CHARGE_AMOUNT) as [SumProductCharge],
count(Airway_Bill_No) as [CountTrackingNumber]
FROM Shipping
Where Ship_DT = '2008-05-12' and status != 'voided'
GROUP BY Cust_DB_Shipment_Key
) dt
ON (stuff(t1.Cust_DB_Shipment_Key,1,5,'')) =
(stuff(dt.Cust_DB_Shipment_Key,1,5,''))
Where Ship_DT = '2008-05-12' and status != 'voided'
----------------------------------------------------------------------
SELECT CASE when
Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(Cust_DB_Shipment_Key,1,5,''))
End,
Airway_Bill_No,
Shipper_Reference,
Service_Name,
Chargeable_Weight,
Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping
s JOIN Shipping..Distinct_Service_by_SAS_Code d ON s.External_Product_CD = d.SAS_Code
Where Ship_DT = '2008-05-12' and status != 'voided'
I'm looking for a way to combine the following 2 sets of code into one select statement. They're similar in that they reference the same tables, but they have different conditionals:
Code: SELECT TABLE_01.Date , TABLE_01.State , TABLE_01.City , ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth INTO TABLE_FINAL_01 FROM TABLE_01 LEFT OUTER JOIN TABLE_02 A
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results Table B Contains an Icon, this image is displayed in the results Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA UNION ALL Select title, description, icon as image from tableB UNION ALL title, description, ( inner Join SELECT top(1) from imageTableC where imagetableC.FK = tableC.PK) as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps. at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)??? Thanks in advance.
Hi all, I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ? SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1 FROM dbo.Orders WHERE (ShipVia = 1) GROUP BY EmployeeID ORDER BY EmployeeID
Table 1 Results EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29 SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2 FROM dbo.Orders WHERE (ShipVia = 2) GROUP BY EmployeeID ORDER BY EmployeeID Table 2 results EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19 Table 3 the desired result: EmployeeID CountShipVia1 CountShipVia2
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer AS DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest ( Zip, UpsRegion, UspsRegion ) SELECT CMRC_ZipRegionUps.Zip, CMRC_ZipRegionUps.UpsRegion, CMRC_ZipRegionUsps.UspsRegion FROM CMRC_ZipRegion, CMRC_ZipRegionUsps GO
Is there any way to do this? Or do I have to manually enter all the entries?
I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two). insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName" insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)" I have tried: Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName = @uName VALUES (uID, @eventID, @comment) Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated! Thanks in advance.
I now would like to have a SQL query which gives me the number of unique object and reference combinations, like this:
a b c
d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.
Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:
These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.
I hope that's clear; if not, maybe the SQL will make it clearer:
SELECT [tblTickets].[id] AS TicketId, SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal FROM [tblOrders] INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id] INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblTickets].[id]
SELECT [tblCombinations_Tickets].[ticket_id] AS cTicketId, SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal FROM [tblOrders] INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id] INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblCombinations_Tickets].[ticket_id]
I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.
I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.
This has got to be laughably simple. But I'm stuck :( Can anyone help?
Not concatenation, more... err.. I don't know what you'd call it.
SELECT DISTINCT [C01241 Opened].[Col004] AS OpenerEmail, [C01241 External Data].[DMCEMAIL] AS ExternalDataEmail, [C01241 Internal Data].[Col15] AS InternalDataEmail FROM [C01241 Opened] LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004] LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]
(Apologies for the table/col names, this is all very temporary)
So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.
At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.
What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.
I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production FROM dbo.job left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid WHERE dbo.Job.CompanyJobId = 3505048 and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA') GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
Result
3505048 SR 434 T-5201SR 434 T-5201 2007-10-11 16:36:45.647 Y 1314.26 (material qty) 1569.26 (production qty)
(where 1314.26 is sum material and 1569.26 is production)
Query 2
selectsum(EmployeeLaborEvent.Hours) as hours fromdbo.job left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid WHERE dbo.Job.CompanyJobId = 3505048
Result:
1647.50 (which are sum of hours, this figure is correct)
Now I try to merge query 2 into Query 1 like this:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production, sum(EmployeeLaborEvent.Hours) as hours FROM dbo.job left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048 and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA') GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
When I run the query the result is:
3505048 SR 434 T-5201SR 434 T-5201 2007-10-11 16:36:45.647 Y 1314.26(material) 1569.26 (production) NULL (hours)
The material and production stay the same (and is correct). Hours are wrong.
What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??
Hello, I have two tables that I would like to combine but with some rules. Table 1 has two columns with Actual Time and Actual People Table 2 has two columns also with Planned Time and Planned People
I would like to combine these two tables if the actual time is not more than 30 minutes from a planned time If I use one of the planned times, i would not want to show it again, even if there is another actual time that is not more than 30 minutes of it.
For ex. Planned Time has 20:00,23:00 Planned People has 2,5
Actual Time has 19:00,19:30,19:45,21:15 Actual People has 5,2,5,3
Output should be like Planned_Time,Actual_Time,Planned_People,Actual_People ,19:00,,5 20:00,19:30,2,2 ,19:45,,5 ,21:15,,3 23:00,,5,
I can do this in asp but if this can be done in sql it would be better. Any ideas appreciated. Thanks.
this is my query: tabel1: select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1' if i run this query i will get output like this: userid user_name password role_code expiry_date created_date 1 karthik karthik AD 01/17/20082007-01-24 active 0:00:00.000 1
i have another table which has the following records
tabel 2: select * from code master
codename codedescription AD admin sp supervisor
so in the first query i need the output as like tithe following:
is it possible to combine two tables(not related with each other) as onde like we put them together with our hands physically.
this is what i want;here's the two tables to be combined:
table-a ______________table-b 15 _ a ____________ ny _____ arena 25 _ d ____________ fg_____ metus 35 _ f ____________ 45 _ f ____________ these two tables above will become table-c like below;
table-c 15__arena 25__metus 35__null 45__null but a warning , no relation between the tables and, row counts will not be equal anytime one of them may have more rows then the other ,ihe tried many join methods but gave me allways the lots of results more than i want ,please anyone can help? is it possible to put two tables physically like we put them together with our hands ?
Hi all, I am working from a database containing sets of questions and answers relating to maternity episodes. The answer tables are broken up into two tables, freetext answers and standard drop-down answers. The questions relating to these answers are held in the same table, MAT_QUESTIONS. When I want to view the freetext answers and questions for a certain episode (based on incidentid), the following code works.. SELECT P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, FA.ANSWERTEXT FROM MAT_FREEANSWERS FA, MAT_QUESTIONS QS, MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P WHERE FA.QUESTIONID=QS.ID AND FA.INCIDENTID=I.INCIDENTID AND I.INCIDENTID=D.INCIDENTID AND D.MOTHER_PAS_NO=P.PAS_NO AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59' AND QS.QUESTIONNAIREID=2 AND FA.INCIDENTID=4501
and if I want to view the answers to the standard drop down questions, I use the following code...
select P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, A.ANSWER from MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P, MAT_QUESTIONNAIRE Q, MAT_QUESTIONNAIREINCIDENTS QI, MAT_QUESTIONS QS, MAT_ANSWERS A, MAT_INCIDENTANSWERS IA WHERE I.INCIDENTID=D.INCIDENTID AND I.INCIDENTID=QI.INCIDENTID AND D.INCIDENTID=IA.INCIDENTID AND D.MOTHER_PAS_NO=P.PAS_NO AND Q.ID=QI.QUESTIONNAIREID AND QI.QUESTIONNAIREID=QS.QUESTIONNAIREID AND QS.ID=A.QUESTIONID AND A.ID=IA.ANSWERID AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59' AND Q.ID=2 AND I.INCIDENTID=4501
However, I would like to join the two together, as I would like to see a list of all questions and answers posed to a particular patient. But, as there are so many tables in common, I'm having difficulty doing this.
When the value in the second column is null, I need to check the value in the 3th table.
The join between the first and the thirth is:
select C.issueID, min(D.project) as Project from issuerequest as C left outer join ProductCustomer on (C.customer = D.customerID) and (C.product=D.produktID) where actuellproduct='1' group by C.issueID
this gives a result as: 100 | P002 100 | P003 101 | P004 102 | P002 103 | null ...
What I actually want is just 2 columns with in the first IssueID and in the second Project and no duplicate data. Everything (except null) is more important then the same value in column 3.
100 | P002 101 | P003 102 | P002 103 | null
I've tried with except, but can't get all duplicate data out.
What's the syntax for combining INTO and UNION clauses?What I want to do is:SELECT blahINTO newtableFROM oldtable1WHERE <conditions>UNIONSELECT blahINTO newtableFROM oldtable2WHERE <conditions>DTS doesn't seem to like my syntax. Where should the INTO(s) really go?ThanksAndy
