SQL Query to get data from two tables with one to many relationship

sql query to find one to many relationship in same table
how to retrieve data from two tables with one sql statement
sql join one-to-many return one row
one-to-many relationship with multiple tables
sql select from multiple tables
many-to-many relationship sql query
one-to-many relationship sql query create table
how to write one-to-many query

I am trying to query some data from SQL Server database but can't figure out how to write the query to get the desired data. Below is the scenario.

A CaseMaster can have multiple CaseStages (Preliminary, Appeal, Execution, Reconciliation etc.) and each CaseStage may have one of the statuses like New, In Progress, Closed, Adjudicated, On Hold etc.).

I want to get all cases whose stage's status is "In Progress" or if stage's status is not in Progress then only last stage whose status is "Adjudicated". In the result set, for each case, either that stage should appear which is "In Progress" or only last one which is "Adjudicated" (not both).

Below is what I have tried so far, but it shows both "In Progress" and "Adjudicated".

SELECT
    TOP (100) PERCENT
    dbo.CaseStages.stage_name,
    dbo.CaseStages.stage_case_no,
    dbo.CaseStages.case_type,
    dbo.CaseStages.company_role,
    dbo.CaseStages.stage_start_date,
    dbo.CaseStages.stage_status, 
    dbo.CaseStages.case_id,
    dbo.CaseMaster.file_number
FROM
    dbo.CaseStages INNER JOIN
    dbo.CaseMaster ON dbo.CaseStages.case_id = dbo.CaseMaster.id
WHERE
    ( dbo.CaseStages.stage_status = 'In Progress' )
    OR
    ( dbo.CaseStages.stage_status <> 'In Progress')
    AND
    ( dbo.CaseStages.stage_status = 'Adjudicated')
GROUP BY
    dbo.CaseStages.stage_name,
    dbo.CaseStages.stage_case_no,
    dbo.CaseStages.case_type,
    dbo.CaseStages.company_role, 
    dbo.CaseStages.stage_start_date,
    dbo.CaseStages.stage_status, 
    dbo.CaseStages.case_id, 
    dbo.CaseMaster.file_number

Try this,

SELECT TOP (100) PERCENT 
    dbo.CaseStages.stage_name,
    dbo.CaseStages.stage_case_no, 
    dbo.CaseStages.case_type, 
    dbo.CaseStages.company_role, 
    dbo.CaseStages.stage_start_date, 
    dbo.CaseStages.stage_status, 
    dbo.CaseMaster.file_number
FROM
(
    select 
        A.stage_name,
        A.stage_case_no, 
        A.case_type, 
        A.company_role, 
        A.stage_start_date, 
        A.stage_status, 
        A.file_number 
    FROM dbo.CaseStages A
    where dbo.CaseStages.stage_status = 'In Progress'
    union all
    select 
        A.stage_name,
        A.stage_case_no, 
        A.case_type, 
        A.company_role, 
        A.stage_start_date, 
        A.stage_status, 
        A.file_number
    from dbo.CaseStages A
    left join 
    (
        select distinct case_id 
        from dbo.CaseStages b 
        where b.stage_status = 'In Progress'
    )B
    on a.case_id=b.case_id
    where a.stage_status = 'Adjudicated'
    and b.case_id is null
)A
INNER JOIN dbo.CaseMaster 
ON A.case_id = dbo.CaseMaster.id

SQL query two tables with relation one-to-many, Hi I have two tables: EMP with EmpID(int) and EmpName (nvarchar 100)fields and a second table Distribution with fields JobNo(nvarchar50)  Search for Database Sql on the New KensaQ.com. Welcome to Kensaq.com. Find Database Sql Today!

Try like this (Not tested)

SELECT 
    dbo.CaseStages.stage_name,
    dbo.CaseStages.stage_case_no,
    dbo.CaseStages.case_type,
    dbo.CaseStages.company_role,
    dbo.CaseStages.stage_start_date,
    dbo.CaseStages.stage_status, 
    dbo.CaseStages.case_id,
    dbo.CaseMaster.file_number
FROM
    dbo.CaseStages INNER JOIN
    dbo.CaseMaster ON dbo.CaseStages.case_id = dbo.CaseMaster.id
WHERE
    ( dbo.CaseStages.stage_status = 'In Progress' )

union

SELECT
    TOP (1) 
    dbo.CaseStages.stage_name,
    dbo.CaseStages.stage_case_no,
    dbo.CaseStages.case_type,
    dbo.CaseStages.company_role,
    dbo.CaseStages.stage_start_date,
    dbo.CaseStages.stage_status, 
    dbo.CaseStages.case_id,
    dbo.CaseMaster.file_number
