SQL Query to pull data

• tapinderaujla

  SSC Veteran

  Points: 279

  More actions

  May 15, 2018 at 12:11 am

  #330818

  Hi, 
  i have two table named "Objects" and "Attributes". 

  Object Table Structure and Data:
  

  Attributes Table Structure and Data:
  

  I need to write SQL query to return ObjectId and ObjectName from object table and AttributeName, ValueInt,ValueStr columns from Attribute Table Structure.  In Attributes table , there are multiple versions for some attributes, but i need this sql to pull valueInt and ValueStr for the recent versions and pull only those rows from Attributes table where either ValueInt or ValueStr is must have value in it.

  Final Result should look like green rows below:
  

  I need help to write this sql query. 

  Thanks,
  Tapinder

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 15, 2018 at 1:05 am

  #1990453

  Please provide sample data in the form of CREATE TABLE and INSERT statements.  How do you determine what rows contain the "recent versions"?

  John

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  May 15, 2018 at 1:28 am

  #1990457

  tapinderaujla - Tuesday, May 15, 2018 12:11 AM

  Hi, 
  i have two table named "Objects" and "Attributes". 

  Object Table Structure and Data:
  

  Attributes Table Structure and Data:
  

  I need to write SQL query to return ObjectId and ObjectName from object table and AttributeName, ValueInt,ValueStr columns from Attribute Table Structure.  In Attributes table , there are multiple versions for some attributes, but i need this sql to pull valueInt and ValueStr for the recent versions and pull only those rows from Attributes table where either ValueInt or ValueStr is must have value in it.

  Final Result should look like green rows below:
  

  I need help to write this sql query. 

  Thanks,
  Tapinder

  Hi Tapinder,

  
Did you mean this?
d
SELECT
A.OBJECTID,
A.OBJECTNAME,
 B.ATTRIBUTEID,
B.ATTRIBUTENAME,
 B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR
FROM OBJECT A
INNER JOIN 
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT  NULL) OR (B.VALUESTR IS NOT NULL);


  Can you add more details about recent version to give accurate solutions?

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  May 15, 2018 at 1:28 am

  #1990458

  saravanatn - Tuesday, May 15, 2018 1:28 AM

  tapinderaujla - Tuesday, May 15, 2018 12:11 AM

  Hi, 
  i have two table named "Objects" and "Attributes". 

  Object Table Structure and Data:
  

  Attributes Table Structure and Data:
  

  I need to write SQL query to return ObjectId and ObjectName from object table and AttributeName, ValueInt,ValueStr columns from Attribute Table Structure.  In Attributes table , there are multiple versions for some attributes, but i need this sql to pull valueInt and ValueStr for the recent versions and pull only those rows from Attributes table where either ValueInt or ValueStr is must have value in it.

  Final Result should look like green rows below:
  

  I need help to write this sql query. 

  Thanks,
  Tapinder

  Hi Tapinder,

  
Did you mean this?

  SELECT
A.OBJECTID,
A.OBJECTNAME,
 B.ATTRIBUTEID,
B.ATTRIBUTENAME,
 B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR
FROM OBJECT A
INNER JOIN 
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT  NULL) OR (B.VALUESTR IS NOT NULL);


  Can you add more details about recent version to give accurate solutions?

  Saravanan

• tapinderaujla

  SSC Veteran

  Points: 279

  More actions

  May 15, 2018 at 7:45 am

  #1990520

  saravanatn - Tuesday, May 15, 2018 1:28 AM

  saravanatn - Tuesday, May 15, 2018 1:28 AM

  tapinderaujla - Tuesday, May 15, 2018 12:11 AM

  Hi, 
  i have two table named "Objects" and "Attributes". 

  Object Table Structure and Data:
  

  Attributes Table Structure and Data:
  

  I need to write SQL query to return ObjectId and ObjectName from object table and AttributeName, ValueInt,ValueStr columns from Attribute Table Structure.  In Attributes table , there are multiple versions for some attributes, but i need this sql to pull valueInt and ValueStr for the recent versions and pull only those rows from Attributes table where either ValueInt or ValueStr is must have value in it.

  Final Result should look like green rows below:
  

  I need help to write this sql query. 

  Thanks,
  Tapinder

  Hi Tapinder,

  
Did you mean this?

  SELECT
