Joining Tables - Return Name / Description And Keyword For Every Record ID

May 7, 2012

I have 3 tables I'm trying to join:

Table 1: Contains ID, Name, Description
Table 2: Contains ID, Keyword
Table 3: Is lookup table for tables 1 & 2

I need a query to return a table having columns of:
Name, Description, Keyword for every record ID in Table 1

The issue is that table 2 contains multiple keywords for each key ID and my query returns an error: Subquery returned more than 1 value.

When I run this query using a specific key, it returns the correct information the way I want it:

DECLARE @ServiceID VARCHAR(10)
SET @ServiceID = '35'
DECLARE @Name VARCHAR(8000) DECLARE @Desc VARCHAR(8000) DECLARE @Keywords VARCHAR(8000)
SELECT @Name = [DefService].[Name],@Desc = [DefService].[Description],@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name] FROM [DefService] INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] WHERE [DefService].[ServiceID] = @ServiceID PRINT @Name + ' | ' + @Desc + ' | ' + @Keywords
SELECT @Name AS Name,@Desc AS Description,@Keywords AS Keywords

Results:
NameDescriptionKeywords
GoToMyPC - Account RequestRequest a Citrix GoToMyPC account.GTMPC, GoTo, application, software, install, Installation, applications

When I run the same query without a specific key it fails. The results only return a single row containing Name, Description and then ALL keywords for every key ID...very odd behavior.

BTW, I need to do this in a single SQL query and not a stored proc or other method.

View 10 Replies


ADVERTISEMENT

Transact SQL :: Joining Two Tables - Return Max Date

Oct 5, 2015

I am joining on two tables, and returning the values that do not exist in #topoftheline -- well now I see that their are multiple calltimes so I want to return ONLY the most recent call time.  What would I change in my syntax to only return that most recent datetime?

Create Table #topoftheline
(
callreceived datetime,
callerfirstname varchar(100),
callerlastname varchar(100),
callnotes varchar(4000)
)

[Code] ...

View 3 Replies View Related

Joining Three Tables With Multiple Record

Dec 9, 2014

We have Three Tables in sqlserver2012

Master Table

OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz

Child1 Table

OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))

Child2 Table

PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White

We want to have result like

OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow

1 1 ABC Red Blue White x x
Blue

2 2 Bcd x x x Green Red

I have tried

;with mycte as (
select ms.OrderID,ms.PackageID
,ms.CustomerName
, Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),' '),'Row','') rowNum
,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum

[Code] .....

it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record

View 5 Replies View Related

Return Single Records By Joining Multiple Tables

Jun 4, 2008

I would like to know if it's possible to return a single record by joining the tables below. [Persons]
PersonID [int] | PageViewed [int]
=============== =================
1 10
2 5
3 2
4 12


[PersonNames] - PersonID JOINS Persons.PersonID
PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int]
=============== ============== ======================= ===================
1 1 Samantha Brown 5
1 2 Samantha Green 10
2 3 Richard T 10
3 4 Riko T 0
4 5 Sammie H 0


[AltNames] - backup for searches caused by common spelling mistakes
AltNameID [int] | AltNames [nvarchar]
================ =============================
1 Sam, Samantha, Sammie, Sammy
2 Riko, Rico


[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID]
NameID [int] | AltNameID [int]
============= ================
1 1
4 1
3 2 
This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch
========= ============ ================= =================
1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy
2 5 Richard T Richard T
3 2 Riko T Riko T, Riko, Rico
4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
 
 
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
 
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field.
Any help would be greatly appreciated.
Thanks, Pete.

View 4 Replies View Related

SQL Server 2012 :: Joining Three Tables With Multiple Record?

Dec 9, 2014

We have Three Tables in sqlserver2012

Master Table

OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz

Child1 Table

OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))

Child2 Table

PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White

We want to have result like

OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow

1 1 ABC Red Blue White x x
Blue
2 2 Bcd x x x Green Red

I have tried

;with mycte as (
select ms.OrderID,ms.PackageID
,ms.CustomerName
, Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),''),'Row','') rowNum
,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum
From child1 c inner join MasterTable ms on c.Orderid=ms.orderid)

[code]....

it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record

View 2 Replies View Related

