January 21, 2016 at 11:58 am
hi there.
I am very new at this, here goes.
I am have a user field table, that has two table fields that have different values in each parent table. both tables have a different type-id which differentiate the two apart
For example:
tableF1 = Relationship - table values are: V1, V2, V3 (type-id 98)
tableF2 = owner - table value are: O1, O2, O3 (type-id 120)
I want to reference both and get both table values in two separate columns
column1 column2
v1 O1
how would I do this?
my code shows the nulls and I don't want to see the nulls
Sample code below:
select
case when a.Type_Id = 98 then a.Description end As Relationship,
case when a.Type_Id = 120 then a.Description end As Owner
from User_field_table A
inner join user_field_table2 B on a.Type_Id = b.Type_Id and a.Code_Id = b.Code_Id
where b.Type_Id = a.Type_Id
I get
relationship Owner
NULL O1
v1 Null
Please help!!
January 21, 2016 at 1:39 pm
Your joins are not going to work. You have type_id 98 in one table and type_id 120 in another. What is Code_ID? How do these tables relate to each other?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2016 at 2:00 pm
Table a and table b link together using type ID and code ID.
Table a contains the description of the fields ie. Relationship and owner and their respective values within the fields. If you understand what I'm saying
For example : relationship is the parent name and the child values are V1. V2. V3 same as owner. Same type ID and code ID
January 21, 2016 at 2:17 pm
You really need to give us more information than this.
Mike has included a set of two links in his reply to you. Please click them, read them, then follow the instructions.
January 21, 2016 at 7:00 pm
My best guess at the code, known as a "cross tab" query, is below. You're not using anything from table B in the code. Are these really the same table with different types to identify each piece of data?
select a.code_id,
MAX(case when a.Type_Id = 98 then a.Description end) As Relationship,
MAX(case when a.Type_Id = 120 then a.Description end) As Owner
from User_field_table a
group by a.code_id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 21, 2016 at 11:18 pm
here my actual code to make things clear on what im trying to achieve here:
select
D.First_Name as FirstName,
D.Name as Surname,
D.Phone_1 as Main,
D.Phone_3 as Cell,
case when a.Type_Id = 98 then a.Description end As Relationship,
c.DateCol as Date_of_contact,
C.TextCol as Reference,
case when a.Type_Id = 120 then a.Description end As Owner
from AMGR_User_Field_Defs A
inner join AMGR_User_Fields B on a.Type_Id = b.Type_Id and a.Code_Id = b.Code_Id
inner join AMGR_Notes C on b.Client_Id = c.Client_Id
inner join AMGR_Client D on d.Client_Id = B.Client_Id
where C.Contact_Number = d.Contact_Number
and c.Client_Id = D.Client_Id
and a.Type_Id in (98,120)
Firstname | Lastname| Main | cell | RelationShip| dateofcontact| Reference| Owner
Sarah | dex | | | patient | 2016.01.15 | approval | Null
Sarah | dex | | | null | 2016.01.15 | approval | almari
Piet | rademeyer| | | patient | 2016.01.15 | approval | Null
Piet | rademeyer| | | null | 2016.01.15 | approval | almari
I don't want to see the nulls. so I want to get the following out of my query:
Firstname | Lastname| Main | cell | RelationShip| dateofcontact| Reference| Owner
Sarah | dex | | | patient | 2016.01.15 | approval | almari
Piet | rademeyer| | | patient | 2016.01.15 | approval | almari
January 22, 2016 at 12:59 am
Julie, from this point on two things can happen.
One is that me or someonee else, despite the still incomplete information you have given, decides to start guessing what the missing details might be, posting code that sort of works, and then you and them will be exchanging messages for another week until you have a solution that does what the person helping you thinks you want, that does on your test data what you want it to do, and that hopefully works correct on your production data as well.
The other one is that you go back, read that link I already pointed out to you in my previous reply, and actually supply us with all the information we need to help us solve your problem.
(For the sample data, please make sure to include all relevant special cases)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply