T-SQL (SS2K8) :: Selecting Distinct From Multiple Tables
Jun 4, 2014
I have 3 tables:
News:
bigint NewId
nvarchar NewTitle
datetime NewDate
nvarchar NewBrief
--------------------------
Category:
int CatId
nvarchar CatName
--------------------------
NewsRelCategory:
bigint Id
int CategoryIdFk
bigint NewsIdFk
--------------------------
I want to select NewId, NewDate and Distinct NewTitle
I tried this but NewTitle doesn't distinct:
SELECT
FROM dbo.Category INNER JOIN
NewsRelCategory ON dbo.Category.CatId = NewsRelCategory.NrcCategoryIdFk INNER JOIN
dbo.News ON NewsRelCategory.NrcNewsIdFk = dbo.News.NewId
View 9 Replies
ADVERTISEMENT
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Mar 28, 2014
I have data like the following:
ID COUNTER DATA
1 10 BLAH
1 20 BLAH
2 10 BLAH
3 10 BLAH
2 20 BLAH
2 30 BLAH
What I want to return is:
1 20 BLAH
2 30 BLAH
3 10 BLAH
I want the top 1, having the highest counter from each ID. This is a highly simplified version of that I am pulling which also is between a date range, but same principle.
IE: SELECT * FROM Table WHERE ID in (SELECT DISTINCT ID FROM Table WHERE Date BETWEEN <date> AND <date>
I'd rather keep it in one statement if possible, but if I have to do it in multiple passes then so be it.
View 6 Replies
View Related
Nov 4, 2004
i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.
ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.
View 1 Replies
View Related
Apr 2, 2007
Hi all.
I'm trying to select from multiple table in one select statement but i'm having problems. Here is the code i'm trying:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[snow_ors_additionalInfoRead]
GO
CREATE PROCEDURE dbo.snow_ors_additionalInfoRead
@Reference int
AS
SELECT
Account.CanTravel,
Account.SEEmployee,
Account.WorkHours,
Account.DrivingLicence,
Account.CriminalConvictions,
Account.CriminalConvictionsDetails1,
Account.CriminalConvictionsDate1,
Account.CriminalConvictionsDetails2,
Account.CriminalConvictionsDate2,
Account.CriminalConvictionsDetails3,
Account.CriminalConvictionsDate3,
Application.VacancyMonitoring,
Application.VacancyMonitoringDetails
FROM Account, Application
WHERE Account.Reference = @Reference AND
Application.reference = @Reference
GO
Any help at all would be great.
thanks people.
View 5 Replies
View Related
Aug 13, 2006
Hi there,I'm new to sql and thus I'm having problems with a specific query which Ihope you guys can help me with.Basicly I have a few tables which I'm trying to do a query on:Table groups contains information about specific groups e.g. "Windows" or"Unix".Table users contains information about specific users e.g. "a", "b" or "c".Table users_groups contain information about group relationship (a user canbe in multiple groups) e.g. (a, Windows), (b, Unix), (a, Unix).In this case user c is ungrouped.Now I'd like to find the users which does belong to group Windows and thosewho do not:select distinct username from users_groups where groupname = "Windows" orderby username asc;This works pretty well for finding users in the specific group. In this casethe result is a.However I'd like to get the opposite result (b and c) but I'm stuck.The problem is that I'd like a list of all users excluding those which arein "Windows"Here is a partial query:select distinct users.username from users left join users_groups onusers.username = users_groups.username where users_groups.username is nullorder by users.username asc;This only gives me those users who are not grouped at all. This mean thatuser b is not in those results.Please advise.Thanks in advance.-- Henrik
View 2 Replies
View Related
Apr 4, 2007
hi friends
i have a query like this
query1="select co.*, ct.*, from contacts co, enquiries en, contactPersonType ct where co.co_contactId=en.en_contactId and co.co_contactType=ct.ct_contactTypeId"
i want to display only the contact persons details who made enquires.
this gives me duplicate records. im not displaying enquiry details. how can i use distinct with this?
thank you
neon
View 8 Replies
View Related
Feb 19, 2004
Hello,
Is it possible to get a list of rows from Multiple tables which have the same Column Name. I have 3 tables which comtain similar info
and I want to get a list of Names
the structure is
ID;Name;Address;Phone No.
I was thinking something along the lines of SELECT Name FROM TABLE1,TABLE2, TABLE3
But this does not work.
Is there a nice way of doing this with SQL or should I do code outside the SQL DB
View 4 Replies
View Related
Jul 17, 2015
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1
I supposed to get last row as
cc Zz bab
Instead I am getting
cc Zz rrr
which is wrong
View 8 Replies
View Related
Sep 6, 2006
Hi
I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.
I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?
Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!!
Thanks!
View 7 Replies
View Related
Nov 2, 2015
best way to go about this. My query works great and displays the information I require.
EG all demographic for ED, IP & OP. However if a patient has a record in each of the tables then I get the same info multiple times.
What I'm trying to do is a Select Distint against the entire query (below).
eg Select Distinct * from all below.
DECLARE @StartDate datetime = '01 oct 2015';
DECLARE @EndDate datetime = '30 OCT 2015';
--***OUTPATIENT DQ QUERY***--
SELECT distinct
HEYNo,
NHSNo2,
NHSNo1,
[code]....
View 3 Replies
View Related
Sep 19, 2000
I want to have a linking table say for example we call this a claim. Based on the claim number you need to relate to one of say 6 different types of claims. The types of claims related to their own individual parent table. (individual because each type of claim tracks completely different information) does anyone have an idea on how to set this up?
Sample Structure
table = Claim
Field 1 = ClaimTypeA_ID
Field 2 = ClaimTypeB_ID
Field 3 = ClaimTypeC_ID
Field 4 = ClaimTypeD_ID
Field 5 = ClaimTypeE_ID
Field 6 = ClaimTypeF_ID
The six field relate to the 6 different tables ID.
If I do this how do I store the data? put 0's in each of the claim types that are not used???
Any suggestions would be appreciated.
View 2 Replies
View Related
Jul 1, 2015
i have this query in a proc
declare @bu_id INT,
@CurCaptureDate DATETIME,
@user_id INT,
@col_name VARCHAR(100),
@sort_order VARCHAR(4),
@CityPair_ID INT=NULL,
[code]....
where @reasons and @departure_code can be multiple.
View 2 Replies
View Related
Mar 23, 2008
Im just curious how i would take multiple columns from multiple tables.... would it be something like this ???
table: Products COLUMNS ProductName, ProductID
table: Categorys COLUMNS CategoryName, CategoryID,ProductID
SELECT Products.ProductName, Categorys.CategoryName,Products.ProductID,Categorys.CategoryID,Categorys.ProductID
FROM Categorys, Tables
WHERE Products.ProductID = Categorys.ProductID
View 1 Replies
View Related
Jan 15, 2015
I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now
Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
View 9 Replies
View Related
Aug 14, 2014
I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.In Below case I want output as: below two records only.
'test1', 'need this record', 25, {d '1901-01-01'}
'test3', 'some longer value', 23, {d '1900-01-01'}
declare @table1 table (
col1 varchar(10) not null,
col2 varchar(200) null,
col3 int not null,
[code]....
View 2 Replies
View Related
Dec 1, 2014
I have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables.
I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.
insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT)
select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID
from SALES_DETAIL, CUSTOMER
where SALES_HEADER.sales_id = CUSTOMER.cust_id
group by sales_detail.SALES_ID, CUSTOMER.cust_id;
It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound. How to insert from multiple tables when there are no primary / foreign keys & relationships.
View 3 Replies
View Related
Aug 28, 2015
I am trying to figure out an automated way to get a list of all the tables used in a set of 275 T-SQL scripts. i do not want to go through each script manually, look at all the from clauses and write down the tables used.
I have managed to get all the 275 scripts into a table on SQL Server 2008 R2 database. one column per each script, so there are 275 rows in the table.
FYI: each query can have multiple result sets, so multiple from clauses can exist.
For Example:
Query 1 = SELECT * FROM Table1 a
INNER JOIN Table2 b ON b.Field = a.Field
LEFT OUTER JOIN Table3 c ON c.Filed = b.Field
UNION ALL
SELECT * FROM Table4 a
INNER JOIN Table5 b ON b.Field = a.Field
LEFT OUTER JOIN Table6 c ON c.Filed = b.Field
Desired Output = Table1, Table2, Table3, Table4, Table5, Table6
View 9 Replies
View Related
Jul 23, 2014
Can we insert into multiple table using merge statement?I'm using SQL Server 2008 R2 and below is my MERGE query...
-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.
WITH Cont as
( Select ContactID from Contact where ContactID=@ContactID)
MERGE Employee as NewEmp
Using Cont as con
[code]...
View 2 Replies
View Related
Jul 6, 2007
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
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.
Any ideas?
View 2 Replies
View Related
Jul 18, 2007
Hi, This is quite an obvious problem, but for some reason I can't think through the solution. I have two columns, a datetime (datecreated) and an id (FK) othertableid (id)what I would like to be able to do is select a list of id and datecreated, but only chose the latest row for each id.So I guess it's a bit like a distinct on the id column, but ensuring the date returned is the top date. Please help
View 1 Replies
View Related
Sep 1, 2007
Hi,
I'm just wondering if someone can help me with some SQL syntax stuff.I want to take this sql statement:
"SELECT TOP 50 tblProfile.chName, tblProfile.intCount FROM tblProfile, tblLinks WHERE (tblLinks.MemberID = tblProfile.MemberID) ORDER BY tblLinks.dtDateAdded DESC;"
and select only unique "chName's" records
View 9 Replies
View Related
Sep 6, 2007
I know "select distinct ProductId from Product " selects one product only one time how can i apply the same logic in a query like give bellow SELECT I.QuoteRequestItemId, I.ProductId ,P.StorePartNumber from QuoteRequestItem I left join Product P on I.ProductId = P.ProductId ie product with same productId should be considered only oncethere should not be more than one row with same productId
View 2 Replies
View Related
Jun 11, 2008
Hi,
I want to select the 8 most saled products from large orders table... the problem is that when i use the "distinct" sentence (something like this- "SELECT TOP 8 distinct id, products, productid FROM tbl_orders ORDER BY id") I get back the distinct of any columns.... (and any ID is distinct, of course), but if i don't include the id's in the distinct sentence, i can't order by id's.
can i get the last orders, only by distinct product, and not by distinct id, and order them by the id's?
View 17 Replies
View Related
Nov 30, 2003
I have an SQL statement that looks like the following:
SELECT [Docs-Entities].entityID, entityName, COUNT([Docs-DocsEntities].filename) AS numDocs
FROM [Docs-Entities] LEFT JOIN [Docs-DocsEntities]
ON [Docs-Entities].entityID = [Docs-DocsEntities].entityID
GROUP BY [Docs-Entities].entityID, entityName
but the problem is that numDocs (the COUNT) is not returning a distinct count. In the DocEntities table, a particular document can actually have multiple entries with the same entityID so that produces inflated numbers for numDocs. But when I do a SELECT DISTINCT on a particular entityID, the results are less and don't match the numDocs number because I only need to list the document one time. This is not a huge issue, but it looks bad on my site.
Is there a way that I can make COUNT count distinctly?
Thanks for the help and I hope I worded that cllearly...
View 2 Replies
View Related
May 13, 2004
I am trying to append data from a source table to a destination table, the only difference between the tables is that a primary key has been established on the destination table. Problem is, the source table has a handful of duplicate values in the column that corresponds to primary on destination table; so when I run the query I get a primary key conflict. What I'd like to do is select distinct values from the source table to avoid the conflict, but am having trouble getting it to run. Here is the statement-
INSERT INTO cust_master
SELECT DISTINCT cust_master_temp.*
FROM cust_mast_temp LEFT OUTER JOIN
cust_master ON cust_master.temp.CUST_NUMBER = cust_master.CUST_NUMBER
Doesn't seem to be understanding "Distinct"
Any Ideas?
View 2 Replies
View Related
Jan 11, 2013
I have a table contains information related to sales:
SO number Order Date Customer SellingPerson
1001 2012/07/02 ABC Andy
1002 2012/07/02 XYZ Alan
1003 2012/07/02 EFG Almelia
1004 2012/07/02 ABC John
1005 2012/07/02 XYZ Oliver
1006 2012/07/02 HIJ Dorthy
1007 2012/07/02 KLM Andy
1008 2012/07/02 NOP Rowan
1009 2012/07/02 QRS David
1010 2012/07/02 ABC Joey
Now, i want to write a query using CTE that gives me first five distinct customer in result set:
SO number Order Date Customer SellingPerson
1001 2012/07/02 ABC Andy
1002 2012/07/02 XYZ Alan
1003 2012/07/02 EFG Almelia
1006 2012/07/02 HIJ Dorthy
1007 2012/07/02 KLM Andy
I wrote this query :
With t(so_number,order date,customer, SellingPerson)
as
(select top 5 so_number,order date,customer, SellingPerson from t)
select distinct billingcontactperson from t order by so_id
And getting this error:
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 't' does not contain a top-level UNION ALL operator.
View 9 Replies
View Related
Mar 26, 2007
Good day,
I have a table of approximately 10 million rows. The table has 3 field making up the key, namely:
ID, Date, Program
I need to extract all the distinct Program's from the table.
I have don so with:
Select distinct Program from table
This unfortunately takes roughly 2 minutes which is far to long. Is there something I can do to help speed this process up?
Thanks in advance.
View 14 Replies
View Related
Aug 4, 2013
I have an action log table which records when a registrant record was viewed by a compnay employee. I have an sql query like this:
SELECT [ID]
,[RegistrantID]
,[EmployeeID]
,[UserID]
,[CompanyID]
[Code] ....
and data is like this:
IDRegistrantID EmployeeIDUserID CompanyID VacancyID Action ActionDate
17931629515163213NULL 42013-08-04 16:45:40.457
17921629215163213NULL 42013-08-04 16:45:33.003
1791NULL15163213NULL 32013-08-04 16:45:23.660
1790162959162893NULL 42013-08-04 16:45:09.543
[Code] ....
I want to select distinct views to a registrantid record ( the first ones) in one year. if a registrant was viewed 10 tmes a year then it will show only first time it was viewed. If it was viewed 10 times by an employeed in 2 years then it will show first time it was viewed. if it was viewed by 2 employees of same company 10 times in one year then it first time viewed record will be shown. if it was seen 10 times by 2 employees of two different companies in one year then first record of two companies will be shown. do i need to use group by or what ?
View 1 Replies
View Related
Feb 26, 2006
Hi, I think you can do this in oracle but I'm trying to figure a way to do it in ms sql.. I need to select a distinct combination of columns, like so...
select distinct(ItemName,ItemData,FID) from tblSIFups
Does anyone know how to achieve that? I have multiple data where I shouldn't and I don't have any control over the application so I need to clean it this way.
thanks, nicki
View 1 Replies
View Related
Jan 16, 2008
Hi All,
I'm a beginner in SQL and would like some help with writing a query that needs to:
a) Return the latest time that an event happened (along with the event), and also
b) Determine if this event occurred more than 30mins ago.
For example, Table EVENT consists of the following data:
EVENT DateTime,
A 16/1/08, 14:03:55
B 16/1/08, 14:30:27
A 16/1/08, 17:42:18
I would like the results for the first part of query to be:
EVENT DateTime,
A 16/1/08, 17:42:18
B 16/1/08, 14:30:27
I have tried creating a query based off this thread, but for some reason it kept complaining that the EVENT column in Table EVENT didn't exist.
For the part b), I have no clue as to what I should do apart from that I would need to use NOW().
Any help would be appreciated.
MonkeyMark
View 3 Replies
View Related
Jun 25, 2007
Hi guys,
Just trying to select a set of Articles from a SQL Server Database. The Articles all have a Category ID which is stored in another table (as an Article could be in more than one Category). I want to select the Top 3 Articles in a Category. At the moment I have as my SQL;
"SELECT TOP 3 f.ArticleID, f.Heading, f.Summary, f.WrittenDate, f.ArticleURL FROM feedTable f LEFT JOIN Categories c ON f.ArticleID = c.ArticleID WHERE c.CategoryID=" + CategoryID + " AND c.ArticleID<>" + id + " ORDER BY c.CategoryID"
Which seems to work to an extent in that I do get three articles in the same Category appearing. However, there are sometimes duplicates appearing, so I need to incorporate a DISTINCT clause to the above. I'm not sure where to put this in though. Any ideas?
Thanks.
View 6 Replies
View Related