Joining Two Queries

Jun 2, 2008

 Hello I have two queries that I would like help joining:
 Query #1

SELECT     UserName, (SELECT  COUNT(*) AS Expr1  FROM Jokester WHERE (InvitedBy = aspnet_Users.UserName)) AS invs
FROM         aspnet_Users
 Query #2

SELECT     UserName, SUM(Hits) AS Hits
FROM         (SELECT     UserName,
                                                  (SELECT     COUNT(*) AS Cnt1
                                                    FROM          (SELECT     COUNT(*) AS Cnt2
                                                                            FROM          Hits
                                                                            WHERE      (JokeId = j.JokeId)
                                                                            GROUP BY UserId) AS T1) AS Hits,
                                                  (SELECT     AVG(CAST(Rating AS numeric(12, 2))) AS Rating1
                                                    FROM          Ratings
                                                    WHERE      (JokeId = j.JokeId)) AS Rtng
                       FROM          Jokes AS j) AS t2
WHERE     (Hits >= 1) AND (Rtng >= 3)
GROUP BY UserName
 
They both return correct values, I just want to join them on UserName
 
Thank you,
 
Louis
 



 

View 8 Replies


ADVERTISEMENT

? Joining Two Queries

Feb 27, 2005

Right now I have two Queries that I've created:
What I'm wanting to do is graft everything from Query1 onto Query2 but only where Query1.UserID = Query2.UserID . Any suggestions on how I would go about doing this?

--Query 1
(
SELECT
PC1.ID,
PC1.UserID,
PC1.ModuleID,
PC1.ModifiedDate
FROM
Projex2_0_0_Cores PC1
WHERE
PC1.ModuleID = 369
)

--Query 2
(
SELECT
PC2.UserID,
MAX(PC2.CreatedDate) as CreatedDate
FROM
Projex2_0_0_Cores PC2
WHERE
PC2.ModuleID = 369
GROUP BY
PC2.UserID,
PC2.ModuleID
)

View 4 Replies View Related

Joining Two Queries

Apr 29, 2008

Hi Can anyone help me join these two quries together?
SELECT SUM(SchemeAccount.TotalVar) AS TotalVar, Scheme.Code
FROM SchemeAccount LEFT OUTER JOIN
Scheme ON SchemeAccount.SchemeID = Scheme.SchemeID
GROUP BY Scheme.Code

and

SELECT SUM(Variation.VarAmount) AS VarAmount, Scheme.Code
FROM Variation RIGHT OUTER JOIN
Scheme ON Variation.SchemeID = Scheme.SchemeID
GROUP BY Variation.SchemeID, Scheme.Code

View 1 Replies View Related

Joining Queries For An Export?

Jan 9, 2004

I have three queries - but in the end I want one list to export. Is their anyway I can join the queries by B.ADMINISTRATOR so that I could export one list with the Administrator and the three counts separately. I don't want to go through the process of putting them into tables and then joining them later.

SELECT B.ADMINISTRATOR, COUNT(*) FROM SPONSOR_PRIMARY AS A, SPONSOR_SECONDARY AS B, SPONSOR_TERTIARY AS C, SALES.DBO.COMPANY AS S
WHERE (A.SPONSORID = B.SPONSORID AND A.SPONSORID = C.SPONSORID AND S.XTELELINK = B.ADMINID AND S.PROVIDER_TYPE = 'TPA Only') GROUP BY B.ADMINISTRATOR

SELECT B.ADMINISTRATOR, COUNT(*) FROM SPONSOR_PRIMARY AS A, SPONSOR_SECONDARY AS B, SPONSOR_TERTIARY AS C, SALES.DBO.COMPANY AS S
WHERE (A.SPONSORID = B.SPONSORID AND A.SPONSORID = C.SPONSORID AND S.XTELELINK = B.ADMINID AND S.PROVIDER_TYPE = 'TPA Only' AND C.SPONSOR_SEARCH = 1) GROUP BY B.ADMINISTRATOR

