[Show all top banners]

phone
Replies to this thread:

More by phone
What people are reading
Subscribers
[Total Subscribers 0]
:: Subscribe
Back to: Kurakani General Discussion Refresh page to view new replies
 Questions to SQL Gurus
[VIEWED 954 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 365 days
Recommended Popular Threads Controvertial Threads
TRUMP 2016!!! Here is why?
Vote for Hillary for President not Bernie 2016
Anybody know place to F*K in KTM?
How long does it take to get I485 approved?
What are your first memories of when Nepal Television Began?
कोमल ओलि र धिरेन्द्र बिचको अफेयर
TPS Timeline
Starting business in Nepal
Love lost....जब तिमी मेरो रहेनौ
मेरो बागडोलमा रहेको घर मा छोरी को नाता ले पूर्वराष्ट्रपति लाइ स्वागत छ
👭 👭 👭 👭 देसी भाभी 👭 👭 👭 👭
sarai chada vaye keti haru
H-1B 2018/2019: कसैको approval notice आयो?
काठमाडौँमा जग्गा किन्ने विचार
Cryptocoin Mania bitcoin ether
अस्ट्रेलियामा नेपालकै बेइज्जत हुनेगरी एक नेपालीले यस्तो गरे
Watch world cup live for free
Gemstones in Nepal
TPS TO BE EXTENDED UNTIL DECEMBER 24, 2019
पिन्कुको नया काले हस्पि नाक चुचेय भाते भतुवा
sarai chada vaye keti haru
अर्चना पनेरुले बिहे पश्चात एस्तो अस्लिल भिडियो सार्वजनिक गरे
राई कान्छा र धोति
मुजी चोर धोतिहरु
Nidda Devkota?
Crypto Marktet, Investment, Coinbase, Gdax,
How to differentiate a PAKHE from a SHAHARIA
Finally tps extended (18 months) ( full statement)
TRUMP 2016!!! Here is why?
Vote for Hillary for President not Bernie 2016
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