I am using following query to get the results so far but now I need to change in below query so instead of three queries and union , I can get the result in one single query

SELECT spec.name FROM mst_specification  as spec 
inner join mst_lineitem_type as subsubcat on spec.lineitem_type_id=subsubcat.id 
union 
SELECT spec.name FROM mst_specification  as spec 
inner join mst_subcategory as subcat on spec.subcategory_id=subcat.id 
union 
SELECT spec.name FROM mst_specification  as spec 
inner join quote_categories as cat on spec.category_id=cat.id ;

Edit based on comments :

I need to use same mst_specification table in join with another tables

SELECT * FROM mst_quote_type as qt 
INNER Join quote_categories as cat on qt.id=cat.quote_type_id
INNER JOIN mst_subcategory as subcat on cat.id=subcat.quote_category_id 
INNER JOIN mst_lineitem_type as subsubcat on subcat.id= subsubcat.subcategory_id
INNER JOIN quote_lineitem_library as item on subsubcat.id=item.lineitem_type_id
INNER JOIN mst_uom as uom on uom.id=item.uom_id ; 

CodePudding user response：

Left Join the tables on their id's.

SELECT DISTINCT 
  spec.name as spec_name
, cat.name as cat_name
, qt.name as qt_name
, subcat.name as subcat_name
, subsubcat.name as subsubcat_name
, item.name as item_name
, uom. name as uom_name
FROM mst_specification AS spec
LEFT JOIN quote_categories AS cat 
  ON cat.id = spec.category_id
LEFT JOIN mst_quote_type AS qt
  ON qt.id = cat.quote_type_id
LEFT JOIN mst_subcategory AS subcat 
  ON subcat.quote_category_id = cat.id
LEFT JOIN mst_lineitem_type AS subsubcat 
  ON subsubcat.subcategory_id = subcat.id
LEFT JOIN quote_lineitem_library AS item 
  ON item.lineitem_type_id = subsubcat.id
LEFT JOIN mst_uom AS uom 
  ON uom.id = item.uom_id
ORDER BY spec.name, 2, 3, 4;

db<>fiddle here

But if you just need the specification names

SELECT DISTINCT name as spec_name
FROM mst_specification
ORDER BY name

CodePudding user response：

You want to select the spec names where an ID exists in another table. Use EXISTS or IN for these lookups.

select spec.name 
from mst_specification spec 
where spec.lineitem_type_id in (select id from mst_lineitem_type)
   or spec.subcategory_id in (select id from mst_subcategory)
   or spec.category_id in (select id from quote_categories)
order by spec.name;

The question remains why you want to do this. With a proper database built with foreign keys, an ID should always refer to a row in the parent table. So what you have here can be boiled down to

select spec.name 
from mst_specification spec 
where spec.lineitem_type_id is not null
   or spec.subcategory_id is not null
   or spec.category_id is not null
order by spec.name;

Or, if you also see it guaranteed (maybe because of a check constraint) that one of the IDs is not null:

select spec.name 
from mst_specification spec 
order by spec.name;

CodePudding user response：

It appears that you just want to add three spec lookups to your existing query.

If so, just join like normal, but give each occurrence of the spec tab a different alias...

SELECT * FROM mst_quote_type as qt 
INNER Join quote_categories as cat on qt.id=cat.quote_type_id
INNER JOIN mst_subcategory as subcat on cat.id=subcat.quote_category_id 
INNER JOIN mst_lineitem_type as subsubcat on subcat.id= subsubcat.subcategory_id
INNER JOIN quote_lineitem_library as item on subsubcat.id=item.lineitem_type_id
INNER JOIN mst_uom as uom on uom.id=item.uom_id

INNER JOIN mst_specification AS spec_subsubcat ON spec_subsubcat.lineitem_type_id=subsubcat.id 

INNER JOIN mst_specification AS spec_subcat ON spec_subcat.subcategory_id=subcat.id 

INNER JOIN mst_specification AS spec_cat ON spec_cat.category_id=cat.id

Your question title does mention nulls, though you don't explain that in the question, so perhaps only one of the three category levels actually has a key? In which case you may want...

SELECT *, COALESCE(spec_cat.name, spec_subcat.name, spec_subsubcat.name)
FROM mst_quote_type as qt 
INNER Join quote_categories as cat on qt.id=cat.quote_type_id
INNER JOIN mst_subcategory as subcat on cat.id=subcat.quote_category_id 
INNER JOIN mst_lineitem_type as subsubcat on subcat.id= subsubcat.subcategory_id
INNER JOIN quote_lineitem_library as item on subsubcat.id=item.lineitem_type_id
INNER JOIN mst_uom as uom on uom.id=item.uom_id

LEFT JOIN mst_specification AS spec_subsubcat ON spec_subsubcat.lineitem_type_id=subsubcat.id 

LEFT JOIN mst_specification AS spec_subcat ON spec_subcat.subcategory_id=subcat.id 

LEFT JOIN mst_specification AS spec_cat ON spec_cat.category_id=cat.id

The LEFT JOINs ensure the join happens if possible or yields NULLs if not possible. Then, the SELECT uses a COALESCE() to return the first not-null name.

(Picks the category spec name if there is one. Picks the sub category spec name if there is no category spec name. Picks the sub sub category spec name if the other two are not present.)