Joining Record With The Most Recent Record On Second Table

Apr 23, 2008

Could anybody help me with the following scenario:

Table 1 Table2

ID,Date1 ID, Date2

I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.

Example:

Table1 data Table2 Data

ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008


The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1

Any help would be greatly appreciated.
Thanks

View 4 Replies View Related

How To Return Event Description From Stored Proceedure?

Nov 15, 2006



I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..



can the stored proceedure send the text?

View 1 Replies View Related

Incorrect Syntax Near The Keyword 'RETURN'

Dec 12, 2007

Hi ,
Can you tell me what is wrong with the following function. I'm getting an Incorrect syntax near the keyword 'RETURN'
FYI. I'm new in this and I'm starting to write functions in SQL server 2005.

ALTER FUNCTION [dbo].[f_sty_print_menu_per_role_per_app2]
(
-- Add the parameters for the function here
@menu_name VARCHAR(255) = NULL ,
@is_user VARCHAR(255) = NULL ,
@is_appl VARCHAR(255) = NULL
)
RETURNS VARCHAR
AS
BEGIN
-- Declare the return variable here
DECLARE @Rows NUMERIC(10)
DECLARE @RETURN_ENABLED VARCHAR(1)

-- Add the T-SQL statements to compute the return value here
SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name
Group By Profile_INdex

Exec (@Rows);

IF @Rows > 0
SELECT @RETURN_ENABLED = 'N'
ELSE
SELECT @RETURN_ENABLED = 'Y';
END

-- Return the result of the function
RETURN @RETURN_ENABLED;

END

Thank you

View 4 Replies View Related

Editing Data With A Filter Always Return Error - Incorrect Syntax Near Keyword WHERE

Aug 26, 2015

Every time i open table to edit data with SSDT and filter some column to find row to edit, when i try to do the edit i get the error

 "Incorrect syntax near keyworld 'WHERE'",

But then if i give ok and esc, refreshing data the value i entered is stored.

View 2 Replies View Related

Joining Table On On Last Entered Record

Jan 2, 2007

Dear All,

What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.

I'd like to select every record in table A but only joining the last relevant record from table B. So:

Table A:

A1 Prj1
A2 Prj2

Table B:

B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007

So I'd like to list using the most efficient way this:

A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007

I'm assuming this is NOT the most efficient way:

select A, (select top 1 date from B orderBy ...)

Any suggestions?

View 6 Replies View Related

Tables Description

Jun 3, 2007

I have hundreds of tables in my database.
Is there a way to get a list of them with a description
of their content (the procedure and the transaction they
are related to, the way columns are created)?

Thank you. Anna - Verona (Italy)

View 2 Replies View Related

How To Return First Record Child Record And Count

Jan 31, 2006

I've been looking for examples online to write a SPROC to get some data. Here are the tables.

Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]

I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.

View 3 Replies View Related

SQL Challenge - How To Return A Record Set Starting At A Particular Record?

Feb 25, 2007

I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.

View 4 Replies View Related

Adding Description To Tables And Fields

Oct 27, 2000

Can I add a description to a table or field? The way access gives you to add a description to a field.


Thanks!

View 1 Replies View Related

Where Is Stored Columns Description For SQL Server Tables?

Jul 20, 2005

I am a beginer in SQL Server and if someone knows I would like to knowwhere are stored columns description - am refering at that columndescription that can be write for each column when you create a tablein a SQL Server Database using SQL Server Enterprise Manager.I want to get this information from the database and display it in aC# application, I 've tryed COLUMNPROPERTY function and sp_columnsprocedure, but this are not giving me the column definition, onlytype, name, etcThanksMaria

View 4 Replies View Related

Joining Tables - But Data Not Always In All Tables

Aug 18, 2007

I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views.

