[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 6796 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 30 days
Recommended Popular Threads Controvertial Threads
What are your first memories of when Nepal Television Began?
nrn citizenship
काेराेना सङ्क्रमणबाट बच्न Immunity बढाउन के के खाने ?How to increase immunity against COVID - 19?
Travelling to Nepal - TPS AP- PASSPORT
TPS Work Permit/How long your took?
मन भित्र को पत्रै पत्र!
Informatica consultancy share
Travelling on TPS advance travel document to different country...
Are you ready to know the truth?
Thank you for the sajha policy
Nepali doctors future black or white usa ?
चितवनको होस्टलमा १३ वर्षीया शालिन पोखरेल झुण्डिएको अवस्था - बलात्कार पछि हत्याको शंका - होस्टेलहरु असुरक्षित
महँगो अण्डाको पिकल्प : कुखुरा र खोर भाडामा लिने
Does the 180 day auto extension apply for TPS?
TPS Renewal Reregistration
Why is every youths leaving Nepal? Why are not youths entering politics and serving the country, starting business etc?
Is money sent to support family tax deductible?
Send Parcels from USA to Nepal & Worldwide.
Another Song Playing In My Mind
हेर अमेरिकामा नेपालीहरुको बेज्जत
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