Trouble Joining Multiple Tables

Dec 14, 2004

Hi,
This seems like a basic problem but I can't figure out how to resolve it.

I have a query :

SELECT PR.WBS2, SUM(LedgerAR.Amount * - 1) AS Expr5, LB.AmtBud AS budget
FROM PR LEFT OUTER JOIN
LedgerAR ON PR.WBS1 = LedgerAR.WBS1 AND PR.WBS2 = LedgerAR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND PR.WBS3 = LB.WBS3
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> 'zzz') AND (PR.WBS2 <> '98') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (PR.WBS1 = '001-298')
GROUP BY PR.WBS2, LB.AmtBud
ORDER BY PR.WBS2


The output of the above query:

WBS2Expr5budget
0141
0141953000
0143
121724540
1217500
1217622.5800
12171000
12172000
12174000
12174500
121772908000
121793513500
12173445018000
12176596032000
12173801044000
121838100
121913224.5
1220
1221
122262000
12224000
122312702


I want to sum up the middle column and last column grouping by wbs2. However, when I do SUM(lb.amtbud) the budget column is not summing correctly it is summing the column as if the data appeared like this:

0141
01410101410101410103000
01410101410101410147.53000
01410101410101410147.53000
0143
014305
1217
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
1217010121701012170101017.58000
121701012170101217010382.58000
12170101217010121701027.58000
121701012170101217010302.58000
12170101217010121701027.58000
121701012170101217010382.58000
121701012170101217010302.58000
1217010121701012170104958000
1217010121701012170102008000
1217010121701012170101017.58000
1217010121701012170101182.58000
1217010121701012170101952.58000
1217060
1217061
121708012170804000



So as a result I am getting 9000 where wbs2 = '0141'

I figure that in my top query I am not joining something correctly. Could someone point out what I am doing wrong?

Thank You.

:)

View 2 Replies


ADVERTISEMENT

Joining Multiple Tables

Mar 5, 2007

table - employee
empdcno name
1 jon
2 peter
3 john
4 n1
5 n2
6 n3
7 n4
8 n5
9 n6
10 n7

table - personalinfo
empdcno telno address
1 111 aaa
2 222 bbb
3 333 ccc
4 444 ddd
5 555 eee
6 666 fff
7 777 ggg
8 888 hhh
9 999 iii
10 000 jjj

table - hrappempeducs
empdcno schoolcode degree
1 A1 BSCS
3 A2 BSIT
5 A3 degree1
9 A3 degree2
10 A2 degree3

table - hrsetschools
schoolcode schname schaddress
A1 Harvard usa
A2 LaSalle philippines
A3 UP india
A4 s1 peru
A5 s2 japan

Result expected:

name telno address degree schname

jon 111 aaa BSCS Harvard
peter 222 bbb null null
john 333 ccc BSIT LaSalle
n1 444 ddd null null
n2 555 eee degree1 UP
n3 666 fff null null
n4 777 ggg null null
n5 888 hhh null null
n6 999 iii degree2 UP
n7 000 jjj degree3 LaSalle

I am not quite familiar with joins.
Thanks
-Ron-

View 4 Replies View Related

Joining Multiple Tables In A View.

Dec 5, 2005

I have three tables
 
1st table is Student
StudnetID (pk)
Other fields…
 
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
 
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
 
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…
 
StudnetID: 1
Name:  John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager:   123-456-7890
Fax:
Memo: This is one student record.
 
Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won’t have.
Thanks in advanced,
Nathan Rover

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

Joining Tables Through Multiple Criteria

Oct 14, 2013

I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.

Here is table a:

CREATE TABLE #A(
naics INT,
ust_code INT,
port INT,
all_qty_1_yr FLOAT,

[Code] ....

And here is table b:

CREATE TABLE #B(
naics INT,
ust_code INT,
port INT,
stat_month INT,
Cum_qty_1_mo FLOAT,

[Code] ....

I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’

/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]

[Code] ....

output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like

* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)

View 1 Replies View Related

Joining Tables With Multiple Criteria

Jun 9, 2014

I have a straight-forward select query to show work orders for a particular customer as below. I want to add a field value from another table, deltickitem diwhich contains contract records. I need to include the field di.weekchg to show the weekly hire rate, but the joined query must ensure that the both the contract number matches that in the original select and that the item number matches that in the actual select. Additionally, there is the problem that the item can appear more than once in the deltickitem table against a particular contract (if item has been off-hired and then re-hired on the same contract number) - in this case the query must select the record with the highest di.counter number, which I haven't worked out how to put in my query.

