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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Feb 10, 2008
Hi,
I'm having difficulties joining two tables. I have
Table 1:
--------
T1_1 T1_2 T1_3
10 100 a
10 150 a
20 50 a
30 300 b
30 120 a
40 560 a
40 230 b
Table 2:
--------
T2_1 T2_2 T3_3
10 1 3
10 1 4
20 1 2
20 1 8
30 0 4
40 1 5
40 1 7
Now I want to join these two tables (primary key TX_1) and get the sum of column T1_2 under the condition that T1_3 = a and T2_2 = 1 so that I get
Resulting table: T1_3 = a AND T2_2 = 1
----------------
T1_1 SUM(T1_2)
10 250
20 50
40 560
but I am at a complete loss as how to accomplish this! Any help would be highly appreciated. I hope I have provided enough information to make my question comprehensible.
Cheers,
Christin
View 10 Replies
View Related
Oct 14, 2006
Hi Guys,
I haven't written any SQL code for a few years and am stumbling on a basic join.
I have "Company" "Category" "Sub Category" with each sub category belonging to a category and each company having one category.
There is currently only one record in DB and I have joined all three tables but am getting multiple results (ie all combinations instead of just one record).
What is the best join to run?
Cheers
Doug
View 3 Replies
View Related
Jun 29, 2006
I have created a join statement with 3 tables in my database. The join wokers fine but the result is not on the format (Layout) I need it. Here is where I need help. Let me explain what I need to accomplish.
I have 3 tables in my database
1- Rates (Master Table)
Every Prepaid long distance card have a list of countries with individual rates
2- Countries_Template (Child table used to get the country name)
This table is to get copy of a list of countries every time a new card's rate is created
3- Card_Denominations (Child table where I create all the price denominations of each card)
Each cards have multiple cards denominations ($2, $5, $10, $20 and so on) so every time a card is inserted in the Cards table I insert the card denominations on the Card_Denominations table.
The format I need to return from my Query using above 3 tables have to look like this:
Flag Country Rate $2 $5 $10 $20
pic USA .20 500 750 1200 1700
Please help
Tia
Charles
---------Join Statement -----------------
Select Rates.Card_Id, Rates.Country_Id, Rates.Flag, Countries_Template.Country_Name, Card_Denominations.Card_DenominationFrom RatesRight Join Countries_TemplateOn Rates.Country_Id = Countries_Template.Country_IdRight Join Card_DenominationsOn Rates.Card_Id = Card_Denominations.Card_IdWhere Rates.Card_Id = 1
---------------------------------------------
View 1 Replies
View Related
Jan 18, 2004
Hi, I need help in joining 2 tables. This is not a regular 2 table join. it's little bit more complicated. Below is the working query I use. it gets the data I need. The problem I am having is joining the query below with another table.
-----------------------
SELECT BuddyID
FROM (SELECT TOP 10 BuddyID
FROM (SELECT TOP 20 BuddyID FROM Buddies Where UserID = 100025 ORDER by BuddyID) as a ORDER by BuddyID DESC) as b
ORDER by BuddyID
---------------
the query above will give me the BuddyID for about 75 records. I want to join the BuddyID on the "Buddies" table to the BuddyID on the "Users" table. I just don't know where to put "INNER JOIN Buddies ON Users.BuddyID = Buddies.BuddyID"
View 3 Replies
View Related
Sep 23, 2005
Hi,I've a problem joining 2 table. Say the tables are T1 and T2. I need SELECT T2.* from T2 WHERE T2.code = "@code" then I want ot join this results with T1 where T1.ID = T2.ID and need to show all the recods of T1. Any help appriciated.Cheers.-VJ
View 2 Replies
View Related
Jan 13, 2002
I'm developing a new library system to replace the old one. I'm using a text based system (DB2 on Linux). So I have to write all commands.
The old database has two tables:
OLDBook(isbn primary key, name)
OLDAuthor(firstname, lastname, isbn) (firstname+lastname+isbn=primary key)
For a new one I have created two tables:
Book(isbn primary key, name, authorID (foreign key))
Author(authorID, fname, lname)
I've already copyed names from OLDAuthor to Author and I've created the new keys (authorIDs). Book table is still empty.
Now the problem is that I can't copy right data to Book table. I've tried these commands:
1)
insert into Book (isbn, name, authorID) select OLDBook.isbn, Author.aurhorID, OLDBook.name from OLDBook, author, OLDAuthor where Author.fname = OLDAuthor.firstname AND Author.lname = OLDAuthor.lastname AND OLDAuthor.isbn = OLDBook.isbn
With this command I'll get a error message:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "MYUSERID.BOOK" from having duplicate rows for those columns.
SQLSTATE=23505
What I do wrong? Why I'm inserting duplicate rows/keys?
2)
insert into Book (isbn, authorID, name) select OLDBook.isbn, Author.authorID, OLDBook.name from OLDBook, OLDAuthor, Author INNER JOIN Author ON OLDAuthor.lastname = Author.lname AND OLDAuthor.firsname = Author.fname
...And this command ends to this message:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0203N A reference to column "AUTHOR.LNAME" is ambiguous. SQLSTATE=42702
Ok. I'm pretty sure that I can't make JOIN with non-unique fields. But how I can handle this?
I've also tried many other variations of these commands but without success.
Please help me if You can. :)
Nipa
View 1 Replies
View Related
Sep 9, 2003
In a simple join query, such as
SELECT *
FROM a, b
WHERE a.id = b.id
if table 'a' has 1 million and table 'b' has few thousands of records, will the order of the tables in from class will make any difference?
View 1 Replies
View Related
May 15, 2002
Hi all -
Heres the deal,
I have six tables that are loosly related,
four in one join, two in the other....
in the first join there is a column called commentID -
and is based on a join of the four tables, the results of the
query is based on a where clause
and this needs to be tied into the second set of joined
tables (they are joined on a commentID themselves)
I have both the queries and joins working -
The primary query (4 joined tables) needs to do a lookup on the second (2 joined tables) and add its selected value, into the final result set....
thanks a million for the help!!
take care
tony
View 1 Replies
View Related
Mar 15, 2007
I know how to join 2 tables, but I have a third I need to insert. For some reason, this doesn't work:
Code:
$rows = ff_select(
"select ".
"u.email as email, ".
"c.user_id as user_id, ".
"u.name as name, ".
"r.age as age ".
"from #__comprofiler as c ".
"left join #__users as u on c.user_id = u.id ".
"left join #__rnr_contest as r on c.user_id = r.userid ".
"where (r.age != chicken) and (r.age != nystrip) and (r.age != regrets) and (u.block = 0) and (c.cb_contactmethod LIKE '%Email%') and (u.usertype != 'Super Administrator') and (u.email != 'please@change.com') and (u.username != 'guest') and (u.username != 'piedmont') ".
"order by email"
anyone see why? It tells me that "chicken" is not a column which is weird because I don't think it's listed as a column in my query... is it?
View 3 Replies
View Related
Feb 27, 2008
I need some help and I can't figure this out.
I have a query that works :
Code:
SELECT table1.DealType, table1.bookings, table2.tours, table1.ResDate1
FROM (SELECT ResDate1, DealType, COUNT(ResID) AS bookings
FROM tblReservations res LEFT OUTER JOIN
tblDeals del ON res.DealID = del.DealID
WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24')
GROUP BY DealType, ResDate1) table1 LEFT OUTER JOIN
(SELECT ResDate1, DealType, COUNT(ResID) AS tours
FROM tblReservations res LEFT OUTER JOIN
tblDeals del ON res.DealID = del.DealID
WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') AND
(ResStatus = 'GOOD TOUR' OR
ResStatus = 'OVERGIFTED/GOOD TOUR')
GROUP BY DealType, ResDate1) table2 ON table1.ResDate1 = table2.ResDate1 AND table1.DealType = table2.DealType
ORDER BY table1.DealType
but now I'm trying to add a "table3" which is simply :
Code:
SELECT ResDate1, DealType, COUNT(ResID) AS nqs
FROM tblReservations res LEFT OUTER JOIN
tblDeals del ON res.DealID = del.DealID
WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') AND
(ResStatus = 'NON QUALIFIED')
GROUP BY DealType, ResDate1
but when I try to combine it its just not working:
Code:
SELECT table1.DealType, table1.bookings, table2.tours, table1.ResDate1, table3.nqs
FROM (SELECT ResDate1, DealType, COUNT(ResID) AS bookings
FROM tblReservations res LEFT OUTER JOIN
tblDeals del ON res.DealID = del.DealID
WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24')
GROUP BY DealType, ResDate1) table1 LEFT OUTER JOIN
(SELECT ResDate1, DealType, COUNT(ResID) AS nqs
FROM tblReservations res LEFT OUTER JOIN
tblDeals del ON res.DealID = del.DealID
WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') AND
ResStatus = 'NON QUALIFIED'
GROUP BY DealType, ResDate1) table3 LEFT OUTER JOIN ON table3.ResDate1 = table1.ResDate1
(SELECT ResDate1, DealType, COUNT(ResID) AS tours
FROM tblReservations res LEFT OUTER JOIN
tblDeals del ON res.DealID = del.DealID
WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') AND
(ResStatus = 'GOOD TOUR' OR
ResStatus = 'OVERGIFTED/GOOD TOUR')
GROUP BY DealType, ResDate1) table2 ON table1.ResDate1 = table2.ResDate1 AND table1.DealType = table2.DealType
ORDER BY table1.DealType
any help, pointers, kick in the right direction is appreciated
View 1 Replies
View Related
Jan 31, 2005
Hi,
I have three tables - clients, messages, orders. clientid is the primary key for clients and clientid is present in both messages and orders as foreign key. I want to get clientid, messagecount, and ordercount, that is, for each client I want to get number of messages posted by the client and number of orders placed by the client.
To get clientid, messagecount I do this:
select a.clientid, count(b.clientid) as messagecount
from clients a left outer join messages b on a.clientid = b.clientid
group by a.clientid
To get clientid, ordercount I do this:
select a.clientid, count(b.clientid) as ordercount
from clients a left outer join orders b on a.clientid = b.clientid
group by a.clientid
But I am not able to get all three clientid, messagecount, ordercount in a single query. This is what I tried. But it is not giving me the correct answer.
select a.clientid, count(b.clientid) as messagecount, count(c.clientid) as ordercount
from clients a
left outer join messages b on a.clientid = b.clientid
left outer join orders c on a.clientid = c.clientid
group by a.clientid
Can any one tell me how to do it right?
Thanks.
View 2 Replies
View Related
Oct 22, 2007
I have 3 tables on my db, Projects, ProljectAllocationLog and Users
Project consists of Projectid(PK), ProjectName
ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, Users consists of UserID (PK), Fullname
ProjectIDProjectName
1234ProjectOne
2346ProjectTwo
7892ProjectThree
ProjectAllocationLogIDProjectIDUserID
112341
223463
323461
412342
578922
612343
778921
878922
UserIDUserName
1Debbie Coates
2Fred Bloggs
3Jack Smith
I want to be able to view a list of showing the most current User for each project
eg
ProjectIDUserName
1234Jack Smith
2346DebbieCoates
7892Fred Bloggs
SELECT Max(ProjectAllocationLog.projectAllocationLogID) AS MaxOfprojectAllocationLogID, ProjectAllocationLog.ProjectID
INTO #TEMP
FROM ProjectAllocationLog
GROUP BY ProjectAllocationLog.ProjectID;
SELECT [#TEMP].ProjectID, ProjectAllocationLog.UserID, Users.UserName
INTO #TEMP2
FROM Users INNER JOIN ([#TEMP] INNER JOIN ProjectAllocationLog ON [#TEMP].MaxOfprojectAllocationLogID = ProjectAllocationLog.projectAllocationLogID) ON Users.Userid = ProjectAllocationLog.UserID;
Select * from #Temp2
I have created this, which shows me the results I want, but think it is really clumsy, Is there a way of doing this so that I don’t need to create the Temp tables?
View 1 Replies
View Related