SELECT B.ADMINISTRATOR, COUNT(*) FROM SPONSOR_PRIMARY AS A, SPONSOR_SECONDARY AS B, SPONSOR_TERTIARY AS C, SALES.DBO.COMPANY AS S
WHERE (A.SPONSORID = B.SPONSORID AND A.SPONSORID = C.SPONSORID AND S.XTELELINK = B.ADMINID AND S.PROVIDER_TYPE = 'TPA Only' AND C.SPONSOR_CHANGE = 1) GROUP BY B.ADMINISTRATOR

View 3 Replies View Related

'joining' Results Of 2 Queries

Jul 20, 2005

Does anyone know how I can 'join' the results ofone SQL query to the bottom of another?Eg. I have two queries:1. SELECT Name, Surname FROM People WHERE Surname = SmithNAME SURNAMEAdam SmithJohn SmithMichael SmithSteve Smith2. SELECT Name, Surname FROM People WHERE Surname = JonesNAME SURNAMEBob JonesLarry JonesTom JonesWhat I want to produce is:NAME SURNAMEAdam SmithJohn SmithMichael SmithSteve SmithBob JonesLarry JonesTom JonesHowever, if I use UNION like this:SELECT Name, Surname FROM People WHERE Surname = SmithUNIONSELECT Name, Surname FROM People WHERE Surname = Jonesit mixes up all the results:NAME SURNAMEAdam SmithBob JonesJohn SmithLarry JonesMichael SmithSteve SmithTom Jones(I guess it's sorting by the first field, NAME).Is there a way to stop it sorting the results, so that itjust tacks the second query results to the bottom of thefirst query results?(I realise I could use "ORDER BY Surname" to get the same resultin this simple example, but for the more complicated queriesI want to use it won't work).Thanks for any help,Matt.

View 3 Replies View Related

Transact SQL :: Joining / Combining Two CTE Queries?

Apr 29, 2015

I have these two CTE queries, the first one is the existing one which runs fine and returns what we need, the second is a new CTE query which result I need to join in to the existing CTE, now that would be two CTE's can that be done at all?The second query is working on the same table as the first one so there are overlaps in names, and they are the same, only columns I need to "join" is the "seconds" and "AlarmSessions".

;with AlarmTree as (
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,

[code]....

View 4 Replies View Related

Transact SQL :: Joining Two Queries (one Regular And One Grouped)

Apr 29, 2015

I have these two queries I would like to join, however the later is a grouped query how can I join it with the first query? Has to be joined on EventId. The second query is a total table scan.

SELECT AH.EventID,
AH.TechnicalAddress, AH.AlarmAlias, AH.AlarmPath as [OrgAlarmPath], AH.AlarmCounter as AlarmCount, AH.EventDateTime as EventTime,
AH.[Priority], AH.AlarmMessage, AH.EventText, AH.CallListName, AH.AlarmReadDate as EndTime,
AH.alh_EventEndedUserRemark as [EndRemark] --, SUM(seconds) here, and AlarmSessions here
FROM AlarmHistory AH

[Code] ...

2)
WHERE ia.EventTypeId = 0
group by ia.EventId
order by EventId desc

View 4 Replies View Related

SQL 2012 :: Queries Based On ER Diagram And Joining Tables

Feb 22, 2015

I need to create a few select queries based on an er diagram.

These are my Create Table statements and import statements:

Create Table Agent (Aid integer primary key, Pid integer, aName text);
Create Table Product (Pid integer primary key, pName text);
Create Table Supplier (Sid integer primary key, sName text);
Create table Supplies (Sid integer, Pid integer, price decimal(8,2));

.import agent.txt Agent
.import product.txt Product
.import supplier.txt Supplier
.import supplies.txt Supplies

I think I got all my create table statements are correct.

I need to Find the number of agents for each supplier that has at least one agent. The result should be tuples of the form (sid, sName, number of agents)

-Select Sid, sName, count(Aid) from Agent A join Supplier S on (S.Sid = A.Sid) group by S.Sid, S.sName, Aid;
But it gives me this error: no such column: A.Sid

Im thinking I might have a problem with my create table statement and/or primary key statements?

View 9 Replies View Related

Integration Services :: Joining Two Select Queries Results In One Row?

Jun 12, 2015

I want to get output of below query in single row.

Select 'Name'
Select 'Surname'

Expected output is Name,Surname

View 6 Replies View Related

T-SQL (SS2K8) :: Joining Results Of Two Queries Without Creating Temporary Tables?

