Questions to SQL Gurus

Posts 10 · Viewed 6914 · Go to Last Post

· Snapshot 0

Like · Likedby · 0

Hi SQL Gurus,
How do I join two tables with additional restrictions? Here is the scenario:
There is First Table that has two columnsProductID and ProductName.

ProductID ProductName
1 P1
2 P2

The second Table has 4 columns :
MaterialID ProductID MaterialName Status
M1 1 MName 1 Pass
M2 1 MName2 Pass
M3 1 MName3 Pass

Join the table Product and Material on ProductID. However, join the last row with if Status for each row is Pass. If the Status of any or all row is Fail, then join with the row that has Fail at first.
Can I have a query please?

Last edited: 29-Nov-18 02:49 AM

lazyketa

· Snapshot 59

Like · Liked by · 0

Before even going to the solution you have to make sure that you have to have a sorting (order by) criteria to get consistency in getting first or last row. Not going to give you a complete SQL statement but to solve this problem you can use RANK.

pidiiit

· Snapshot 139

Like · Liked by · 0

See below:

Last edited: 29-Nov-18 11:29 AM

pidiiit

· Snapshot 142

Like · Liked by · 0

Use this. Rownumber will assign each row a value and rank will assign 1 as fail 2 as pass.

SELECT P.PRODUCT_ID,
P.PRODUCT_NAME,
M.MATERIAL_ID,
M.PRODUCT_ID,
M.MATERIALNAME,
M.STATUS
FROM PRODUCT_TABLE P
JOIN
(
SELECT MATERIAL_ID,
PRODUCT_ID,
MATERIALNAME,
[STATUS],
RANK() OVER(PARTITION BY PRODUCT_ID ORDER BY [STATUS]) AS STATUS_COLUMN
FROM MATERIAL_TABLE
) M ON P.PRODUCT_ID = M.PRODUCT_ID
WHERE M.STATUS_COLUMN = 1

Let me know if you have any concerns.

Last edited: 29-Nov-18 11:29 AM

everestial007

· Snapshot 231

Like · Liked by · 0

This questions fits best on StackOverflow.
I am surprised that somebody helped/answered the question in this site.

phone

· Snapshot 364

Like · Liked by · 0

Thanks pidiiit .

I got the below result but I need the only highlighted row.

Join the table Product and Material on ProductID. However, join the last row with if Status for each row is Pass. If the Status of any or all row is Fail, then join with the row that has Fail at first.

Can I have a query please?

Dev_

· Snapshot 457

Like · Liked by · 0

Add this
And where M.MATERIALNAME=Mname3

phone

· Snapshot 604

Like · Liked by · 0

Thanks Dev_,

My situation is I don't know the value of both tables and there could be thousands of records in both tables. The above table is just for example.

Join the table Product and Material on ProductID. However, join the last row with if Status for each row is Pass. If the Status of any or all row is Fail, then join with the row that has Fail at first.
Can I have a query please?

pidiiit

· Snapshot 607

Like · Liked by · 0

check your inbox msg. I sent you the query

phone

· Snapshot 623

Like · Liked by · 0

Thanks pidiiit.
I got the query and it worked.

Please log in to reply to this post

You can also log in using your Facebook