That's about it (subject line), I have used UNION and now I want to select DISCTINCT from that resultset
Code:
SELECT c1 FROM t1
UNION
SELECT c2 FROM t2
That code gives me half of what I want.
I would like a list of the unique results.
I know I could use a TempTable and do the DISTINCT on that, but I'm hoping there is a more elegant way.
EDIT: The following code gives me the result I want:
Code:
Dear All, please let me know which combination is better one.
here in our application, there is union as well as distinct.
now i'm replacing the the union and distinct with the suggested replacement by you all.
1) using union and removing distinct. 2) using distinct and union all. now my doubt is ..is there any other possibility for these? actually union is also not suggested generally.
please help me in this regard.
Vinod Even you learn 1%, Learn it with 100% confidence.
when i try to run this following query, i get an error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
The ntext data type cannot be selected as DISTINCT because it is not comparable.
/resultados_termo.asp, line 176
the query:
select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' )
when i use UNION ALL, i get repeated rows. i need the select distinct on it.
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows. thanks for any suggestions...
~ select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nowo where nowo.wasrst <='40' union select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nopo where nopo.wasrst <='499'
Hi all I'm new to SQL Server 2005 and its queries. I've created a DB that stores bonusses. For example, a person goes out with usually 4 other people, and does their work. The first person is the engineer, the second is the team leader, and the third and forth is the helpers. According to the number of notes they capture for that day, they get a bonus. if 3 notes has been done for the day, the enigeer gets 100 bucks per note, aka 300 bucks - 3 * 100 = 300. the team leader gets half of it, 150, and the helpers get each half of that, 75 and 75. in a select query i did the match/calculation now i want to add everything up to get a total, 300+150+75+75 - how does one do it, and write it to a table? Note: The amount of people can change, the role of the person can change, so a helper can be a team leader or engineer as well, and the amount also changes depending on performance.DECLARE @BonusID int; SET @BonusID = '1';
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, b.Notes * a.Amount AS Total, bd.DateModified FROM tblBonusDetails AS bd INNER JOIN tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN tblUsers AS u ON bd.UserID = u.UserID INNER JOIN tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN tblRoles AS r ON bd.RoleID = r.RoleID WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Surveyor')
UNION
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, (b.Notes * a.Amount)/2 AS Total, bd.DateModified FROM tblBonusDetails AS bd INNER JOIN tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN tblUsers AS u ON bd.UserID = u.UserID INNER JOIN tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN tblRoles AS r ON bd.RoleID = r.RoleID WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Team Leader')
UNION
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, ((b.Notes * a.Amount)/2)/2 AS Total, bd.DateModified FROM tblBonusDetails AS bd INNER JOIN tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN tblUsers AS u ON bd.UserID = u.UserID INNER JOIN tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN tblRoles AS r ON bd.RoleID = r.RoleID WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Helper')
ORDER BY Total DESC
BonusDetailID BonusID Name Surname Role Total DateModified
I want to include product added date and time in my querry but getting this error "The number of columns in the two selected tables or queries of a union query do not match".
Code:
SELECT Products.*,ProdPics.* FROM Products INNER JOIN ProdPics ON Products.ItemID=ProdPics.ItemID WHERE Products.ItemID = 4 UNION SELECT Date, Time FROM History WHERE ProdID = 4
Using SQL 2000...tblCustomer:CustomerID intCompanyName varchar(20)HasRetailStores bitHasWholesaleStores bitHasOtherStores bittblInvoiceMessages:MessageID intMessageText varchar(100)CustomerID intAllRetailStores bitAllWholesaleStores bitAllOtherStores bitAllStores bitIsActive bitThe Invoice Messages are text blocks which will be added to invoicesgoing out to customers. A customer can have Retail stores, Wholesalestores, and/or Other Stores. The messages can go to only thosecustomers with a specific type of store, or all customers, or to aspecific customer. It is important to note that a customer can have 1,2 or all 3 types of stores. Here are a couple of sample entries in theinvoice messages table:tblInovoiceMessages1,For Customers with Retail and Wholesale Stores,0,1,1,0,02,Only For Customer # 10,10,0,0,0,0....Attempt #1 (IF SELECT UNION SELECT)IF (SELECT TC.HasRetailDestinationsFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)UNIONSELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)Attempt #1 checks if the Customer has retail stores, and if it does,returns all messages for Retail Stores. The second Select statementchecks for all messages designated for that particular Customer. I useUnion to combine the tables (which have identical structures) and itworks great.Attempt #2 (IF SELECT UNION SELECT UNION IF SELECT)IF (SELECT TC.HasRetailStoresFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)UNIONSELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)UNIONIF (SELECT TC.HasWholesaleStoresFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllWholesaleStores = 1)Attempt #2 is the same as Attempt#1 except that I attempt to Unionanother If Select query to the first two queries. This attemptgenerates:Server: Msg 156, Level 15, State 1, Line 12Incorrect syntax near the keyword 'IF'.I have tested each individual If Select statement, and they all returnproper results. However, anytime I attempt to Union more than 1 IfSelect statement together, I get the Msg 156 error. Is there somelimitation that I am not aware of?
can someone help me how can i access datas using union? or show my data in the gridview.....pls..... coz i have 3 tables... i need to output the datas of the 3 tables in 1 gridview. tables are: TT0001,TM0011,TM0001 TT0001 has syain_id(PK),time_in,time_out,year(PK),month(PK),day(PK) TM0011 has office_name,office_id(PK) TM0001 has syain_id(PK) office_id(PK),empl_date the scenario is: i have a combo box for office_name, and a textbox for imputting the date( 2007/01/23).when i click the button OK. the time_in,time_out and syain_name of the person who is present in the choosen date(ex. 2007/01/23) will be shown in my gridview_info. i already have some codes but it still confuse me and has many errors.... my code: GridView_info.Visible = True '//for odbc Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft® Visual Studio® 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes" Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn) 'Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString) MyConn.Open() '//parsing 2 Dim MyString As String = TextBox_date.Text Dim MyDateTime As DateTime = DateTime.Parse(MyString) Console.WriteLine(MyDateTime) Dim stringQuery2 As String = "SELECT * from tempo_db" Dim SQLcommand2 As New Odbc.OdbcCommand("Create table tempo_db ( syain_name char(40),year char(4), month char(2), day char(2), in_hh int, in_mi int, out_hh int, out_mi int )", MyConn) SQLcommand2.ExecuteNonQuery() Dim SQLcommand1 As New Odbc.OdbcCommand("Drop table tempo_db", MyConn) SQLcommand1.ExecuteNonQuery() Dim da As New Odbc.OdbcDataAdapter("Select TT0001.Year,TT0001.Month,TT0001.Day,TT0001.in_hh,TT0001.in_mi,TT0001.out_hh,TT0001.out_min where TT0001.Year =" + MyString + " and TT0001.Month =" + MyString + " and TT0001.Day =" + MyString + " and TT0001.syain_id =" + TM0001.syain_id + " Union Select TM0001.syain_name where TM0001.syain_id =" + TT0001.syain_id + " Union Select TM0011.office_name where TM0011.office_id =" + TM0001.office_id + " Dim ds As New DataSet() Dim foundrow As DataRow Dim ds2 As New DataSet 'Dim temp_data_table As New DataTable GridView_info.DataSource = ds da.Fill(ds, "TT0001") Dim sqldataadapter2 As New Odbc.OdbcDataAdapter(stringQuery2, MyConn) sqldataadapter2.Fill(ds2, "tempo_db") Dim date_ctr As Integer date_ctr = 1 While date_ctr <= Date.DaysInMonth(Now.Year, Now.Month) ds.Tables(0).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Year")} 'ds.Tables(1).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Month")} 'ds.Tables(2).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Day")} foundrow = ds.Tables(0).Rows.Find(date_ctr) 'foundrow = ds.Tables(1).Rows.Find(date_ctr) 'foundrow = ds.Tables(2).Rows.Find(date_ctr) Dim in_hh, in_mi, out_hh, out_mi As String If foundrow IsNot Nothing Then in_hh = foundrow.Item("in_hh") in_mi = foundrow.Item("in_mi") out_hh = foundrow.Item("out_hh") out_mi = foundrow.Item("out_mi") End If End While GridView_info.DataSource = ds2 GridView_info.DataBind() MyConn.Close() End Sub
/// ps: im having problem with my select statements..... can someone help me analyze what i had written... and what is my mistake...
Hi, I have discovered something weird. I prepared a dataset that consists of a table adapter which has a select command of type stored proc. My stored procedure performs a select on a table1 and then table2 using UNION. My stored proc is running perfectly in the SQL Management studio (SQL2005) - no questions. In Visual Studio when I test my dataset querying my tableadapter I get a result that is not just a UNION but a join of empty columns (number of empty columns = number of columns from one of my tables) and then the result of select statement from my stored proc. And then my asp.net code fails as well because my gridView is expecting only 3 columns but instead I am getting 6 (3 empty + 3 those I was expecting in the first place.)
hi all....im having problem with union selection query....this is the situation...i have a details view with data source from two table in the sql server...both select query has same query string parameter,the thing is i dont know how to display the data from both table in the column in details view...here i paste the code.... 1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> 2 3 <script runat="server"> 4 5 6 </script> 7 8 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> 9 <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" 10 CellPadding="4" DataKeyNames="programID" DataSourceID="programdetailsSqlDataSource" 11 ForeColor="#333333" GridLines="None" Height="50px" Width="272px"> 12 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> 13 <CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" /> 14 <EditRowStyle BackColor="#2461BF" /> 15 <RowStyle BackColor="#EFF3FB" /> 16 <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> 17 <Fields> 18 <asp:BoundField DataField="namaprogram" HeaderText="Nama Program" SortExpression="namaprogram" /> 19 <asp:BoundField DataField="lokasi" HeaderText="Lokasi" SortExpression="lokasi" /> 20 <asp:BoundField DataField="tarikh" DataFormatString="{0:dd/mm/yyyy}" HeaderText="Tarikh" 21 HtmlEncode="False" SortExpression="tarikh" /> 22 <asp:BoundField DataField="kapasiti" HeaderText="Kapasiti" SortExpression="kapasiti" /> 23 <asp:HyperLinkField DataTextField="nama" HeaderText="Nama Staff" NavigateUrl="~/staffdetail.aspx?staffID={0}" DataNavigateUrlFields="staffID" /> 24 </Fields> 25 <FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" /> 26 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> 27 <AlternatingRowStyle BackColor="White" /> 28 </asp:DetailsView> 29 30 <asp:SqlDataSource ID="programdetailsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:blksConnectionString %>" 31 SelectCommand="SELECT * FROM [program] UNION SELECT staffID,nama,programID FROM [staff] WHERE ([programID] = @programID)"> 32 <SelectParameters> 33 <asp:QueryStringParameter Name="programID" QueryStringField="programID" Type="Int32" /> 34 </SelectParameters> 35 </asp:SqlDataSource> 36 <br /> 37 </asp:Content> and here is the errors: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
i have 2 selects:select * from view_veiculos where nome_marc like '%fiat%' and ano='2001'union select * from view_veiculos where nome_marc like '%fiat%'when i execute it on sql server, i get the following results:id 1 _______ ano 2004id 2 _______ ano 2001the row with ano 2004 is before the row with ano 2001the problem is that id like it to be ordered following the select order, which means that 2001 should be displayed before 2004,like that:id 1 _______ ano 2001id 2 _______ ano 2004all the results from the first select from the query need to be placed before the results from the second query.how can i make it ?thanks for all
I have three tables "UNIONED" and I want the this inserted into a table.
INSERT INTO mytable (A, B, C, D, E) SELECT A, B, C, D, E FROM (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2) UNION (SELECT A, B, C, D, E FROM a_lot_of_parts) GROUP BY A,B,C,D,E
This part alone works just like I want it:
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2) UNION (SELECT A, B, C, D, E FROM a_lot_of_parts)
I just want it inserted inte stated columns in my table.
I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...
I have 3 tables One table is the order Table, Bill to table and ship to table I have to Views created as followed
This query uses the Ship to table to pull the ship to information to the shipping system. SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);
This query uss the Bill to as the ship to inforamtion SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;
I need this infroamtion in one table which I have done in the UNION statement as followed: SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO) UNION ALL SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;
Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.
Hi,I'm trying to get the count of rows from the union of several tables.My code is:select count(*) from (select * from #AdvSearch_Mainunionselect * from #AdvSearch_Atty)This will not get past the syntax check saying that the error occurs onthe final closing ")".Can someone tell me how to correctly write this?Thanks,Glen--------------------------Numbers 6:24-26----------------------------------------------------Numbers 6:24-26--------------------------*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this:
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _ "FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _ "ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _ "UNION ALL" & _ "SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _ "FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _ "ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1"
This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption:
Invalid column name 'Towing'.
Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!
How can I write a query to return the results for a given survey for all members (including members who have not given responses) given the surveyID.
In the [SurveyQuestionMemberReponse] table I record survey results for any members who have answered the survey. However, if a member has not responsed to the survey they will not have a record in this table.
I want to return a list of members with their response to each question in the survey. If a member has not given a response I would like to indicate they have not responded to the survey and they should still appear in the list.
When I attempt to write a query to UNION the results of a query aimed at gathering all of the results in the [SurveyQuestionMemberReponse] to all of the people in the [Members] table I recieve an error when I include the questionText field in my result set.
The error indicates:
The text data type cannot be selected as DISTINCT because it is not comparable.
Can someone please point me in the right direction. I suspect I am going about this all wrong.
[NOTE] The 'surveyType' in the [Surveys] table indicates which subset of members a given Survey should be available to. For this example let's just assume that every survey should belong to all members.
I have the following stored procedure in SQL 2000 and would like todiplay the database name where data is drawn from. I'm using 4databases db1, db2, db3, db4 which all have the same table (Table1)with identical column names (Surname, GivenNames).CREATE PROCEDURE [dbo].[x_searchwildcard] @varSurname VARChar(25)ASSelect a.Surname, a.GivenNamesFrom [db1]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db2]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db3]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db4]..Table1 As aWhere a.Surname LIKE @varSurname + '%'Order By a.Surname,a.GivenNamesGOI tried the followingSelect a.Surname, a.GivenNames, db_name()However it only gave me the name of the database where the storedprocedure is kept (in my case 'Common')I was hoping it would display results something like the followingSurname GivenNames Database------- ---------- --------Fred Smith db1Freddy Smith db2Fred Smith db3Fred Smithe db3Fred Smith db4Fred Smithye db4Instead I receiveSurname GivenNames Database------- ---------- --------Fred Smith commonFreddy Smith commonFred Smith commonFred Smithe commonFred Smith commonFred Smithye commonAny ideas?ThanksRick
I'm trying to get a UNION of UserIDs from multiple subselects. Theoriginal query--which puts all the found UserIDs into separatecolumns, looks like this:--------------------------------------------------------------------------------SELECTtmp_MY_TABLE_2_3_4.LAST_UPDATED_USER_ID AS UserID_1MY_DB_1.MY_TABLE_5.LAST_UPDATED_USER_ID AS UserID_2MY_DB_1.MY_TABLE_6.LAST_UPDATED_USER_ID AS UserID_3FROM(SELECT MY_DB_1.MY_TABLE_1.PRIMARY_KEY_IDFROM MY_DB_1.MY_TABLE_1WHERE MY_DB_1.MY_TABLE_1.PRIMARY_KEY_ID = 12345) AS tmpMY_TABLE_1LEFT JOIN(SELECT MY_DB_2.MY_TABLE_2.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_2.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_2UNIONSELECT MY_DB_2.MY_TABLE_3.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_3.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_3UNIONSELECT MY_DB_2.MY_TABLE_4.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_4.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_4) AS tmp_MY_TABLE_2_3_4ON tmpMY_TABLE_1.PRIMARY_KEY_ID = tmp_MY_TABLE_2_3_4.PRIMARY_KEY_IDLEFT JOINMY_DB_1.MY_TABLE_5 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =MY_TABLE_5.PRIMARY_KEY_IDLEFT JOINMY_DB_1.MY_TABLE_6 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =MY_TABLE_6.PRIMARY_KEY_ID--------------------------------------------------------------------------------As you can see, I'm getting "LAST_UPDATED_USER_ID" from all tables/aliases--but those tables/aliases need to LEFT JOIN to"tmpMY_TABLE_1.PRIMARY_KEY_ID", because I only care about"LAST_UPDATED_USER_ID"'s that are related to my "tmpMY_TABLE_1"records.I tried putting, parenthesis around the whole SQL (aliasing as"tmpAllTables") and putting in the front:--------------------------------------------------------------------------------SELECT tmpAllTables.UserID_1 AS UserID FROM tmpAllTablesUNIONSELECT tmpAllTables.UserID_2 AS UserID FROM tmpAllTablesUNIONSELECT tmpAllTables.UserID_3 AS UserID FROM tmpAllTables--------------------------------------------------------------------------------But that didn't work. Any thoughts?
I'm having trouble creating a temp table out of a select statement that uses multipe union alls.
Here's what I have, I'm trying to get the results of this query into a temp table...
select parent, (select cst_id from co_customer (nolock) where cst_key = Parent) as cst_id, (select cst_name_cp from co_customer (nolock) where cst_key = Parent) as cst_name_cp, (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent) as Parent_Total_assets, sum(own_assets) as Total_child_own_assets
from ( Select parent, Child, (select org_own_assets_ext from dbo.co_organization_ext where org_cst_key_ext = child) as Own_assets
from (Select Cst_key as Child, dbo.return_org_parent(cst_key,0,1) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,1) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,2) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,2) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,3) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,3) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,4) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,4) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,5) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,5) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,6) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,6) is not null union all Select Cst_key as Child, dbo.return_org_parent(cst_key,0,7) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,7) is not null )as c ) as d
group by parent
having sum(own_assets) <> (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent)
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr, FROM plan2008 GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr FROM fsp_auftrag GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
OK I have a Forum on my website make up of 3 tablesTopisThreadsMessageI show a list of the 10 most recent Changed Threads. My Problem is that my Subject field is in the messages Table, IF I link Threads to Messages then try to use Select Disticnt I get mutliple Subject fields as the messsges are not unique (obvisally) So I want to get the top 10 Threads by postdate and link to the Messages table to get the Subject headerAny help? Or questions to explain it better?
Hello Everyone Hopefully someone can help me create a SQL statement for this. I need the ff: fields Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE, Specialty Let say I have a table. Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE. Specialty1000 999 Mike James Plano 75023 Internal Medicine1000 998 Mike James Allen 75021 Internal Medicine3333 700 John Smith Arlington 70081 Dermatologist3333 701 John Smith Dallas 72002 Dermatologist2222 630 Terry Walker Frisco 75001 Optalmologist2222 632 Terry Walker Dallas 76023 Optalmologist4444 454 Tim Johnson San Anontio 72500 Internal Medicine 4444 464 Tim Johnson Frisco 72660 Internal Medicine I want to select only "one" instance of the provider it doesnt matter what is selected either the first address or the second address. It should show Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE. Specialty1000 999 Mike James Plano 75023 Internal Medicine3333 700 John Smith Arlington 70081 Dermatologist2222 632 Terry Walker Dallas 76023 Optalmologist4444 464 Tim Johnson Frisco 72660 Internal Medicine And yes, the table is not Normalized..Is there anyway I could get away with it without having to normalize? Thanks Lorenz
Is their a way to select all items from a table that are not distinct? Meaning, I want to know which items in a column occur more than once. Example: Suppose we have a table with student names, ss# and address. I want to display only records where their is more than one studen with the same name. So for example their could be ten people with the name of "Mike" in a class?
I have a table myTable (ID, Year, Name, Note)data in this table:ID Year Name Note 1 2008 Petter hdjhs2 2008 Nute jfdkfd3 2007 Suna dkfdkf4 2007 Para jfdfjd5 2009 Ute dfdlkf Please help me to Select DISTINCT [Year]]ex:1 2008 Petter hdfdfd3 2007 Suna fdkfdk5 2009 Ute fkdfkdfd Thank!
Hi! I have 4 tables and they have a common column (eg. regionid). These 4 tables have data overlapping with the others. Some data exist in a table but not on the others. What I want to do is to do a select that will display all distinct regionid from these tables. It should be total of all the tables but will suppress any duplicates with the others.
Note that UNION is working but I can't use that. Why ? because UNION is not supported or maybe not working properly with RDB database. I'm doing an appliaction for heterogenous datasource.
Any tips, hints or info will be appreciated. thanks in advance.
SELECT DISTINCT tb2.column20 tb2.column20, tb1.column10, tb2.column21, tb2.column22, tb3.column30 FROM table1 tb1, table2 tb2, table3 tb3 WHERE tb1.column11 = 'P' AND tb2.column23 = 'P' AND tb1.column12 = tb2.column24 AND tb2.column25 = tb3.column31 ORDER BY tb2.column20
Its supposed to return only the distinct entries in tb2.column20
Can you have "Select Distinct" in Union Query,because that is what I am trying to do and this is the error message I get.
"The text, ntext, or image data type cannot be selected as DISTINCT."
I would need to do that because i have duplicate records,because these records are getting written into the db when templates are generated and sometimes if they double click it generates two and writes that many results as well, so that is why I was thinking that select distinct would solve my problem.
Thanks for your help
This is the query in question:
SELECT Distinct 'O' AS Origin, a.RecordID, a.RelocateID, a.SupplierID, a.DateIn, a.DateOut, a.NoOfDays, a.AgreeAmt, a.PaymentMethod, a.AccomType, a.Reason, a.InvRecvd, a.RelocateeTempAccomTS, a.BedConfiguration, a.NumberOfPax, a.AdditionalItems, a.Currency, a.TotalAmount, a.EnteredBy, a.LastModifiedBy, a.ReferenceNumber, a.Location, a.Comments, a.ArrivalTime, a.PONumber,CommissionRate, ISNULL ((SELECT TOP 1 ExchangeRateToUSD FROM luCurrencyExchangeRates c WHERE a.Currency = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL ((SELECT TOP 1 ExchangeRateToUSD FROM luCurrencyExchangeRates c WHERE 'AUD' = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS AUDExchangeRate, a.WhenConfirmed, e.RequestID AS RequestID, e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause, dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc FROM dbo.RelocateeTempAccom a LEFT OUTER JOIN dbo.luDecisionMaker ON a.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT OUTER JOIN dbo.viewZYesno ON a.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN dbo.docRequests e ON '{RelocateeTempAccom.RecordID}=' + CONVERT(VARCHAR a.RecordID) = e.WhereClause WHERE (ISNULL(a.Cancelled, 0) = 0)
UNION ALL
SELECT Distinct 'D' AS Origin, RecordID, RelocateID, DTASupplierID AS SupplierID, DTADateIn AS DateIn, DTADateOut AS DateOut, DTANoOfDays AS NoOfDays, DTAAgreeAmt AS AgreeAmt, DTAPaymentMethod AS PaymentMethod, DTAAccomType AS AccomType, Reason, InvRecvd, RelocateeDTATS AS RelocateeTempAccomTS, BedConfiguration, NumberOfPax, AdditionalItems, Currency, DailyTotal AS TotalAmount, EnteredBy, LastModifiedBy, ReferenceNumber, Location, Comments, ArrivalTime, PONumber,CommissionRate, ISNULL ((SELECT TOP 1 ExchangeRateToUSD FROM luCurrencyExchangeRates d WHERE b.Currency = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL ((SELECT TOP 1 ExchangeRateToUSD FROM luCurrencyExchangeRates d WHERE 'AUD' = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS AUDExchangeRate, WhenConfirmed, e.RequestID AS RequestID, e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause, dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc FROM dbo.RelocateeDTA b LEFT JOIN dbo.luDecisionMaker ON b.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT JOIN dbo.viewZYesno ON b.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN dbo.docRequests e ON '{RelocateeDTA.RecordID}=' + CONVERT(VARCHAR, b.RecordID) = e.WhereClause WHERE ISNULL(Cancelled, 0) = 0
