Help with a SQL query

[Ed_Prunier]

I am very new to SQL and trying to learn my way around the tables.  I'd like to retrieve a list of all document types from select document groups and show show which scan queue they belong to.  The problem I'm having with my query is that no row is returned if the document type is not assigned to a scan queue.  I'd like to see a blank entry.  

SELECT dt.itemtypenum AS "Document ID", dt.itemtypename AS "Document Type", itg.itemtypegroupname
AS "Document Type Group", sq.queuename AS "Scan Queue"
FROM hsi.doctype dt
INNER JOIN hsi.itemtypegroup itg ON dt.itemtypegroupnum = itg.itemtypegroupnum
INNER JOIN hsi.scanqueuexit si ON dt.itemtypenum = si.itemtypenum
INNER JOIN hsi.scanqueue sq ON sq.queuenum = si.queuenum
WHERE dt.itemtypenum > 100
AND dt.itemtypegroupnum IN (102,104,111,112)
ORDER BY "Document Type"

[Jeffrey_Edmunds · Accepted solution]

Hi Edward,

I think the problem is in the type of JOIN statement you're using. Try changing the INNER JOINs into LEFT JOINS like this:

SELECT dt.itemtypenum AS "Document ID", dt.itemtypename AS "Document Type", itg.itemtypegroupname
AS "Document Type Group", sq.queuename AS "Scan Queue"
FROM hsi.doctype dt
LEFT JOIN hsi.itemtypegroup itg ON dt.itemtypegroupnum = itg.itemtypegroupnum
LEFT JOIN hsi.scanqueuexit si ON dt.itemtypenum = si.itemtypenum
LEFT JOIN hsi.scanqueue sq ON sq.queuenum = si.queuenum
WHERE dt.itemtypenum > 100
AND dt.itemtypegroupnum IN (102,104,111,112)
ORDER BY "Document Type"

An INNER join is going to show only those records that exist in both tables. A LEFT join (aka LEFT OUTER JOIN) is going to show all of the records in the left table and only those in the right table that match.

Jeff

[Ed_Prunier]

Thank you Jeff, that was very helpful.  I'm much closer now.  

Ed

[Jeffrey_Edmunds]

No problem - glad to help!

Jeff

[Mike_Saville]

You really will want to run two separate queries to get clean results.  The query above will return documents that look like the are and aren't in scan queues.  The two below will give to separate results.  The first will return document types that are in scan queues the second will return any document types that are not in any scan queues.

select dt.itemtypenum, dt.itemtypename, itg.itemtypegroupname, sq.queuename from hsi.doctype dt

join hsi.scanqueuexit si on si.itemtypenum = dt.itemtypenum

join hsi.scanqueue sq on sq.queuenum = si.queuenum

join hsi.itemtypegroup itg ON dt.itemtypegroupnum = itg.itemtypegroupnum

order by itemtypename

select dt.itemtypenum, dt.itemtypename, itg.itemtypegroupname from hsi.doctype dt

left outer join hsi.scanqueuexit si on si.itemtypenum = dt.itemtypenum

join hsi.itemtypegroup itg ON dt.itemtypegroupnum = itg.itemtypegroupnum

where si.queuenum is null

order by dt.itemtypename