Get unmatched values and matched values from two tables.

Question

Get unmatched values and matched values from two tables.

karthik82.vk

SSCrazy

Points: 2002
More actions
April 10, 2015 at 4:32 am

#398304

I have two tables category and lu_category.
The category table has columns [CategoryId], [CategoryName], [TotalCategoryRiskScore] and the lu_category has columns [CategoryId], [CategoryName].
I want a sql query that will list all values from lu_category table and category table and if a categoryid is not available in lu_category table but available in category table,
i need that too in the result.
Below is the screenshot of the data and my desired output
I have attached the data as spreadsheet.
Can anyone please help me with this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Where does CategoryId 8 come from?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, that "Mobile Equipments" category has appeared from nowhere. But all you need is a LEFT JOIN between the two tables, so that all rows from the left hand table are included regardless of whether they are matched in the right hand table. I suspect you're hoping someone will write the query for you. You might be that lucky, but people are usually more willing to help if you've had a go at it yourself first.

John

karthik82.vk SSCrazy Points: 2002 More actions · Answer 3

Hi Both,

I just updated the post. its a typo from my end.

I tried LEFT JOIN and FULL JOIN however i am getting only one row.

the category table has a dept_id and i need the result by dept_id in where condition.

below is the sql code that i used.

SELECT c.CategoryId, c.CategoryName, c.TotalCategoryRiskScore FROM lu_Category lc LEFT JOIN Category c ON lc.CategoryId = c.CategoryId

WHERE c.dept_id= 'NDC002001'

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

karthik82.vk (4/10/2015)
Hi Both,
I just updated the post. its a typo from my end.
I tried LEFT JOIN and FULL JOIN however i am getting only one row.
the category table has a dept_id and i need the result by dept_id in where condition.
below is the sql code that i used.
SELECT c.CategoryId, c.CategoryName, c.TotalCategoryRiskScore FROM lu_Category lc LEFT JOIN Category c ON lc.CategoryId = c.CategoryId
WHERE c.dept_id= 'NDC002001'

.. or c.dept_id is null.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

karthik82.vk (4/10/2015)
Hi Both,
I just updated the post. its a typo from my end.
I tried LEFT JOIN and FULL JOIN however i am getting only one row.
the category table has a dept_id and i need the result by dept_id in where condition.
below is the sql code that i used.
SELECT c.CategoryId, c.CategoryName, c.TotalCategoryRiskScore FROM lu_Category lc LEFT JOIN Category c ON lc.CategoryId = c.CategoryId
WHERE c.dept_id= 'NDC002001'

I dont see any reference in your post that refers to c.dept_id

suggest you provide some real data as CREATE/INSERT

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Yes, putting a column from the outer table into your WHERE clause turns your outer join into an inner join. Try turning WHERE into AND so that it becomes part of the join predicate.

John