My scripts are as follows:
Main View
SELECT
CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID,
CUSTOMER_POLICY_DETAILS.HISTORY_ID,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE,
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID,
CUSTOMER_INSURED_PARTY.SURNAME,
SYSTEM_INSURER.INSURER_DEBUG,
SYSTEM_SCHEME_NAME.SCHEMENAME,
CUSTOMER_POLICY_DETAILS.POLICYNUMBER,
--TotalPayable
IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal
WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS TotalPayable,
--NetPremium
IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID
WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS NetPremium,
--IPT
IsNull(SUM(CASE
WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS IPT,
--Fee
IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID
WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS Fee,
--TotalCommission
IsNull(SUM(CASE
WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS TotalCommission

FROM
ACCOUNTS_CLIENT_TRAN_LINK
INNER JOIN ACCOUNTS_TRANSACTION
ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID
INNER JOIN ACCOUNTS_TRAN_BREAKDOWN
ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID
INNER JOIN LIST_TRAN_BREAKDOWN_TYPE
ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID
INNER JOIN CUSTOMER_POLICY_DETAILS
ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND
CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID
INNER JOIN SYSTEM_INSURER
ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID
INNER JOIN SYSTEM_SCHEME_NAME
ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID
INNER JOIN CUSTOMER_INSURED_PARTY
ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND
ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID
WHERE
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY'

GROUP BY
CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID,
CUSTOMER_POLICY_DETAILS.HISTORY_ID,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE,
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID,
CUSTOMER_INSURED_PARTY.SURNAME,
SYSTEM_INSURER.INSURER_DEBUG,
SYSTEM_SCHEME_NAME.SCHEMENAME,
ACCOUNTS_TRANSACTION.Transaction_ID,
CUSTOMER_POLICY_DETAILS.POLICYNUMBER

Add on View 1
CREATE VIEW TOPCARDPA AS
select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01'

Add on View 2
CREATE VIEW TOPCARDRESC AS
select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01'

Join Result Script
SELECT
TOPCARD.AUTHORISATIONUSER,
TOPCARD.AUTHORISATIONDATE,
TOPCARD.TRANSACTION_CODE_ID,
TOPCARD.SURNAME,
TOPCARD.INSURER_DEBUG,
TOPCARD.SCHEMENAME,
TOPCARD.POLICYNUMBER,
TOPCARD.TotalPayable,
TOPCARD.NetPremium,
TOPCARD.IPT,
TOPCARD.Fee,
TOPCARD.TotalCommission,
TOPCARDPA.SELECTED,
TOPCARDRESC.SELECTED
FROM
dbo.TOPCARD TOPCARD
INNER JOIN dbo.TOPCARDPA TOPCARDPA
ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND
TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID
INNER JOIN dbo.TOPCARDRESC TOPCARDRESC
ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID
AND
TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID

I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.

View 2 Replies View Related

Trouble With Temp Tables And UNION Keyword

Feb 18, 2008

I'm trying to summarize costs assigned to active jobs for a manufacturing business. I need to aggregate work in process (WIP) cost that resides in labor-transaction and part-transaction tables based on transaction types, and transaction dates. Some transactions increase the WIP cost of the job while others decrease WIP. The business needs to see how much $$ is tied up in each job as of a particular date -- the calculation is:
ToDate (cost of materials and labor assigned to job)
- ToInv (cost of materials returned to inventory)
- ToSales (cost of materials sold).

I developed this query incrementally and, so far, the #ToDate, #ToInv, and #ToSales temp tables seem to be populating with the correct data. My thought was to combine these three tables with a UNION and then extract the grand totals and here's where I started getting the following error:
------------------------------------------
Incorrect syntax near the keyword 'UNION'.
------------------------------------------
The problem is with the UNIONs going into #myTotal.

I would appreciate any help with this. Also, please let me know if you can suggest a better design for this. Thanks!

Below is a simplified version of my query:

--#ToDate
CREATE TABLE #ToDate (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToDate (JobNum, Cost)

--M&S To Date
SELECT pt.jobnum,
SUM(pt.extcost) AS Cost
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN ( <valid trans types> )
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

UNION -- This one works ok.

--L&B To Date
SELECT jh.JobNum,
sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost
FROM LaborDtl l
JOIN JobHead jh ON l.JobNum = jh.JobNum
WHERE jh.JobReleased = 1
AND l.PayrollDate < '2007-9-30'
GROUP BY jh.JobNum

--#ToInv
CREATE TABLE #ToInv (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToInv (JobNum, Cost)

SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

--#ToSales
CREATE TABLE #ToSales (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToSales (JobNum, Cost)

SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum

--#myTotal
CREATE TABLE #myTotal (JobNum varchar(14), Cost decimal (16,2), Source varchar(9))
INSERT INTO #myTotal (JobNum, Cost, Source)

SELECT d.JobNum, SUM(d.Cost) AS Cost FROM #ToDate d GROUP BY d.JobNum ORDER BY d.JobNum

UNION -- Problem**********************

SELECT i.JobNum, SUM(-1*i.Cost) AS Cost FROM #ToInv i GROUP BY i.JobNum ORDER BY i.JobNum

UNION -- Problem**********************

SELECT s.JobNum, SUM(-1*s.Cost) AS Cost FROM #ToSales s GROUP BY s.JobNum ORDER BY s.JobNum


--Select grand total for each job
SELECT JobNum, SUM(Cost) FROM #myTotal ORDER BY JobNum

--Drop temp tables
DROP TABLE #ToDate
DROP TABLE #ToInv
DROP TABLE #ToSales
DROP TABLE #myTotal

View 3 Replies View Related

Joining 2 Tables...

Apr 29, 2007

Hi. I'm new to SQL, and need to join 2 tables... any hints???
 table1:id (int)title(varchar(50))body(text)
 table2:id (int)title(varchar(50))body(text)
somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information:
table1:id          title                       body1           "first title"              "first body"2           "second title"         "second body"3           "third title"             "third body"
table2:id          title                       body1           "first title"              "first body"2           "second title"         "second body"3           "third title"             "third body"
 I would like to get...
id    table         title                     body3      1         "third title"             "third body"3      2         "third title"             "third body"2      1         "second title"         "second body"2      2         "second title"         "second body"1      1         "first title"              "first body"1      2         "first title"              "first body"
 Does anyone know how to get this? I am fairly flexible if i need to change things...
 cheers, eh!

View 1 Replies View Related

Inner Joining Two Tables

Aug 30, 2007

Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT     *,    _SubCategories.SubCategoryNameFROM     _ProductsINNER JOIN     _SubCategoriesON    _Products.DesignId = _SubCategories.SubCategoryIdWHERE    DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec 

View 11 Replies View Related

Joining Two Tables In .NET??

Jan 14, 2008

Hello all,
I have two datatables "customersReached " and "customersGuessed " and I want to combine them into one table only, the problem is that one table exeeded to the other by two fields, so what can I do???????
Mahmoudona

View 4 Replies View Related

Joining Two Tables

Apr 14, 2004

I've been trying to think about how I can do this. I have forums that I have written built around SQL Server. Basicly you have:

-A users Table
-A Posts Table
-A Replies Table.

Posts and replies have very similar structures. I'd like to be able to merge them and pick out the earliest post for said forum.

1 - is there a way to merge them so that the post date for both the replies and posts tables is contained in 1 column. If not is there a better alternative.

I'd also like to add indexing to the posts so I can do paging. Is there a way for me to add an index number to them while I can sort them anyway i want.

View 1 Replies View Related

Joining 2 Tables Using BETWEEN

Apr 18, 2006

I am using MS SQL Server 2005 on Windows XP with SQL Server Management Studio Express CTP. I am having issues with my query on joining 2 tables I created using BETWEEN to restrict the Salary. Table 1 is called Employee and Table 2 is called Job_title. The column Job_title_code is the only column that is in both tables which is how I am joining both tables. Here is my SQL query:


Code:

SELECT Employee.*, Job_title.*

From Employee

INNER JOIN Job_title

ON Employee.Job_title_code=Job_title.Job_title_code

WHERE Salary

BETWEEN 50000 AND 500000;



The results I am getting back are:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Job_title_code'.

I can't figure out how to fix this error. I feel like I have tried everything, so any help will be much appreciated. Thank you.

View 2 Replies View Related

Joining Tables

Jun 22, 2004

Hi,
I have a table with fields as partnerid, contractno.
The partnerid field has the Id number which can be a supplier or a customer.
I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.

Data in table
PId ContractNo
20045 1567
435 1567
123 1567
345 1678
1004 1678

I need to display the data in the following format.

PId(Supplier) PId(Customer)
20045 1567
20045 435
20045 123
345 1678
345 1004

But I'm getting the data replicated with all records joined every record.
Give the suggestion.

View 2 Replies View Related

Joining Three Tables

Oct 10, 2004

G'day,
I have got following 4 tables

Table 1
name age city

jack 20 Melbourne
Nick 30 Bendigo
Russ 28 Sydney

Table 2
name age city Company

jack 20 Melbourne AAA
Nick 30 Bendigo BBB
Russ 28 Sydney AAA
Marty 31 Perth AAA

Table 3

name age city Position

jack 20 Melbourne Manager
Nick 30 Bendigo Manager
Russ 28 Sydney Clerk
Marty 31 Perth Manager

Table 4

name age city datejoined

jack 20 Melbourne 09-09-2001
Nick 30 Bendigo 08-05-2001
Russ 28 Sydney 10-12-2000
Marty 31 Perth 11-11-1999

I want a query which extract the name, age and city from Table 2 (where name,age and city equals table1 values) and position from table3 where position is 'manager' else return null and date joined from table 4 only for the managers else return null.

so the result should be

name age city position datejoined

jack 20 Melbourne Manager 09-09-2001
Nick 30 Bendigo Manager 08-05-2001
Russ 28 Sydney null null


my query

SELECT b. name, b.age, b.city,b.company,c.position,d.datejoined
FROM Table1 a, Table2 b, Table3 c, Table4 d
WHERE
a.age=b.age
and a.name=b.name
and a.city=b.city
and b.age*=c.age
and b.name*=c.name
and b.city*=c.city
and b.position='Manager'
and b.age*=d.age
and b.name*=d.name
and b.city*=d.city

THE RESULT IS

jack 20 Melbourne Manager 09-09-2001
Nick 30 Bendigo Manager 08-05-2001
Russ 28 Sydney null 10-12-2000

When I try to join table4 with table i am getting a exception

Ps: as the original code was in SQL SERVER 6.5 I have to use *= for joins not keywords LEFT JOIN or RIGHT JOIN

hope yo guys can help me

regards
Melb

View 13 Replies View Related

Need A Little Help On Joining Two Tables

Feb 12, 2004

I have a bit of an issue that I can not seem to figure out and was hoping to get some feedback/advice from you all.

First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.

Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.

Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.

Example code:
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id ? users.group.id

The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?

Thanks in advance for any input.
T

View 6 Replies View Related

Joining 4 Tables

Apr 18, 2008

Hi,
i have some sql experience and can link tables but the link i am trying to get is not displaying how i need it to

here is the code i am using, which display logical results, but not the ones i need :P


qry = "SELECT * FROM wce_contact INNER JOIN wce_mailer_link ON wce_contact.UNIQUEID = wce_mailer_link.Contactid LEFT JOIN wce_mailer ON wce_mailer.uniqueid = wce_mailer_link.mailerid RIGHT JOIN wce_mailer_attachments ON wce_mailer_attachments.uniqueid = wce_mailer.fileid WHERE wce_contact.uniqueid = '"& Request.QueryString("id") &"'"



Ok i have these tables


wce_contact
This has the contacts name and address

wce_mailer
This holds the details of the mailer and a link to the wce_mailer_attachments, there would be multiple rows in wce_mailer_attachments table which link to 1 row in wce_mailer.


wce_mailer_link
This holds the wce_contact uniqueid, and the wce_mailer uniqueid. there will be many contacts to many mailers

wce_mailer_attachments
This holds an individual row for one attachment, but the uniqueid would be the same for multiple rows, Dependant on how many attachments the users adds. i.e. one mailer could have several attachments, they would all have the same uniqueid.

Basically the results i am getting using the join i built are displaying each attachment as a separate row when i display the mailers assigned to a contacts record. i need them to display in one single row where the uniqueids are the same in the wce_mailer_attachments and they match the only fileid in wce_mailer.

Hope that makes sense.

Thanks for any help

View 1 Replies View Related

Joining Tables

May 18, 2008

Hi guys can you help me joining three tables?

I used to join all those three but there's a redundant on it's value..

Table1
IDAccount
1A
2B
3C

Table 2
ID Name
1A
2B
3C

Table 3
AccountName
456ABC
456ABC
456ABC

with the following result:(what query should I use?)

IDNameAcctName AccountID2Name2ID3Name3
1AABC 456 2B3C

View 1 Replies View Related

Joining Two Tables

Jun 15, 2008

Hello friends,

Im a begginer in SQL Server and I just to ask for help on how to this this tables



table1

Field1
123
1234
12
154
123
321
123
21

Table2

Field1
test1
test2
test3
test4
test5
test6

desired output

Field1 Field1
123 test1
1234 test2
12 test3
154 test4
123 test5
321 test6
123
21

can i ask for script how to join this table? i try this script but it doest work.

select table1.col1,table2.col1
from table1
left join table2
ON table1.col1 is NOT NULL

Thanks in advance and more power!

View 2 Replies View Related

Joining 3 Tables

Aug 10, 2005

Hi all!
I have a problem joining three tables. My tables are:
rooms: room_id ¦ room_name
computers: computer_id ¦ computer_name ¦ room_id
bookings: booking_id ¦ computer_id ¦ date

I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.

I have gotten a correct result with:

SELECT rooms.room_name, t.computer_name, temp.date FROM rooms LEFT JOIN (SELECT computers.room_id, computers.computer_name, bookings.date FROM computers JOIN bookings ON (bookings.computer_id = computers.computer_id)) AS temp ON (rooms.room_id = temp.room_id)

but I would like to use something shorter like:
SELECT rooms.room_name, computers.computer_name, bookings.date from computers INNER JOIN bookings ON (computers.computer_id = bookings.computer_id) RIGHT JOIN rooms ON (computers.room_ID = rooms.room_ID)

That however gives me a list where every combination of rooms and computers is listed and bookings displayed when there is a match. I don't really get why this doesn't work and I've been staring at it for days. Could somebody help me out? (I'm using PHP and MySQL)

Thanks,
Nick

View 2 Replies View Related

Joining Tables

Dec 9, 2005

Hi all..

Let say i have 3 tables (tblA, tblB, tblC). I want to get all data in tblA. At the same time, i want to join the tables. If value in column aStatus equal to 1, it will get the value from tblB. If the column value is 2, it will refer to tblC. Else, if it 0, no need to refer to any table. just like inner join.. but i dun get the logic of how im going to do that.. can someone advise me on how to do the joining? please refer to picture below.

View 8 Replies View Related

Joining Two Tables

Aug 18, 2006

Table1

Table1ID ControlID (int)
1 100
2 101
3 102
4 103
5 104

Table2

Table2ID ControlID LastChangedDate (datetime)
1 100 08/02/2006 1:30 PM
2 100 08/16/2006 3:30 PM
3 101 08/18/2006 8:15 AM
4 102 08/14/2006 8:35 AM
5 102 08/17/2006 11:14 AM
6 103 08/18/2006 1:14 PM
7 104 08/17/2006 4:32 PM


How can you join Table1 controlID with Table2 controlID with the latest lastchangedDate or the greatest table2ID?

So my result would be

Table1ID ControlID Table2ID ControlID LastChangedDate
1 100 2 100 08/16/2006 3:30 PM

View 4 Replies View Related

Joining Tables

Feb 19, 2007

Hi,

Not sure if this as straight forward as it sounds!

Everytime I create new fields on our DB it creates new tables(our customer units have changed). What I need to do is pull the customer units from both tables into the same column. Is there a way to do this?

Thanks

Rob

View 2 Replies View Related

Joining Three Tables

Jun 11, 2007

I am having trouble building a query that joins three tables. The tables are:

Table------------key fields
Organizations---org_KEY (all fields start with org_)
Locations-------LOC_KEY--LOC_ORG_KEY (all fields start with loc_)
Users-----------USR_ORG_KEY----USR_LOC_KEY (all fields start with usr_)



Every location record is tied to a record in the organization table. (Via loc_key=org_key)
Each user record belongs to one organization and one location. (usr_org_key=org_key and usr_loc_key=loc_key)
Not all locations have a user. So if there is no user for any location ,I need the address info.

I need a result that is of all organizations, Locations and users
Not all locations have a user but they all have address information eg (loc_address1, loc_city, etc....)

The result will look like this:

Org_id, org_name, loc_id, loc_name, loc_address1,loc_city,usr_id,usr_name



grouped by organization, then location, then user

Thanks for all help

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved