Query one column twice in one statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query one column twice in one statement

Author

Topic

fnsmedia
Starting Member

14 Posts

Posted - 2002-06-27 : 05:14:21

I am a newbie...please help.

I have a table 'Company' that stores the names of both Customer and Supplier companies in the same column - Comp_name.

I need to query this column twice in the same statement, returning both the client and supplier names when searching on Job_Id.
All I am getting is one or other of the company names returned.

Does this make sense?

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 05:20:08

You need to have something in your database design that identifies the difference between a company and supplier name. i.e. an indicator col or something special in the data column comp_name to decide/seperate the data. Would it be possible for you to provide more information, including DDL?

<<monet makes money>>

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-06-27 : 06:04:01

Something like this may help:

SELECT Job_id,
ClientName = (SELECT Comp_name FROM Company WHERE Job.Client_id = Company.Comp_id),
SupplierName = (SELECT Comp_name FROM Company WHERE Job.Supplier_id = Company.Comp_id)
FROM Jobs

Or you could join the Company table twice, like:

SELECT Job_id,
Client.Comp_name,
Supplier.Comp_name
FROM
Jobs LEFT JOIN Company Client ON Jobs.Client_id = Company.Comp_id
Jobs LEFT JOIN Company Supplier ON Jobs.Client_id = Company.Comp_id

HTH

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 06:34:20

I think several assumptions have been made on that SQL. He/She did not mention the existence of a supplier_id or client_id col nor a supplier or client table.

More column info provided would help a lot.

Dan
www.danielsmall.com IF Factors

<<monet makes money>>

fnsmedia
Starting Member

14 Posts

Posted - 2002-06-27 : 06:47:32

Apologies for the lack of table /column info.

To query only the supplier/data provider company name I am using the following:


SELECT dbo.Company.Cmp_Name,
	dbo.Instruction.INS_Id
FROM  dbo.Company, dbo.DataProvider, dbo.Instruction, dbo.Search
WHERE  (dbo.Search.SRC_INS_Id = dbo.Instruction.INS_Id  AND 
 dbo.Search.SRC_DP_Id = dbo.DataProvider.DP_Id  AND 
 dbo.DataProvider.DP_Cmp_Id = dbo.Company.Cmp_Id)

To query only the client's company name I would use:


SELECT dbo.Company.Cmp_Name,
	dbo.Instruction.INS_Id
FROM  dbo.Company, dbo.Instruction, dbo.Contact
WHERE  (dbo.Contact.Cntct_Cmp_Id = dbo.Company.Cmp_Id  AND 
 dbo.Contact.Cntct_id = dbo.Instruction.INS_Cntct_id)

Does this give you everything you need?

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 07:02:42

I still need to know what the job_id column name is as it isnt listed but heres a piece of SQL to list the company and supplier name with an indicator to tell the type of entity being returned...

SELECT * FROM
(SELECT dbo.Company.Cmp_Name,
dbo.Instruction.INS_Id, 1 AS 'Type'
FROM dbo.Company, dbo.DataProvider, dbo.Instruction, dbo.Search
WHERE (dbo.Search.SRC_INS_Id = dbo.Instruction.INS_Id AND
dbo.Search.SRC_DP_Id = dbo.DataProvider.DP_Id AND
dbo.DataProvider.DP_Cmp_Id = dbo.Company.Cmp_Id)
UNION ALL
SELECT dbo.Company.Cmp_Name,
dbo.Instruction.INS_Id, 2 AS 'Type'
FROM dbo.Company, dbo.Instruction, dbo.Contact
WHERE (dbo.Contact.Cntct_Cmp_Id = dbo.Company.Cmp_Id AND
dbo.Contact.Cntct_id = dbo.Instruction.INS_Cntct_id)
) AS
WHERE [Your Job ID Table] ON JOBIDCOL???

I will refine it if you can tell me more about the JObID col...

Dan

<<monet makes money>>

fnsmedia
Starting Member

14 Posts

Posted - 2002-06-27 : 07:29:33

nuts, sorry.

dbo.Instruction.INS_Id is the JOb Id column.

Hope this helps
Thanks

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 07:37:33

SELECT * FROM
(SELECT dbo.Company.Cmp_Name,
dbo.Instruction.INS_Id, 'Supplier' AS 'Type'
FROM dbo.Company, dbo.DataProvider, dbo.Instruction, dbo.Search
WHERE (dbo.Search.SRC_INS_Id = dbo.Instruction.INS_Id AND
dbo.Search.SRC_DP_Id = dbo.DataProvider.DP_Id AND
dbo.DataProvider.DP_Cmp_Id = dbo.Company.Cmp_Id)
UNION ALL
SELECT dbo.Company.Cmp_Name,
dbo.Instruction.INS_Id, 'Company' AS 'Type'
FROM dbo.Company, dbo.Instruction, dbo.Contact
WHERE (dbo.Contact.Cntct_Cmp_Id = dbo.Company.Cmp_Id AND
dbo.Contact.Cntct_id = dbo.Instruction.INS_Cntct_id)
) AS CS.INS_Id = [Your Job ID Here]

Just cut'n'paste it in QueryA

HTH

Daniel Small MIAP
www.danielsmall.com

<<monet makes money>>

fnsmedia
Starting Member

14 Posts

Posted - 2002-06-27 : 08:04:39

Dan

Sorry but am being really dense here, in your last line what is the CS of CS.INS_Id = [Your Job ID Here]

cheers

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 08:12:31

No, you're not, my typo