[Show all top banners]

phone
Replies to this thread:

More by phone
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Discussion Refresh page to view new replies
 Questions to SQL Gurus
[VIEWED 1755 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 90 days
Recommended Popular Threads Controvertial Threads
TRUMP 2016!!! Here is why?
~चौतारी १९९~
Mnang, Mustang Treking and Our Engagement
Learning computer programming
ICE RAID
Are you invited for 4th of July Celeberation by WH
❤ नारी दर्पण ❤
TPS FEDERAL NOTICE
हामीहरुले २० जना केटिहरुसंग सेक्स गर्नै पर्छ रे नी !
US upcoming recession --- is it good time to buy house when rates are low or is it good time to wait recession?
IT Consultancy Dons and Donts
साझाका Sherlock Holmes .. Please suggest
थुक्क प्रियंका कार्कीजी …
नफर्किने गरि नेपाल जाने सोच
TPS to GC (PERM-I140-I485) TimeLine
TPS ma ghanta hannu paryo re IT matra hamro job bikalpa?
Free! Free! Free! - Training and placement
TPS to change of status denied ..Need help
Nepali people greencard in danger
Salary offer aako cha
Former U.S. President Bill Clinton in Nepal
~चौतारी १९९~
TRUMP 2016!!! Here is why?
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