FROM
    dbo.CaseStages INNER JOIN
    dbo.CaseMaster ON dbo.CaseStages.case_id = dbo.CaseMaster.id
WHERE   
     dbo.CaseStages.stage_status = 'Adjudicated'
ORDER BY     
    dbo.CaseStages.stage_end_date  desc

SQL Query to get data from two table with one to many relationship , How can I get data from multiple tables in SQL? Search For Database Sql Here. Search For Database Sql Now!

Given you have 2 different criteria, one involving the latest date and the other just the in-process record, this should work. I have no data to test with so it was not tested:

SELECT 
    dbo.CaseStages.stage_name,
    dbo.CaseStages.stage_case_no,
    dbo.CaseStages.case_type,
    dbo.CaseStages.company_role,
    dbo.CaseStages.stage_start_date,
    dbo.CaseStages.stage_status, 
    dbo.CaseStages.case_id,
    dbo.CaseMaster.file_number
FROM
    dbo.CaseStages INNER JOIN
    dbo.CaseMaster ON dbo.CaseStages.case_id = dbo.CaseMaster.id
WHERE
    ( dbo.CaseStages.stage_status = 'In Progress' )

UNION ALL

SELECT stage_name, stage_case_no,case_type,company_role,stage_start_date,stage_status,case_id,file_number
FROM 
(SELECT
    MAX(dbo.CaseStages.stage_end_date) EndDate, 
    dbo.CaseStages.stage_name,
    dbo.CaseStages.stage_case_no,
    dbo.CaseStages.case_type,
    dbo.CaseStages.company_role,
    dbo.CaseStages.stage_start_date,
    dbo.CaseStages.stage_status, 
    dbo.CaseStages.case_id,
    dbo.CaseMaster.file_number
FROM
    dbo.CaseStages INNER JOIN
    dbo.CaseMaster ON dbo.CaseStages.case_id = dbo.CaseMaster.id
WHERE  
     dbo.CaseStages.stage_status = 'Adjudicated'
GROUP BY dbo.CaseStages.stage_name,
    dbo.CaseStages.stage_case_no,
    dbo.CaseStages.case_type,
    dbo.CaseStages.company_role,
    dbo.CaseStages.stage_start_date,
    dbo.CaseStages.stage_status, 
    dbo.CaseStages.case_id,
    dbo.CaseMaster.file_number)

How to save the data on different tables?, How can I save data in two tables at a time? SQL Query to get data from two table with one to many relationship [Answered] RSS 9 replies Last post Jul 04, 2011 07:37 AM by NidaNovice

Splitting data into related tables (article), ('003','married'); Enclose it in a transaction if you need to guarantee that both statements either fail or succeed. Build a select query by using tables with a many-to-many relationship On the Create tab, in the Queries group, click Query Design. The Show Table dialog box opens. In the Show Table dialog box, double-click the two tables that contain the data you want to include in your query and also the junction table that links them, and then click Close.

Combine data from two tables which has one to many relationship, Combine data from two tables which has one to many relationship. Hi, Using above query i get multiple records from parent table. There is not a easy sql statement to do this as it is you would have to validate that each child is not used in a  Trying to create a SQL Server query based on a challenge match system. There are two tables that I want to join in the query: Users and ChallengeMatches. The columns in the Users table are UserID, UserDisplayName. The columns in the ChallengeMatch table are ChallengeMatchID, ChallengerUserID, ChallengeeUserID

Creating multiple tables and table relationships, Having all our data in one table can make for very difficult data management. What entities might we define for our SQL Book application? and we've used that column in many of our SELECT queries in order to uniquely identify rows;  In both these tables i have binary data of image. At the end of the table, select an empty row. The two solutions share a common goal: providing to the join function in DAX two tables that have one or more columns with the same data lineage. To export the data the REGIONS table: In SQL Developer, click Tools, then. , setTable('table_does_not

Comments
  • Please provide sample data and desired results. Your query is way more complicated than the explanation would suggest.
  • Also, SELECT TOP 100 PERCENT... is a code-smell. There's no good reason for using that in a production query.
  • You really shouldn't be including the table's schema when referencing a column. Instead you should be using good aliases for your tables (object) and using those as the prefix when you reference a column. Using the schema name is deprecated, and your code will stop working in a future version of SQL Server.
  • Why the GROUP BY ?
  • When using OR you should really be using parentheses (()) to group the clauses as well; otherwise the query may well not behave as you expect.