[Show all top banners]

phone
Replies to this thread:

More by phone
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 Questions to SQL Gurus
[VIEWED 6801 TIMES]
SAVE! for ease of future access.
Posted on 11-29-18 2:46 AM     Reply [Subscribe]
Login in to Rate this Post:     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

 
Posted on 11-29-18 7:35 AM     [Snapshot: 59]     Reply [Subscribe]
Login in to Rate this Post:     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.
 
Posted on 11-29-18 11:08 AM     [Snapshot: 139]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

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

 
Posted on 11-29-18 11:27 AM     [Snapshot: 142]     Reply [Subscribe]
Login in to Rate this Post:     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

 
Posted on 11-29-18 5:52 PM     [Snapshot: 231]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

This questions fits best on StackOverflow.
I am surprised that somebody helped/answered the question in this site.
 
Posted on 11-30-18 9:56 AM     [Snapshot: 364]     Reply [Subscribe]
Login in to Rate this Post:     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?


 
Posted on 11-30-18 8:14 PM     [Snapshot: 457]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Add this
And where M.MATERIALNAME=Mname3
 
Posted on 12-02-18 11:56 AM     [Snapshot: 604]     Reply [Subscribe]
Login in to Rate this Post:     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?
 
Posted on 12-02-18 11:59 AM     [Snapshot: 607]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

check your inbox msg. I sent you the query
 
Posted on 12-02-18 12:18 PM     [Snapshot: 623]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Thanks pidiiit.
I got the query and it worked.
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 60 days
Recommended Popular Threads Controvertial Threads
What are your first memories of when Nepal Television Began?
рдирд┐рдЧреБрд░реЛ рдерд╛рд╣рд╛ рдЫ ??
Basnet or Basnyat ??
Sajha has turned into MAGATs nest
NRN card pros and cons?
Nas and The Bokas: Coming to a Night Club near you
Will MAGA really start shooting people?
рдорди рднрд┐рддреНрд░ рдХреЛ рдкрддреНрд░реИ рдкрддреНрд░!
Top 10 Anti-vaxxers Who Got Owned by COVID
TPS Work Permit/How long your took?
рдХрд╛реЗрд░рд╛реЗрдирд╛ рд╕рдЩреНрдХреНрд░рдордгрдмрд╛рдЯ рдмрдЪреНрди Immunity рдмрдврд╛рдЙрди рдХреЗ рдХреЗ рдЦрд╛рдиреЗ ?How to increase immunity against COVID - 19?
Breathe in. Breathe out.
3 most corrupt politicians in the world
рдЪрд┐рддрд╡рдирдХреЛ рд╣реЛрд╕реНрдЯрд▓рдорд╛ резрей рд╡рд░реНрд╖реАрдпрд╛ рд╢рд╛рд▓рд┐рди рдкреЛрдЦрд░реЗрд▓ рдЭреБрдгреНрдбрд┐рдПрдХреЛ рдЕрд╡рд╕реНрдерд╛ - рдмрд▓рд╛рддреНрдХрд╛рд░ рдкрдЫрд┐ рд╣рддреНрдпрд╛рдХреЛ рд╢рдВрдХрд╛ - рд╣реЛрд╕реНрдЯреЗрд▓рд╣рд░реБ рдЕрд╕реБрд░рдХреНрд╖рд┐рдд
рд╢реАрд░реНрд╖рдХ рдЬреЗ рдкрдирд┐ рд╣реБрди рд╕рдХреНрдЫред
Disinformation for profit - scammers cash in on conspiracy theories
someone please tell me TPS is here to stay :(
Nepali doctors future black or white usa ?
Doctors dying suddenly or unexpectedly since the rollout of COVID-19 vaccines
BREAKING: THE LEFT HAS LOST THE SUPREME COURT!
Nas and The Bokas: Coming to a Night Club near you
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters