Get unique instances from different tables

Category: sql server getstarted


AlexMartini on Wed, 28 Dec 2016 00:47:47

I have two tables in SQL. 

1) Claims with CLAIM ID 2) Work orders with WORK ORDER ID...connected to claims 

Work orders have a pricing type and a work order date. 

I have to create a list with claim ID's, associated work orders, and their pricing types, ordered by work order date. THEN, I want to return the unique combinations of pricing types (by date order)

For example

Claim 1: Pricing type (A,A,A)

Claim 2: Pricing type (A,B)

Claim 3: Pricing type (B,A)

Claim 4: Pricing type (A,B)

Would return


A,B: 2

B,A: 1

How can I do this with SQL? 


Uri Dimant on Wed, 28 Dec 2016 04:13:38

Can you post CREATE TABLE + INSERT INTO statements along with desired result? 

>>>>B,A: 1

Should not be for the above combination Claim 3 rather than 1?

AlexMartini on Wed, 28 Dec 2016 17:09:28

Hello. I'm not sure what you mean about the create table stuff? 

Yes, the number is just the number of times that combination appears. 

Combination A, B appears twice and combination B, A appears once in the data. 

AlexMartini on Wed, 28 Dec 2016 17:11:04

I have a Claims table and a work order table with foreign key claims. How do I return all work orders for the claims? One row for each work order. 

DIEGOCTN on Wed, 28 Dec 2016 17:23:23

You should post the DDL next time, anyway:

select * from Claims inner join Order on Claims.XXXX=Order.YYYY

where XXXX is the name of the column in the table Claims (usually a Primary key)

and YYYY is the name of the column in the table Order (The column which has the Foreign Key)

Please mark as answer if this post helped you

Rick Byham, Microsoft on Wed, 28 Dec 2016 17:26:33

Something like:

SELECT Claims.*, [Work Order].*
FROM Claims
JOIN [Work Order]
    ON Claims.keycolumn = [Work Order].keycolumn ;

Obviously I made up a lot of this. * indicates all columns. You might not want them all. The [ ] is only necessary if there is a space in the table name. Typically you also provide an "alias" for table names using the key word AS. So maybe:

FROM Claims AS C
JOIN [Work Order] AS WO
    ON C.keycolumn = WO.keycolumn ;

Olaf Helper on Wed, 28 Dec 2016 18:04:17

Hello Alex,

And see additional MSDN Join Fundamentals and Types of Joins

AlexMartini on Wed, 28 Dec 2016 18:46:43

Heeeeeeeelp!!!! :)

AlexMartini on Wed, 28 Dec 2016 18:49:06

What is the DDL? 

AlexMartini on Wed, 28 Dec 2016 18:57:09

Thanks Rick. Is your code better than DIEGO's?

Erland Sommarskog on Wed, 28 Dec 2016 21:29:42

Hello. I'm not sure what you mean about the create table stuff? 

What Uri means that if you post CREATE TABLE statements for your table and INSERT statements for your sample data, we are much more likely to help you, since we can copy the script to a query window and develop a tested solution. You can generate the CREATE TABLE script from Object Explorer in SSMS, just right-click the table and select from the context menu.

You seemed to have started a second thread on the same quiestion. I looked at it, but I was not really able to understand what you were looking for. That's also a think we like to have: the desired result given the sample data.

Rick Byham, Microsoft on Wed, 28 Dec 2016 22:53:02

The two example are functionally the same. His hadn't appeared when I started writing my response. We made up names of different tables and columns is all.

pituach on Thu, 29 Dec 2016 03:15:05

Good day Alex

DDL: Data Definition Language statements are used to define the database structure or schema.

* In the forum it mean that you should post queries to create the relevant tables, instead of posting stories (description of the table structure) ;-)

If you create the table with the SSMS GUI and you do not know hoe to write the queries to create the table, then this link can help you:

DML: Data Manipulation Language (DML) statements are used for managing data

* the definitions are from this link which include more info that you must read