Nov 16, 2014

In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?

/*This T-SQL gets the services for the EPN download from WITS*/

-- Select services entered in the last 20 days along with the MPI number and program code.

SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id

-- Select group notes

SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,

[code]....

-- Form an outer join selecting all services with any group notes attached to them.

select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;

-- Drop temporary tables

DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;

View 9 Replies View Related

Parameterized Queries Running Slower Than Non-parameterized Queries

Jul 20, 2005

HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)

View 1 Replies View Related

Help In Joining

Jan 3, 2008

I want to make some joining in my table but I dont know what kind of joining is appropriate. Here is my tables sample:table1 -> tableid, useridtable2 -> userid, firstname, lastnametable3 -> userid, firstname, lastname Now, I want to to get data from table1 with the data from table2 and table3 where userid of table1 = table2, table3. 

View 2 Replies View Related

Joining From Two Different DB

Apr 17, 2006

May I know how to issue JOIN statement to join two ID from different DB ?
example:
dbA - tableA - a.ID
dbB- tableB - b.ID
Thanks.

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

Joining A Table Twice

Jan 26, 2015

I have tables Companies, CompaniesDetails (the company branches), Addresses and Companies_Addresses.

The addresses table contain street and city while the Companies_Addresses has the keys for both companies and branches ,i.e., they are linked to Companies and CompaniesDetails via CompanyID and CompanyDetailID and to Addresses via addressID.

Companies_Addresses
id (PK)
companyID (FK)
companyDetailID (FK)
addressID (FK)

I am able to get the branch address at the moment with this code but I would like to get the company address as well using a single select statement.

Code:
SELECT DISTINCTAddresses.city as branchCity, Addresses.street as branchStreet
FROMCompanies
LEFT JOINCompaniesDetails AS cd ON companies.companyID = cd.companyID

LEFT JOINCompanies_Addresses AS c ON c.companyDetailID = cd.companyDetailID
LEFT JOINAddresses ON c.addressID = Addresses.addressID

WHERE Companies.name LIKE 'abc'
ANDCompanies.status_indicator like 'Current'

View 8 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 Date

Jun 11, 2008

Hi,
I need to select date for my project.
I used the following

select datepart(yy,getdate())
select datepart(mm,getdate())
select datepart(dd,getdate()) -7

and i get

2008
6
4

How do I get the following result?
20080604
without any "." or "-"?
Thanks

View 4 Replies View Related

Joining Columns

Jun 11, 2008

I have 3 columns with text (dates) 06 03 08. I want to join those columns into one column.

Help, cast or concat does not work.

Lisa Jefferson

View 18 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 Of Two Statements

Jun 25, 2014

I am having difficulty with joining these two statements and I just can't seem to get a way. Here is the query:

select Name,Surname,position
from dbo.Employee, dbo.Stafflink
where dbo.Employee.ID = dbo.Stafflink.empID
group by Name,Surname,Position

select Employee.Name as ManagerName, Employee.Position as ManagerPosition
FROM dbo.Employee, dbo.Stafflink
WHERE dbo.Employee.ID = dbo.Stafflink.ManID

How can I join both of these statements?

View 6 Replies View Related

Duplicate Value When Joining

Aug 27, 2014

I am getting output two times but my table has single data then how to remove duplicate my SQL query is:

select nonregister.email as firstname,nonregister.email ,appliedjobstatuswithresumes.description,appliedjobstatuswithresumes.applieddate from nonregister , appliedjobstatuswithresumes where appliedjobstatuswithresumes.nonregid IN (select nonregid from nonregister where nonregid IN (select nonregid from [appliedjobstatuswithresumes]

[Code] ....

View 1 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 A Table With Itself

Dec 6, 2005

If a table gets joind with itself and then joined again with itself, is it possible to perform one kind of outer join from the third table to the second tabe and having the range of records in the second table limited to the joins between the first and the second table?





Join on RegionID Join on RegionID and City
Show records not qualifying for the joint
but limited to the scope of of records
established by the first joint
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City New York | | City New York|
|________________| |_______________|
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City New York | | City New York |
|________________| |_______________|
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City Buffalo | | City New York |
|________________| |_______________|

Plotin

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







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