Help with SQL Query - 'Has ever had'

1

Hi All,

hoping someone can help...

If i have a table (MyTracks), that has the columns "Track_position", "Name" and "Title"

and in "Track_position" I have the values, 1, 1, 2, 3

in Name I have, "James", "James","James","Sarah"

In Title i have, "Title1", "Title2","Title3","Title4"

My simple bit of SQL is...

Select * FROM [MyTracks]

but, i ONLY want to return people that have ever had a track position of 1

so, basically I would like it to...

Select * FROM [MyTracks]

WHERE Name (has ever had) Track_position EQUAL to 1

hope that makes sense? sorry, i'm very new to SQL :slight_smile:

thank you in advance for your help!

1 Like
2

hi

here it is .. :slight_smile: :slight_smile:

drop create data ... 
USE TEMPDB  
GO 


/******************************************************************/
-- drop all tables tempdb 

DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' Drop table ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql

/******************************************************************/


/* ------------------------------------------------------------   */
-- create tables tempdb 
-- int varchar(100) date datetime decimal(10,2) 
/*
create table #data 
(

)
go
*/

create table #MyTracks 
(
Track_position int ,
Name varchar(100),
Title varchar(100)
)
go 
/* ------------------------------------------------------------   */


/*****************************************************************/
-- insert data tables tempdb 
-- insert into #data select 1 

insert into #MyTracks select 1, 'James', 'Title1'
insert into #MyTracks select 1, 'James', 'Title2'
insert into #MyTracks select 2, 'James', 'Title3'
insert into #MyTracks select 3, 'Sarah', 'Title4'
go
/*****************************************************************/


/* ------------------------------------------------------------   */
-- select all tables tempdb 

DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' SELECT * FROM  ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql

/* ------------------------------------------------------------   */
SQL .. 
/******************************************************************/
-- Query Solution 

select * from #MyTracks WHERE Track_position = 1 

/******************************************************************/

image
image.png388×586 5.64 KB

3 
SELECT MT.*
FROM MyTracks MT
WHERE Name IN (
    SELECT DISTINCT Name
    FROM MyTracks
    WHERE Track_position = 1
)
4

I like the 'has ever had'.

The OP has a referential integrity problem, but that assumes referential data. If we assume Associative data, the idea of an algorithm to represent 'has ever had' becomes a function, does it not?

Could ScottPletcher's example (and the example prior) be used to create a function, making SQL more English-like?