This is my basic code, but I keep ending up with duplicate work order lines in my result set.

Select wh.worknumber, wh.custnum, wh.contract, wh.sitename, wh.itemcode, wh.regnum, m.name, di.weekchg,
wh.date_created, wh.task_descr, wh.actual_labour_sale+wh.actual_parts_sale as [Repair Cost]
From worksorderhdr wh Left Join
inventory iv On iv.item = wh.itemcode inner Join
models m On m.id = iv.model_id left join deltickitem di on di.dticket = wh.contract
where wh.custnum = 'BARRATNE' and wh.rejected <> 1 and wh.charge_to_cust = 1
order by wh.date_created

View 9 Replies View Related

Joining Multiple Tables With Conditions?

Mar 19, 2008



Hi,
I have a tbl called BANK.

This shows a banking history of transactions and includes a field called TransType and a field called PaymentID.


I also have two other tables called Suppliers and SubContractors.

For each record in the bank, I need to match up a record in either the suppliers or subcontractors tbl based on the PaymentID value. I know if the record relates to either a Supplier or Subcontractor based on the value of the TransType field which will be either SUPPLIER or SUBCONTRACTOR or Null (in which case a match doesn't matter)


I have a working query based on joining just the Supplier tbl.. but how do I do the join to the other tbl aswell?


So overall, for each record in the bank, if the transtype is SUPPLIER I need to look in the supplier tbl for a match for that paymentID, and if the transtype is SUBCONTRACTOR, I need to do the same but in SUBCONTRACTOR tbl.

How would I best write that?

thanks for any help!

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 2012 :: Multiple Joining Tables - Duplicate Records

Jul 14, 2014

I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.

select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,

[Code]....

View 2 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

SQL Server 2008 :: Query Joining Multiple Tables Getting Duplicates?

May 17, 2013

I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?

select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id

There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.

View 9 Replies View Related

SQL Server 2014 :: Eliminate Duplicate Rows When Joining Multiple Tables

Jun 8, 2015

We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.

SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,

[Code] ....

View 9 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

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

Joining Three Tables

Jul 19, 2007

OK this is probably a really simple question, but I need to join three tables and I dont understand which types of "JOIN"s to use or how to lay it all out. Just as an example, this is what i want to do:

(table_name | field1, field2,)

kids_tbl | id, first_name, hair, eyes

hair_tbl | id, hair_color

eye_tbl | id, eye_color


So I want to replace the hair and eye color in the kids_tbl (which are numbers corresponding with the ids in the other tables) with the actual word from the correct table.

I have been reading up on this but it just isn't making sense to me.

View 4 Replies View Related

Joining Tables

Dec 5, 2007

Hi! I am trying to join two tables and use BETWEEN to restrict record selection. I have a table called, employee and a table called jobs. How do I write a query where I join the two and restrict the record selection by using the employee's salaries?

Thanks for any help!

Scott

View 19 Replies View Related

Joining Two Tables

Jan 7, 2008

Hi guys,

Good day!

I need your help!

I want to get an accurate data.

Thi is a the sample table.

table 1
------------------
uniqueid acctno name balance
1 0201 sam 102.00
2 0202 mel and geo 105.00
3 0202 mel and geo 105.00

table 2
--------------
uniqueid name
1 sam
2 mel
3 geo

table 3
---------------
uniqueid acctno
1 201
2 202

table 4
------------
acctno interestdate
201 10/15/02

***what can I do to get the following result? How can I join those tables. Hope u can help me! Thanks..

uniqueid acctno name balance interestdate
1 201 sam 102.00 10/15/02
2 202 mel and geo 105.00

View 3 Replies View Related

Joining 4 Tables

Jan 9, 2008

Hi guys please help!

Given the following tables:
table 1
1ClientNo 1BirthDate 1FirstName1MiddleName1LastName
100 08/12/1982TomMerry Hanks
101 08/13/1982May Hero Thompson
102 08/14/1982DrewBerry More
103 08/15/1985MinervaLee Potter
table 2
2ClientNo2AccountNo
100 18010515
101 18010450
102 18010220
103 18010220

table 33AccountNo3Name 3Balance
18010515Tom Merry Hanks 100
18010450May Hero Thompson200
18010220Drew and Minierva200
18010220Drew and Minierva200
table 44AccountNo4DatePosted
1801051510/11/2005
18010450
What query can I build so that I can get this result?

ClientIDAccountNoName Balance DatePosted
100 18010515Tom Merry Hanks 100 10/11/2005
101 18010450May Hero Thompson 200
102 18010220Drew and Minierva 200


Thanks!

View 2 Replies View Related







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