jnelson993
Posts: 959
Score: 132
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline
|
Well, a couple of points.
Yes, you do need the LEFT JOIN to accomplish that, but you also need to know that when you have a filter predicate for a left-joined table/view in your WHERE clause, it makes the query act like an INNER JOIN. --> READ THIS <-- for more information. That's likely where you were getting hung up. So really, the filter predicates need to go up into the FROM clause to make that work right. What you're trying to accomplish is to show the v_GS_Share records where the filepath is like the filepath for the MP3 records (the way I understand it) of the v_GS_SoftwareFile.
Second, do you want it to show ALL files, or you want it to show all MP3 files? Cuz you're filtering on .mp3 files currently, but you said "The trouble is that I want the query to show ALL files with their paths..." Just making sure you're not expecting all files to show up when you filter for MP3.
Now, I don't have v_GS_Share in my environment, so I don't quite understand what you're doing with that "shr.path0 + '[ \]' but I'm going to assume you know what you're doing...the bracket operator [ ] is a special wildcard in SQL --> READ THIS <-- if for more information about the bracket wildcard.
So, having said all that, and again, not having the view to test in my environment, I'd do something like this:
SELECT
sys.name0,
sys.user_name0,
inv.filename,
inv.filesize,
inv.modifieddate,
inv.filepath,
shr.name0,
inv.filemodifieddate
FROM
v_r_system sys
INNER JOIN v_gs_softwarefile inv
ON sys.resourceid = inv.resourceid
AND inv.filename LIKE '%.mp3'
LEFT JOIN v_gs_share shr
ON inv.resourceid = shr.resourceid
AND inv.filepath LIKE shr.path0 + '[ \]'
WHERE sys.name0 LIKE 'xxx%'
ORDER BY sys.name0
- The first red line would make sure v_GS_SoftwareFile is only pulling MP3 records
- The INNER JOIN is changed to a left join so you get your NULL records
- V_GS_Share is now joining to v_GS_SoftwareFile instead of to SYS to make sure we only get records that match the share records on machines with mp3s. That might not be necessary, but it's a little extra measure. If I had data I'd test it.
- The last red line would make sure v_GS_Share join only returns records when the software file path is the share path plus a space or a backslash (again, assuming you know that you're using a bracket wildcard)
Make any sense?
_____________________________
Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts
|
Printable Version
Getting NULL values to display (not just a LEFT JOIN th... - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread