Search code examples
sqllinqsql-server-2012sql-server-2008-r2sql-to-linq-conversion

Select one table column with distinct record with other table all data

I have 2 table 'userfoodcategory' and 'MenuMaster'.

'userfoodcategory' has the foodcategory and 'MenuMaster' has multiple items along with this category with a column 'isnonveg'.

I want to query 'userfoodcategory' table data with 1 addition column 'isnonveg', this column is in 'MenuMaster' table.

I am trying below query but it is giving redundant record

 SELECT DISTINCT ufc.*, MM.isnonveg
FROM   MenuMaster MM
LEFT JOIN  userfoodcategory ufc  ON MM.categoryid = ufc.foodcategoryid
WHERE  ufc.USERID = 19 --and MM.isnonveg IS NULL
order by ufc.foodcategoryid

For more details please have a look on below screen shots.

enter image description here

enter image description here

Also I want this as a linq query, but first I was trying to build it in sql and after that I need to convert it in linq as I am new in linq. Thanks in advance.

Solution

  • You can try to use below Query:

    SELECT DISTINCT ufc.*, MM.isnonveg
FROM  (select distinct categoryid,isnonveg FROM MenuMaster) MM
LEFT JOIN  userfoodcategory ufc  ON MM.categoryid = ufc.foodcategoryid
WHERE  ufc.USERID = 19 --and MM.isnonveg IS NULL
order by ufc.foodcategoryid