I'm very new to the forum, and intend to utilize and take advantage of the community. Hopefully, over time, I can contribute.
I have a database that I managed to create the following list of assemblies that have 'Description1'
SELECT
a.AssemblyName,
CAST(uav.StringValue as nvarchar(255)) AS AttributeValue
FROM dbo.GEN_vAssembly a
LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name = 'Description1'
LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND uad.UserAttributeGUID = asa.UserAttributeGUID
LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID
WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'
and another to give me the same list of assemblies with the 'REV'
SELECT
a.AssemblyName,
CAST(uav.StringValue as nvarchar(255)) AS AttributeValue
FROM dbo.GEN_vAssembly a
LEFT JOIN dbo.GEN_tblUserAttributes ua ON ua.Name = 'REV'
LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND ua.UserAttributeGUID = asa.UserAttributeGUID
LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID
WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'
ORDER BY a.AssemblyName
Both scripts run well, but I'm trying to get an output of 'AssemblyName' 'Description1' and 'REV' as each columns.
When I use 'UNION ALL', it outputs two columns with the 'AssemblyName' instances populated twice in the one column.
I have done some dearching, but I honestly don't know what I'm looking for yet? What functions to search for, and what the correct syntax to use and so on. Out of necessity, I'm hoping I will gain the knowledge I need.
Appreciate any advice/help whatsoever.
KP
can you please post an example of what you're getting and what you want to get?
Here is a snapshot of what I'm getting returned:
AssemblyName Column1
6502-111-DRAWING INDEX
6502-111-DRAWING INDEX 1
6502-111-ST201A FR. 2 STBD BS INBD
6502-111-ST201A 1
6502-111-ST202A FR. 2 PORT BS INBD
6502-111-ST202A 1
6502-111-ST203A 1
6502-111-ST203A FR3 BS CL
6502-111-ST204A FR3+400 ASSEMBLY
6502-111-ST204A 1
6502-111-ST205A 1
6502-111-ST205A FR2+400 BS CL
6502-111-ST206A FR. 3+400 STBD
6502-111-ST206A 1
And here is a snapshot of what I'd like to see, if at all possible:
AssemblyName Column1 Column2
6502-111-DRAWING INDEX 1
6502-111-ST201A FR. 2 STBD BS INBD 1
6502-111-ST202A FR. 2 PORT BS INBD 1
6502-111-ST203A FR3 BS CL 1
6502-111-ST204A FR3+400 ASSEMBLY 1
6502-111-ST205A FR2+400 BS CL 1
6502-111-ST206A FR. 3+400 STBD 1
unfortunately not formatting really well for me.
wanting something like this:
instead of UNION why not:
LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name in ('Description1', 'REV')
???
You mean like this?
SELECT
a.AssemblyName,
CAST(uav.StringValue as nvarchar(255)) AS AttributeValue
FROM dbo.GEN_vAssembly a
LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name in ('Description1', 'REV')
LEFT JOIN dbo.GEN_tblUserAttributes ua ON ua.Name = 'REV'
LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND uad.UserAttributeGUID = asa.UserAttributeGUID
LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID
WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'
ORDER BY a.AssemblyName
This still only gives two columns. I'm guessing because the tables were created where the 'REV' and the 'Description1' both have 'AttributeValue' as a column name? This is what gets returned with your suggestion.
not quite. You don't need:
LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name in ('Description1', 'REV')
LEFT JOIN dbo.GEN_tblUserAttributes ua ON ua.Name = 'REV'
just
LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name in ('Description1', 'REV')
Also, there's nothing in the join to hook it up to the main table GEN_vAssembly. What column do then have in common?
yeah I caught your first catch.
I think the catch here, is that both attributes are coming from the same table, if I said that correctly.
Here is one of the tables the system creates:
and here is another table the system creates:
What if you comment that join and add a where clause:
and uad.Name in ('Description1', 'REV')
I'm getting a 'Incorrect syntax near the keyword 'AND'.' message. So I'm not picking up everything that you're laying down.
SELECT
a.AssemblyName,
CAST(uav.StringValue as nvarchar(255)) AS AttributeValue
FROM dbo.GEN_vAssembly a
LEFT JOIN dbo.GEN_tblUserAttributes uad AND uad.Name in ('Description1', 'REV')
LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND uad.UserAttributeGUID = asa.UserAttributeGUID
LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID
WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'
ORDER BY a.AssemblyName
thanks for your patience
more like this:
SELECT
a.AssemblyName,
CAST(uav.StringValue as nvarchar(255)) AS AttributeValue
FROM dbo.GEN_vAssembly a
LEFT JOIN dbo.GEN_tblUserAttributes uad ON a.? = uad.?
LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND uad.UserAttributeGUID = asa.UserAttributeGUID
LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID
WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'
AND uad.Name in ('Description1', 'REV')
ORDER BY a.AssemblyName
but you need to put something where the ? mark is.
So I thought that by replacing your '?' with 'Name' would do the trick based on the table snippet I passed along. I received an 'Invalid column name 'Name''
Maybe I'll just have to pull from the database with two different scripts, one for 'REV' and the other for 'Description1'
YOu could do that. but please more the check to the where clause and remove the extra join.