A.OBJECTID,
A.OBJECTNAME,
 B.ATTRIBUTEID,
B.ATTRIBUTENAME,
 B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR
FROM OBJECT A
INNER JOIN 
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT  NULL) OR (B.VALUESTR IS NOT NULL);


  Can you add more details about recent version to give accurate solutions?

  Thanks Saravanatn!

  The Version Number column contains the recent version.

  For example, for AttributeID =8, there are 3 rows. Two rows with Version Number 2 (One have null value second have B1948 value) and the third row have Version Number =1. 
  So the query should return Row where Version Number is recent version =2, and either one of the column ValueInt or ValueStr must have value. 

  

  Thanks,
  Tapinder

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 15, 2018 at 7:52 am

  #1990524

  Tapinder

  In a CTE, use ROW_NUMBER to partition the rows by AttributeID and number them ordered by VersionID DESC.  Then just choose the rows where the row number is 1.

  John

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  May 15, 2018 at 9:27 am

  #1990535

  tapinderaujla - Tuesday, May 15, 2018 7:45 AM

  saravanatn - Tuesday, May 15, 2018 1:28 AM

  saravanatn - Tuesday, May 15, 2018 1:28 AM

  tapinderaujla - Tuesday, May 15, 2018 12:11 AM

  Hi, 
  i have two table named "Objects" and "Attributes". 

  Object Table Structure and Data:
  

  Attributes Table Structure and Data:
  

  I need to write SQL query to return ObjectId and ObjectName from object table and AttributeName, ValueInt,ValueStr columns from Attribute Table Structure.  In Attributes table , there are multiple versions for some attributes, but i need this sql to pull valueInt and ValueStr for the recent versions and pull only those rows from Attributes table where either ValueInt or ValueStr is must have value in it.

  Final Result should look like green rows below:
  

  I need help to write this sql query. 

  Thanks,
  Tapinder

  Hi Tapinder,

  
Did you mean this?

  SELECT
A.OBJECTID,
A.OBJECTNAME,
 B.ATTRIBUTEID,
B.ATTRIBUTENAME,
 B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR
FROM OBJECT A
INNER JOIN 
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT  NULL) OR (B.VALUESTR IS NOT NULL);


  Can you add more details about recent version to give accurate solutions?

  Thanks Saravanatn!

  The Version Number column contains the recent version.

  For example, for AttributeID =8, there are 3 rows. Two rows with Version Number 2 (One have null value second have B1948 value) and the third row have Version Number =1. 
  So the query should return Row where Version Number is recent version =2, and either one of the column ValueInt or ValueStr must have value. 

  

  Thanks,
  Tapinder

  
SELECT
OBJECTID,
OBJECTNAME,
ATTRIBUTEID,
ATTRIBUTENAME,
CATEGORYNAME,
VERSIONNUMBER,
VALUEINT,
VALUESTR
FROM
(
SELECT
A.OBJECTID,
A.OBJECTNAME,
B.ATTRIBUTEID,
B.ATTRIBUTENAME,
B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR,
ROW_NUMBER()OVER(PARTITION BY A.OBJECTID,B.AttributeID ORDER BY B.VERSIONNUMBER DESC) AS RNK
FROM OBJECT A
INNER JOIN
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT NULL) OR (B.VALUESTR IS NOT NULL)
)C
WHERE RNK=1


  Saravanan

• Thom A

  SSC Guru

  Points: 99126

  More actions

  May 15, 2018 at 9:40 am

  #1990537

  saravanatn - Tuesday, May 15, 2018 9:27 AM

  
