SubQuery (?) help

[simonsoot]

Fairly new to SQL, and I am a little stumped, though I bet the answer is easy.

I have the below query set up and results; but then need to ALSO pull another field in the same table [cicmpy] based on the column named "Dealer". I assume this is a subquery?

SELECT [cicntp].[cmp_wwn],[cicmpy].[syscreated] AS Date_Created,
[Cicmpy].[Cmp_code] AS code,
[cicmpy].[cmp_acc_man] AS RSM,
[cicmpy].[cmp_reseller] AS Dealer,
[cicntp].[cnt_f_name] AS First_Name,
[cicntp].[cnt_l_name] AS Last_Name,
[Cicmpy].[Cmp_name] AS Lead_Company_Name,
[Cicmpy].[cmp_type] AS Type,
[cicmpy].[cmp_fadd1] AS Street_Address,
[cicmpy].[cmp_fcity] AS City,
[cicmpy].[StateCode] AS State_Province,
[cicmpy].[cmp_fpc] AS Postal_Code,
[cicmpy].[cmp_fctry] as Country,
[cicmpy].[cmp_tel] as Phone_No,
[cicntp].[cnt_email] AS Email_Contact,
[cicmpy].[cmp_e_mail] AS Email_Company
FROM [TS-SQL2].[Synergy].[dbo].[cicmpy] left outer join [TS-SQL2].[Synergy].[dbo].[cicntp] on [cicmpy].[cmp_wwn] = [cicntp].[cmp_wwn]
Where [cicmpy].[cmp_type] = 'L' and YEAR({fn NOW()}) = YEAR([cicmpy].[syscreated]) and month({fn NOW()}) = month([cicmpy].[syscreated])
ORDER BY Lead_Company_Name

 

[simian336]

Nope, If you just need anouther field just add it to the list like this....
You already have one name "Dealer" so you might need to do some renameing.

Simi

SELECT [cicntp].[cmp_wwn],[cicmpy].[syscreated] AS Date_Created,
[Cicmpy].[Cmp_code] AS code,
[cicmpy].[cmp_acc_man] AS RSM,
[cicmpy].[cmp_reseller] AS Dealer,

[cicmpy].[Dealer] as Dealer2,

[cicntp].[cnt_f_name] AS First_Name,
[cicntp].[cnt_l_name] AS Last_Name,
[Cicmpy].[Cmp_name] AS Lead_Company_Name,
[Cicmpy].[cmp_type] AS Type,
[cicmpy].[cmp_fadd1] AS Street_Address,
[cicmpy].[cmp_fcity] AS City,
[cicmpy].[StateCode] AS State_Province,
[cicmpy].[cmp_fpc] AS Postal_Code,
[cicmpy].[cmp_fctry] as Country,
[cicmpy].[cmp_tel] as Phone_No,
[cicntp].[cnt_email] AS Email_Contact,
[cicmpy].[cmp_e_mail] AS Email_Company
FROM [TS-SQL2].[Synergy].[dbo].[cicmpy]
left outer join [TS-SQL2].[Synergy].[dbo].[cicntp]
on [cicmpy].[cmp_wwn] = [cicntp].[cmp_wwn]
Where [cicmpy].[cmp_type] = 'L' and YEAR({fn NOW()}) = YEAR([cicmpy].[syscreated])
and month({fn NOW()}) = month([cicmpy].[syscreated])
ORDER BY Lead_Company_Name

 

[simonsoot]

simi,

thanks for the quick reply. I read my question and realize I was not clear.

for example, my original (above) query returned three results, and I need to pull more columns based on one field in the 3 results.

does that make sense?

 

[SQLBill]

Can you give us sample data to show what you want? It would make it much easier on us. You might be able to use a CASE statement.

SELECT [cicntp].[cmp_wwn],[cicmpy].[syscreated] AS Date_Created,
[Cicmpy].[Cmp_code] AS code,
[cicmpy].[cmp_acc_man] AS RSM,
CASE WHEN [cicmpy].[cmp_reseller] = 'DealerA'
     THEN ......
     WHEN .......
     THEN ......

It's difficult to create a CASE statement for you or to decide if that will even work if we don't know how it is going to be used.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

How about something like

CASE cicmpy.cmp_reseller WHEN 'A' THEN cicmpy.col2 
WHEN 'B' THEN cicmpy.col3 
ELSE cicmpy.col4
END AS new column

Is this what you are looking for?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[simian336]

Well I think we are getting closer... By "three results" I am going to assume you mean 3 records. Normally if you want a subquery based on results you would either associate all 3 records or further narrow to a single record but not one of 3...

Post some example data and then your expected results and that might help us.

Simi