SQL for report

[Cobra7]

Need a query thats a bit above me.

I need a report that shows all people who have software X and what version of Y. Y can have multiple versions.

tblSoftware.softwareName = 'Y' AND
(tblSoftware.softwareName LIKE 'YA%' OR tblSoftware.softwareName LIKE 'YB%' OR tblSoftware.softwareName LIKE 'YC%' OR tblSoftware.softwareName NOT LIKE 'YD%')

for example (I know thats valid code, just an example of what I'm looking for).

I don't need to display software X in the ourput, just the version of Software Y.

X=MP2 and Y=MS Office versions (XP, 2003, 2007). So if they show up on the report, they will have MP2 and it will display the Office version.

If you can get me a small template I can do the rest.

[Hemoco]

I tested it on the v4 beta, maybe that's the problem.

[Cobra7]

Very intresting way to make 2 tables like that. That will come in handy, thanks!

However it still wasn't working. I had to load the old report builder and I figured out the code I needed.

SELECT 
  tblComputers.Computername,
  tblSoftware.softwareName AS software1,
  tblSoftware1.softwareName AS software2,
  tblSoftware1.softwareVersion AS version2
FROM
  tblComputers
  INNER JOIN tblSoftware ON (tblComputers.Computername = tblSoftware.ComputerName)
  INNER JOIN tblSoftware tblSoftware1 ON (tblSoftware1.ComputerName = tblSoftware.ComputerName)
WHERE
  tblSoftware.softwareName = 'MP2 6.0 Oracle Edition' AND 
  (tblSoftware1.softwareName LIKE 'Microsoft Office Professional%' OR 
  tblSoftware1.softwareName LIKE 'Microsoft Office Standard%' OR 
  tblSoftware1.softwareName LIKE 'Microsoft Office 2000%' OR 
  tblSoftware1.softwareName LIKE 'Microsoft Office XP%')

Thanks!

EDIT: I had no idea that I could use () like that, I just happened to use them in my example to explain what I wanted, not knowing thats how to do it 🙂

[Hemoco]

try this:

Select tblComputers.Computername, tblSoftware.softwareName As software1,
  tblSoftware1.softwareName As software2, tblSoftware1.softwareVersion
  As version2
From tblComputers Inner Join
  tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
  tblSoftware tblSoftware1 On tblSoftware1.ComputerName =
    tblSoftware.ComputerName
Where tblSoftware.softwareName = 'Microsoft office xp professional' And
  tblSoftware1.softwareName Like 'adobe%'