SELECT
OBJECTID,
OBJECTNAME,
ATTRIBUTEID,
ATTRIBUTENAME,
CATEGORYNAME,
VERSIONNUMBER,
VALUEINT,
VALUESTR
FROM
(
SELECT
A.OBJECTID,
A.OBJECTNAME,
B.ATTRIBUTEID,
B.ATTRIBUTENAME,
B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR,
ROW_NUMBER()OVER(PARTITION BY A.OBJECTID,B.AttributeID ORDER BY B.VERSIONNUMBER DESC) AS RNK
FROM OBJECT A
INNER JOIN
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT NULL) OR (B.VALUESTR IS NOT NULL)
)C
WHERE RNK=1


  Gah, aliasing like that is a huge pet peeve of mine. Why have you aliased the table OBJECT as A, when you have a table ATTRIBUTE (which you've aliased B!). "B" is for Attribute, "A" is for Object? >_<

  Have a look at this article from Aaron Bertrand: Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  May 15, 2018 at 10:03 am

  #1990542

  Thom A - Tuesday, May 15, 2018 9:40 AM

  saravanatn - Tuesday, May 15, 2018 9:27 AM

  
SELECT
OBJECTID,
OBJECTNAME,
ATTRIBUTEID,
ATTRIBUTENAME,
CATEGORYNAME,
VERSIONNUMBER,
VALUEINT,
VALUESTR
FROM
(
SELECT
A.OBJECTID,
A.OBJECTNAME,
B.ATTRIBUTEID,
B.ATTRIBUTENAME,
B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR,
ROW_NUMBER()OVER(PARTITION BY A.OBJECTID,B.AttributeID ORDER BY B.VERSIONNUMBER DESC) AS RNK
FROM OBJECT A
INNER JOIN
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT NULL) OR (B.VALUESTR IS NOT NULL)
)C
WHERE RNK=1


  Gah, aliasing like that is a huge pet peeve of mine. Why have you aliased the table OBJECT as A, when you have a table ATTRIBUTE (which you've aliased B!). "B" is for Attribute, "A" is for Object? >_<

  Have a look at this article from Aaron Bertrand: Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

  Next time won't commit the same mistake.

  Saravanan

• tapinderaujla

  SSC Veteran

  Points: 279

  More actions

  May 15, 2018 at 10:20 am

  #1990549

  saravanatn - Tuesday, May 15, 2018 10:03 AM

  Thom A - Tuesday, May 15, 2018 9:40 AM

  saravanatn - Tuesday, May 15, 2018 9:27 AM

  
SELECT
OBJECTID,
OBJECTNAME,
ATTRIBUTEID,
ATTRIBUTENAME,
CATEGORYNAME,
VERSIONNUMBER,
VALUEINT,
VALUESTR
FROM
(
SELECT
A.OBJECTID,
A.OBJECTNAME,
B.ATTRIBUTEID,
B.ATTRIBUTENAME,
B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR,
ROW_NUMBER()OVER(PARTITION BY A.OBJECTID,B.AttributeID ORDER BY B.VERSIONNUMBER DESC) AS RNK
FROM OBJECT A
INNER JOIN
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT NULL) OR (B.VALUESTR IS NOT NULL)
)C
WHERE RNK=1


  Gah, aliasing like that is a huge pet peeve of mine. Why have you aliased the table OBJECT as A, when you have a table ATTRIBUTE (which you've aliased B!). "B" is for Attribute, "A" is for Object? >_<

  Have a look at this article from Aaron Bertrand: Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

  Next time won't commit the same mistake.

  Thanks a lot Saravanan!!
  Exactly the result i was looking for. 
  I need one more help from you.
  Instead of returning 4 rows, is it possible to return all the results in one row ?
  Example : 
  

  For your convenience , here is SQL for Attribute and Object table Structure with Data:

  /****** Object: Table [dbo].[Object]  Script Date: 05/15/2018 10:06:05 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[Object](
      [ObjectID] [int] NULL,
      [ObjectName] [nvarchar](255) NULL
  ) ON [PRIMARY]
  GO
  INSERT INTO [dbo].[Object] VALUES(174169,'Network of Excellence')
  GO

  ---*************************************************--
  /****** Object: Table [dbo].[AttibuteValues]  Script Date: 05/15/2018 9:57:40 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[AttibuteValues](
      [AttributeID] [int] NULL,
      [AttributeName] [nvarchar](255) NULL,
      [CategoryName] [nvarchar](255) NULL,
      [VersionNumber] [int] NULL,
      [ValueInt] [nvarchar](255) NULL,
      [ValueStr] [nvarchar](255) NULL,
      [FKObjectId] [int] NULL
  ) ON [PRIMARY]
  GO
  INSERT INTO [dbo].[AttibuteValues] VALUES( 9,'Program','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 2,null,'B1948',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 2,'33240',null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 2,null,'Alberta Science and Research Authority',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 2,'Project Number','Research Project Info', 2,null,'Prj1522',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 2,'Project Number','Research Project Info', 2,null,'Prj15072',174169)
  ---*************************************************--

  Thanks,

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  May 15, 2018 at 10:44 am

  #1990554

  tapinderaujla - Tuesday, May 15, 2018 10:20 AM

  saravanatn - Tuesday, May 15, 2018 10:03 AM

  Thom A - Tuesday, May 15, 2018 9:40 AM

  saravanatn - Tuesday, May 15, 2018 9:27 AM

  
SELECT
OBJECTID,
OBJECTNAME,
ATTRIBUTEID,
ATTRIBUTENAME,
CATEGORYNAME,
VERSIONNUMBER,
VALUEINT,
VALUESTR
FROM
(
SELECT
A.OBJECTID,
A.OBJECTNAME,
B.ATTRIBUTEID,
B.ATTRIBUTENAME,
B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR,
ROW_NUMBER()OVER(PARTITION BY A.OBJECTID,B.AttributeID ORDER BY B.VERSIONNUMBER DESC) AS RNK
FROM OBJECT A
INNER JOIN
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT NULL) OR (B.VALUESTR IS NOT NULL)
)C
WHERE RNK=1


  Gah, aliasing like that is a huge pet peeve of mine. Why have you aliased the table OBJECT as A, when you have a table ATTRIBUTE (which you've aliased B!). "B" is for Attribute, "A" is for Object? >_<

  Have a look at this article from Aaron Bertrand: Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

  Next time won't commit the same mistake.

  Thanks a lot Saravanan!!
  Exactly the result i was looking for. 
  I need one more help from you.
  Instead of returning 4 rows, is it possible to return all the results in one row ?
  Example : 
  

  For your convenience , here is SQL for Attribute and Object table Structure with Data:

  /****** Object: Table [dbo].[Object]  Script Date: 05/15/2018 10:06:05 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[Object](
      [ObjectID] [int] NULL,
      [ObjectName] [nvarchar](255) NULL
  ) ON [PRIMARY]
  GO
  INSERT INTO [dbo].[Object] VALUES(174169,'Network of Excellence')
  GO

  ---*************************************************--
  /****** Object: Table [dbo].[AttibuteValues]  Script Date: 05/15/2018 9:57:40 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[AttibuteValues](
      [AttributeID] [int] NULL,
      [AttributeName] [nvarchar](255) NULL,
      [CategoryName] [nvarchar](255) NULL,
      [VersionNumber] [int] NULL,
      [ValueInt] [nvarchar](255) NULL,
      [ValueStr] [nvarchar](255) NULL,
      [FKObjectId] [int] NULL
  ) ON [PRIMARY]
  GO
  INSERT INTO [dbo].[AttibuteValues] VALUES( 9,'Program','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 2,null,'B1948',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 2,'33240',null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 2,null,'Alberta Science and Research Authority',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 2,'Project Number','Research Project Info', 2,null,'Prj1522',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 2,'Project Number','Research Project Info', 2,null,'Prj15072',174169)
  ---*************************************************--

  Thanks,

  What you meant by is it possible to return all the results in one row ?
  Can you kindly elaborate on what condition you want it as single row with a example?

  Saravanan

• tapinderaujla

  SSC Veteran

  Points: 279

  More actions

  May 15, 2018 at 10:51 am

  #1990558

  saravanatn - Tuesday, May 15, 2018 10:44 AM

  tapinderaujla - Tuesday, May 15, 2018 10:20 AM

  saravanatn - Tuesday, May 15, 2018 10:03 AM

  Thom A - Tuesday, May 15, 2018 9:40 AM

  saravanatn - Tuesday, May 15, 2018 9:27 AM

  
SELECT
OBJECTID,
OBJECTNAME,
ATTRIBUTEID,
ATTRIBUTENAME,
CATEGORYNAME,
VERSIONNUMBER,
VALUEINT,
VALUESTR
FROM
(
SELECT
A.OBJECTID,
A.OBJECTNAME,
B.ATTRIBUTEID,
B.ATTRIBUTENAME,
B.CATEGORYNAME,
B.VERSIONNUMBER,
B.VALUEINT,
B.VALUESTR,
ROW_NUMBER()OVER(PARTITION BY A.OBJECTID,B.AttributeID ORDER BY B.VERSIONNUMBER DESC) AS RNK
FROM OBJECT A
INNER JOIN
ATTRIBUTES B
ON A.OBJECTID=B.OBJECTID
WHERE (B.VALUEINT IS NOT NULL) OR (B.VALUESTR IS NOT NULL)
)C
WHERE RNK=1


  Gah, aliasing like that is a huge pet peeve of mine. Why have you aliased the table OBJECT as A, when you have a table ATTRIBUTE (which you've aliased B!). "B" is for Attribute, "A" is for Object? >_<

  Have a look at this article from Aaron Bertrand: Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

  Next time won't commit the same mistake.

  Thanks a lot Saravanan!!
  Exactly the result i was looking for. 
  I need one more help from you.
  Instead of returning 4 rows, is it possible to return all the results in one row ?
  Example : 
  

  For your convenience , here is SQL for Attribute and Object table Structure with Data:

  /****** Object: Table [dbo].[Object]  Script Date: 05/15/2018 10:06:05 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[Object](
      [ObjectID] [int] NULL,
      [ObjectName] [nvarchar](255) NULL
  ) ON [PRIMARY]
  GO
  INSERT INTO [dbo].[Object] VALUES(174169,'Network of Excellence')
  GO

  ---*************************************************--
  /****** Object: Table [dbo].[AttibuteValues]  Script Date: 05/15/2018 9:57:40 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[AttibuteValues](
      [AttributeID] [int] NULL,
      [AttributeName] [nvarchar](255) NULL,
      [CategoryName] [nvarchar](255) NULL,
      [VersionNumber] [int] NULL,
      [ValueInt] [nvarchar](255) NULL,
      [ValueStr] [nvarchar](255) NULL,
      [FKObjectId] [int] NULL
  ) ON [PRIMARY]
  GO
  INSERT INTO [dbo].[AttibuteValues] VALUES( 9,'Program','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 2,null,'B1948',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 2,'33240',null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 2,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 2,null,'Alberta Science and Research Authority',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 2,'Project Number','Research Project Info', 2,null,'Prj1522',174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 8,'Application Number','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 7,'Project Manager','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 4,'Party','Research Project Info', 1,null,null,174169)
  INSERT INTO [dbo].[AttibuteValues] VALUES( 2,'Project Number','Research Project Info', 2,null,'Prj15072',174169)
  ---*************************************************--

  Thanks,

  What you meant by is it possible to return all the results in one row ?
  Can you kindly elaborate on what condition you want it as single row with a example?

  If i execute the query that you sent to me on the tables(Table Structure and table data) given above. 

  SELECT
  OBJECTID,
  OBJECTNAME,
  ATTRIBUTEID,
  ATTRIBUTENAME,
  CATEGORYNAME,
  VERSIONNUMBER,
  VALUEINT,
  VALUESTR
  FROM
  (
  SELECT
  A.OBJECTID,
  A.OBJECTNAME,
  B.ATTRIBUTEID,
  B.ATTRIBUTENAME,
  B.CATEGORYNAME,
  B.VERSIONNUMBER,
  B.VALUEINT,
  B.VALUESTR,
  ROW_NUMBER()OVER(PARTITION BY A.OBJECTID,B.AttributeID ORDER BY B.VERSIONNUMBER DESC) AS RNK
  FROM [OBJECT] A
  INNER JOIN
  AttibuteValues B
  ON A.OBJECTID=B.FKObjectId
  WHERE (B.VALUEINT IS NOT NULL) OR (B.VALUESTR IS NOT NULL)
  )C
  WHERE RNK=1

  The above query will return following four rows: 
  

  Is it possible to show these rows in following output ?
  

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  May 15, 2018 at 11:09 am

  #1990563

  
DECLARE @Object TABLE (
[ObjectID] [int] NULL,
[ObjectName] [nvarchar](255) NULL
);
INSERT INTO @Object (ObjectID, ObjectName
           )
VALUES(174169,'Network of Excellence');
DECLARE @AttibuteValues table(
[AttributeID] [int] NULL,
[AttributeName] [nvarchar](255) NULL,
[CategoryName] [nvarchar](255) NULL,
[VersionNumber] [int] NULL,
[ValueInt] [nvarchar](255) NULL,
[ValueStr] [nvarchar](255) NULL,
[FKObjectId] [int] NULL
);
INSERT INTO @AttibuteValues (
                   AttributeID
                 , AttributeName
                 , CategoryName
                 , VersionNumber
                 , ValueInt
                 , ValueStr
                 , FKObjectId
                   )
VALUES
    ( 9,'Program','Research Project Info', 2,null,null,174169)
  , ( 8,'Application Number','Research Project Info', 2,null,null,174169)
  , ( 8,'Application Number','Research Project Info', 2,null,'B1948',174169)
  , ( 7,'Project Manager','Research Project Info', 2,null,null,174169)
  , ( 7,'Project Manager','Research Project Info', 2,'33240',null,174169)
  , ( 4,'Party','Research Project Info', 2,null,null,174169)
  , ( 4,'Party','Research Project Info', 2,null,'Alberta Science and Research Authority',174169)
  , ( 2,'Project Number','Research Project Info', 2,null,'Prj1522',174169)
  , ( 8,'Application Number','Research Project Info', 1,null,null,174169)
  , ( 7,'Project Manager','Research Project Info', 1,null,null,174169)
  , ( 4,'Party','Research Project Info', 1,null,null,174169)
  , ( 2,'Project Number','Research Project Info', 2,null,'Prj15072',174169);
WITH t AS
(SELECT
 o.OBJECTID,
 o.OBJECTNAME,
 av.ATTRIBUTEID,
 av.ATTRIBUTENAME,
 av.CATEGORYNAME,
av.VERSIONNUMBER,
av.VALUEINT,
av.VALUESTR,
ROW_NUMBER()OVER(PARTITION BY o.OBJECTID,av.AttributeID ORDER BY av.VERSIONNUMBER DESC) AS RNK
FROM @Object o
INNER JOIN
@AttibuteValues av
ON o.OBJECTID=av.FKObjectId
WHERE (av.VALUEINT IS NOT  NULL) OR (av.VALUESTR IS NOT NULL))
SELECT [Project Number].ObjectID
     , [Project Number].ObjectName
     , [Project Number].CategoryName
     , [Project Number].ValueStr [Project Number]
     , Party.ValueStr Party
     , [Project Manager].ValueInt  [Project Manager]
     , [Application Number].ValueStr [Application Number]
FROM t [Project Number]
JOIN t Party ON [Project Number].ObjectID = Party.ObjectID AND [Project Number].RNK = Party.RNK
JOIN t [Project Manager] ON Party.ObjectID = [Project Manager].ObjectID AND Party.RNK = [Project Manager].RNK
JOIN t [Application Number] ON Party.ObjectID = [Application Number].ObjectID AND Party.RNK = [Application Number].RNK
WHERE [Project Number].RNK=1
AND [Project Number].AttributeName='Project Number'
AND Party.AttributeName='Party'
AND [Project Manager].AttributeName='Project Manager'
AND [Application Number].AttributeName='Application Number'
;
  

• tapinderaujla

  SSC Veteran

  Points: 279

  More actions

  May 15, 2018 at 2:40 pm

  #1990603

  Joe Torre - Tuesday, May 15, 2018 11:09 AM

  
DECLARE @Object TABLE (
[ObjectID] [int] NULL,
[ObjectName] [nvarchar](255) NULL
);
INSERT INTO @Object (ObjectID, ObjectName
           )
VALUES(174169,'Network of Excellence');
DECLARE @AttibuteValues table(
[AttributeID] [int] NULL,
[AttributeName] [nvarchar](255) NULL,
[CategoryName] [nvarchar](255) NULL,
[VersionNumber] [int] NULL,
[ValueInt] [nvarchar](255) NULL,
[ValueStr] [nvarchar](255) NULL,
[FKObjectId] [int] NULL
);
INSERT INTO @AttibuteValues (
                   AttributeID
                 , AttributeName
                 , CategoryName
                 , VersionNumber
                 , ValueInt
                 , ValueStr
                 , FKObjectId
                   )
VALUES
    ( 9,'Program','Research Project Info', 2,null,null,174169)
  , ( 8,'Application Number','Research Project Info', 2,null,null,174169)
  , ( 8,'Application Number','Research Project Info', 2,null,'B1948',174169)
  , ( 7,'Project Manager','Research Project Info', 2,null,null,174169)
  , ( 7,'Project Manager','Research Project Info', 2,'33240',null,174169)
  , ( 4,'Party','Research Project Info', 2,null,null,174169)
  , ( 4,'Party','Research Project Info', 2,null,'Alberta Science and Research Authority',174169)
  , ( 2,'Project Number','Research Project Info', 2,null,'Prj1522',174169)
  , ( 8,'Application Number','Research Project Info', 1,null,null,174169)
  , ( 7,'Project Manager','Research Project Info', 1,null,null,174169)
  , ( 4,'Party','Research Project Info', 1,null,null,174169)
  , ( 2,'Project Number','Research Project Info', 2,null,'Prj15072',174169);
WITH t AS
(SELECT
 o.OBJECTID,
 o.OBJECTNAME,
 av.ATTRIBUTEID,
 av.ATTRIBUTENAME,
 av.CATEGORYNAME,
av.VERSIONNUMBER,
av.VALUEINT,
av.VALUESTR,
ROW_NUMBER()OVER(PARTITION BY o.OBJECTID,av.AttributeID ORDER BY av.VERSIONNUMBER DESC) AS RNK
FROM @Object o
INNER JOIN
@AttibuteValues av
ON o.OBJECTID=av.FKObjectId
WHERE (av.VALUEINT IS NOT  NULL) OR (av.VALUESTR IS NOT NULL))
SELECT [Project Number].ObjectID
     , [Project Number].ObjectName
     , [Project Number].CategoryName
     , [Project Number].ValueStr [Project Number]
     , Party.ValueStr Party
     , [Project Manager].ValueInt  [Project Manager]
     , [Application Number].ValueStr [Application Number]
FROM t [Project Number]
JOIN t Party ON [Project Number].ObjectID = Party.ObjectID AND [Project Number].RNK = Party.RNK
JOIN t [Project Manager] ON Party.ObjectID = [Project Manager].ObjectID AND Party.RNK = [Project Manager].RNK
JOIN t [Application Number] ON Party.ObjectID = [Application Number].ObjectID AND Party.RNK = [Application Number].RNK
WHERE [Project Number].RNK=1
AND [Project Number].AttributeName='Project Number'
AND Party.AttributeName='Party'
AND [Project Manager].AttributeName='Project Manager'
AND [Application Number].AttributeName='Application Number'
;
  

  Thanks Joe!

  SQL is awesome! 
  "Party" column have multiple rows, i want to show all non null "Party" column values as comma separated  values . 

  Example Final Result:
  

  Here is the update SQL that you sent to me. I have inserted few more rows for "Party" column.  These rows are in red font.

  DECLARE @Object TABLE (
  [ObjectID] [int] NULL,
  [ObjectName] [nvarchar](255) NULL
  );
  INSERT INTO @Object (ObjectID, ObjectName
      )
  VALUES(174169,'Network of Excellence');
  DECLARE @AttibuteValues table(
  [AttributeID] [int] NULL,
  [AttributeName] [nvarchar](255) NULL,
  [CategoryName] [nvarchar](255) NULL,
  [VersionNumber] [int] NULL,
  [ValueInt] [nvarchar](255) NULL,
  [ValueStr] [nvarchar](255) NULL,
  [FKObjectId] [int] NULL
  );
  INSERT INTO @AttibuteValues (
         AttributeID
        , AttributeName
        , CategoryName
        , VersionNumber
        , ValueInt
        , ValueStr
        , FKObjectId
         )
  VALUES
    ( 9,'Program','Research Project Info', 2,null,null,174169)
  , ( 8,'Application Number','Research Project Info', 2,null,null,174169)
  , ( 8,'Application Number','Research Project Info', 2,null,'B1948',174169)
  , ( 7,'Project Manager','Research Project Info', 2,null,null,174169)
  , ( 7,'Project Manager','Research Project Info', 2,'33240',null,174169)
  , ( 4,'Party','Research Project Info', 2,null,null,174169)
  , ( 4,'Party','Research Project Info', 2,null,'Alberta Science and Research Authority',174169)
  , ( 4,'Party','Research Project Info', 2,null,'Alberta Enviroment',174169)
  , ( 4,'Party','Research Project Info', 2,null,'Quincy Ports',174169)
  , ( 4,'Party','Research Project Info', 2,null,'Edson Oils',174169)
  , ( 2,'Project Number','Research Project Info', 2,null,'Prj1522',174169)
  , ( 8,'Application Number','Research Project Info', 1,null,null,174169)
  , ( 7,'Project Manager','Research Project Info', 1,null,null,174169)
  , ( 4,'Party','Research Project Info', 1,null,null,174169)
  , ( 2,'Project Number','Research Project Info', 2,null,'Prj15072',174169);
  WITH t AS
  (SELECT
  o.OBJECTID,
  o.OBJECTNAME,
  av.ATTRIBUTEID,
  av.ATTRIBUTENAME,
  av.CATEGORYNAME,
  av.VERSIONNUMBER,
  av.VALUEINT,
  av.VALUESTR,
  ROW_NUMBER()OVER(PARTITION BY o.OBJECTID,av.AttributeID ORDER BY av.VERSIONNUMBER DESC) AS RNK
  FROM @Object o
  INNER JOIN
  @AttibuteValues av
  ON o.OBJECTID=av.FKObjectId
  WHERE (av.VALUEINT IS NOT NULL) OR (av.VALUESTR IS NOT NULL))
  SELECT [Project Number].ObjectID
    , [Project Number].ObjectName
    , [Project Number].CategoryName
    , [Project Number].ValueStr [Project Number]
    , Party.ValueStr Party
    , [Project Manager].ValueInt [Project Manager]
    , [Application Number].ValueStr [Application Number]
  FROM t [Project Number]
  JOIN t Party ON [Project Number].ObjectID = Party.ObjectID AND [Project Number].RNK = Party.RNK
  JOIN t [Project Manager] ON Party.ObjectID = [Project Manager].ObjectID AND Party.RNK = [Project Manager].RNK
  JOIN t [Application Number] ON Party.ObjectID = [Application Number].ObjectID AND Party.RNK = [Application Number].RNK
  WHERE [Project Number].RNK=1
  AND [Project Number].AttributeName='Project Number'
  AND Party.AttributeName='Party'
  AND [Project Manager].AttributeName='Project Manager'
  AND [Application Number].AttributeName='Application Number'

• jcelko212 32090

  SSCrazy Eights

  Points: 9294

  More actions

  May 16, 2018 at 1:46 pm

  #1990728

  tapinderaujla - Tuesday, May 15, 2018 12:11 AM

  Your suffering from a lot of problems. First of all, it's not any fun for people that are working for you for free to transcribe the DDL you are too lazy to post from pictures. This is just plain rude and violates all the netiquette it's been established for over 3 decades on SQL groups.

  Looking at the names in your pictures, you don't understand how RDBMS works at all. You've mixed data and metadata and created a horrible version of a design flaw which is called EAV (entity attribute value) modeling.

  >> I have two table named "Objects" and "Attributes" <<

  Columns in the table represent attributes of an entity. This is usually covered the first week of any course on RDBMS so, you just posted something that is impossible.

  >> I need to write SQL query to return ObjectId and ObjectName from object table and AttributeName, ValueInt,ValueStr columns from Attribute Table Structure. <<

  If you would bother to learn the ISO 11179 naming rules, you would know that an attribute as to be something in particular and not a generic. This is because RDBMS is based on the laws of logic, and the most fundamental law of logic is law of identity ("to be is to be something in particular; to be nothing in particular or anything in general is to be nothing at all.")

  We never put metadata in what the standards call the attribute property. Things like "_int" or "_str" are datatypes and reflect the physical storage used to model the attribute. You don't understand abstraction

  >> In Attributes table , there are multiple versions for some attributes,.. <<

  Attributes do not have versions. How many versions are there of something like "automobile weight" in the universe? This is the nature of an attribute.

  >> but I need this SQL to pull valueInt and ValueStr for the recent versions and pull only those rows from Attributes table where either ValueInt or ValueStr is must have value in it. <<

  First of all, the DDL should determine whether something can be nullable or not.

  >> Final Result should look like green rows below: <<

  Why do you think "green" is a meaningful statement in a logical model? Oh, you want us to transcribe the data from your pictures!

  I know it's a little embarrassing be told by someone that broke the standards for the language or using picture doing everything completely wrong. In a public forum, but you really, really do need to start over. And we don't have enough information to really help you. I hope you're not actually doing this in an employment situation where you could hurt people. Try to make all your mistakes in school instead.

  Please post DDL and follow ANSI/ISO standards when asking for help.