SQL Query

Question

hi all,

  I have a scenario which shows below. Kindly help me to write query for the data.

I have 3 Tables. 2 master tables and one link table. The table structure is

Table1

--------------

ID (PK)

Name

Table2

---------

ID (PK)

Place

Table3

-----------

ID (PK)

Table1ID (FK)

Table2ID (FK)

CreatedDate

Here i have multiple place for single name which can be inserted in Table3. In my select query i need to show name and place but based on FIFO(only one entry for one Name and corresponding place based on the created date which is earlier)

Answer 1

Try

;with cte as (select T1.Name, T2.Place, T3.CreatedDate, row_number() over (partition by T1.ID order by T3.CreatedDate) as Rn

from Table1 T1 INNER JOIN Table3 T3 on T1.Id = T3.Table1ID INNER JOIN Table2 T2 ON T3.Table2ID = T2.ID)

select Name, Place, CreatedDate from cte where Rn = 1

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Please give us DDL, sample data and expected output to get a proper suggestion

Answer 3

thanks mate

Answer 4

>> I have 3 Tables. 2 master tables and one link table. <<

The term “master” is not part of RDBMS. It comes from magnetic tape files and some of the old network databases. The minimal polite behavior on an SQL forum is to post DDL. What little you did post is wrong. You use the non-RDBMS magical “id” that changes from table to table so identifies everything in general and nothing in particular instead of a key. You put audit meta data in the table being audited. Youhave no idea what the ISO-11179 standards are

Let's try to fix and hope that you will take the time to learn how to write SQL before you post again. 

CREATE TABLE Alpha
(alpha_id INTEGER NOT NULL PRIMARY
 alpha_name VARCHAR(20) NOT NULL);

CREATE TABLE Beta
(beta_id INTEGER NOT NULL PRIMARY
 beta_location VARCHAR(20) NOT NULL);

The real question is why did you need the gamma_id? This idiom usually uses (alpha_id, beta_id) as the key for a relationship table. 

CREATE TABLE Gamma
(alpha_id INTEGER NOT NULL
  REFERENCES Alpha,
 beta_id INTEGER NOT NULL
  REFERENCES Beta,
 PRIMARY KEY (alpha_id, beta_id)
 screwup_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

>> Here I have multiple places for single alpha_name which can be inserted in Gamma. In my query I need to show alpha_name and beta-place but based on FIFO (only one entry for one alpha_name and corresponding beta_place) based on the screwup_date which is earlier) <<

Did you split time and space for an entity across two tables?! Huge design error. 

SELECT *
  FROM (SELECT alpha_id, beta_id, screwup_date,
               MAX(screwup_date) OVER (PARTITION BY alpha_id) 
               AS recent_screwup_date
          FROM Gamma) AS X
WHERE X.screwup_date = X.recent_screwup_date; 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL