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

T-SQL (SS2K8) :: Selecting Data From Table With Multiple Conditions On Multiple Columns

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

T-SQL (SS2K8) :: Selecting Top 1 From Multiple Ranges?

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

Selecting Multiple Records From Multiple Tables

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

Selecting From Multiple Tables

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

Selecting On Multiple Tables

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

Distinct With Multiple Tables

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

Selecting A Column From Multiple Tables

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

Transact SQL :: Distinct By One Column By Selecting Multiple Column?

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

Selecting From Multiple Tables Avoiding Duplicates

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

Transact SQL :: Distinct Values From Multiple Tables?

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

Database Design Selecting Multiple Different Child Tables

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

SQL Server 2012 :: Selecting Records From Multiple Tables?

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

Microsoft SQL Server 2005. Selecting Multple Columns From Multiple Tables

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

SQL Server 2012 :: Row Counts Based On Distinct Values From Multiple Tables

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

T-SQL (SS2K8) :: Compare Multiple Column Between Two Tables

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

T-SQL (SS2K8) :: INSERT Data From Multiple Tables

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

T-SQL (SS2K8) :: Get List Of All The Tables Used In Multiple Scripts

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

T-SQL (SS2K8) :: Merge Statement MULTIPLE INSERT Into Different Tables

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

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

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

Selecting Distinct

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

Selecting Distinct Records

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

Selecting Distinct Product

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

Selecting Distinct Rows

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

SELECTing A DISTINCT COUNT?

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

Selecting Distinct Rows ??

Oct 27, 1998

View 3 Replies View Related

Selecting A Distinct Row Query

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

Selecting Distinct Records Through CTE

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

Selecting Distinct Records Quickly

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

Selecting Distinct Data Using Group By

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

Selecting Distinct On 3 Columns In Ms Sql Server

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

Help With A Query (Selecting Distinct Rows As Well As How To Use NOW())

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

Selecting Distinct Top 3 Rows From Database Using Join

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







Copyrights 2005-15 www.BigResource.com, All rights reserved