Combining 3 Tables To Get Required Results
Jul 2, 2014
I have a quick question for the SQL community about how to combine 3 tables to get the results needed...
The table names are :
setup_zipcode,
setup_category,
and listing
and inside setup_zipcode it has the columns:
zip_id , zip_code, zip_latitude and zip_longitude
and inside the setup_category it has the columns:
category_id, category_parent, category_path and category_name
and the final table for listing has the columns:
listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country
I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...
So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..
How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?
I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...
$cat is assigned in a query previously on the page...
SELECT *
FROM
setup_zipcode, setup_category, listing
WHERE
listing_category LIKE '%-$cat-%'
AND
listing_zip = zip_code
AND
category_id = '$cat'
ORDER BY listing_title ASC
When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..
I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..
If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...
View 1 Replies
ADVERTISEMENT
Oct 22, 2007
Hi all,
I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ?
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1
FROM dbo.Orders
WHERE (ShipVia = 1)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 1 Results
EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2
FROM dbo.Orders
WHERE (ShipVia = 2)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 2 results
EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19
Table 3 the desired result:
EmployeeID CountShipVia1 CountShipVia2
1
82 44
2
71 36
3
81 45
4
116 70
5
29 15
6
48 25
7
44 24
8
75 48
9
29 19
thanksrobby
View 5 Replies
View Related
May 4, 2005
I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))
When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?
View 1 Replies
View Related
Mar 6, 2006
Hey guys, here is my issue, i'm trying to combine multiple columns of data into one row. For example, I have a temp table:
create table #Custom_Address
( patient_idchar(10)null,
episode_idchar(3)null,
custom_address varchar(100) null
)
As you can tell, the 'custom_address' column is going to house the results of the combination.
I am trying to INSERT (combine) using this statement:
INSERT #Custom_Address
select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address
FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id
WHEREcc.coverage_plan_add1 > 0
OR cc.coverage_plan_add2 > 0
OR cc.coverage_plan_city > 0
OR cc.coverage_plan_st > 0
OR cc.coverage_plan_zip > 0
GROUP BY cm.patient_id, cm.episode_id
I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.
Then I do my update:
UPDATE#ClaimMaster
SET #ClaimMaster.custom_address = ca.custom_address
FROM #ClaimMaster
JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id
However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.
If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?
As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.
View 5 Replies
View Related
Oct 18, 2013
I have a query (SELECT * FROM Pricing) that produces the following results
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23
I want to convert this to the following :
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23
We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23
View 1 Replies
View Related
Jun 3, 2014
i have these 2 queries with the included results sets...What commands could I use to take the TotalBlueCircle Column from the 2nd Results Set and have it included next to the TotalRugby column in the 1st results set??Do i need to do a UNION or use Sub Queries or something else?
View 5 Replies
View Related
Jun 19, 2007
Hello there
I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms. I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking. Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query. The table structures are as follow:
bookingrequestion - bookingrequestid, startdate, enddate
roomsbooked-id, bookingrequestid, roomname
i'm basically trying to use the following query
select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms
i'd like the results of the subquery to return the room names as A,B,C. I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.
thanks
View 1 Replies
View Related
Aug 7, 2007
Need a little help here.
I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.
I need the results as one table.
Here is my code.
___________________________________
SET NOCOUNT ON
DECLARE @IdsString VARCHAR(255), @Id int
SELECT @IdsString = '918|808|1214|89|995|300|526|1207'
DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s
OPEN GetData
FETCH NEXT FROM GetData
INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @Id
FETCH NEXT FROM GetData
INTO @Id
END
CLOSE GetData
DEALLOCATE GetData
_____________________________________
Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?
Any help would be much appreciated.
NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:
Regards
Shaun McGuile
View 14 Replies
View Related
Mar 19, 2012
My application saves customer email addresses in two different fields in ym table
How do i combine two fields from the same table in a select statement?
I've tried the following:
Select EmailAddy + ', ' + FriendsEmail AS Expr1
FROM dbo.Contacts
But all I get are the results from one field.
View 4 Replies
View Related
Jul 26, 2004
I know this has been addressed before but I can't find it...
I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...
Anyone able to help?
View 14 Replies
View Related
Mar 5, 2012
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
View 6 Replies
View Related
Mar 5, 2008
I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.
The following query returns, for each store, the total number of records in the ProductInventory table:
SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
GROUP BY Stores.Name
The following query returns, for each store, the total number of records in the Customers table:
SELECT Stores.Name,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name
I combined the two queries:
SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
LEFT JOIN Customers ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name
When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected
What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
View 7 Replies
View Related
Jul 17, 2013
I have a table of attributes set up as follows:
ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
View 5 Replies
View Related
May 9, 2004
Hello everyone,
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer
AS
DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest
(
Zip,
UpsRegion,
UspsRegion
)
SELECT
CMRC_ZipRegionUps.Zip,
CMRC_ZipRegionUps.UpsRegion,
CMRC_ZipRegionUsps.UspsRegion
FROM
CMRC_ZipRegion,
CMRC_ZipRegionUsps
GO
Is there any way to do this? Or do I have to manually enter all the entries?
Any help would be great. Thank you.
-Alec
View 1 Replies
View Related
May 15, 2007
If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
View 2 Replies
View Related
Feb 2, 2006
Hello,
I have two tables that I would like to combine but with some rules.
Table 1 has two columns with Actual Time and Actual People
Table 2 has two columns also with Planned Time and Planned People
I would like to combine these two tables if the actual time is not more than 30 minutes from a planned time
If I use one of the planned times, i would not want to show it again, even if there is another actual time that is not more than 30 minutes of it.
For ex.
Planned Time has 20:00,23:00
Planned People has 2,5
Actual Time has 19:00,19:30,19:45,21:15
Actual People has 5,2,5,3
Output should be like
Planned_Time,Actual_Time,Planned_People,Actual_People
,19:00,,5
20:00,19:30,2,2
,19:45,,5
,21:15,,3
23:00,,5,
I can do this in asp but if this can be done in sql it would be better.
Any ideas appreciated.
Thanks.
View 4 Replies
View Related
Feb 5, 2007
hi
this is my query:
tabel1:
select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1'
if i run this query i will get output like this:
userid user_name password role_code expiry_date created_date
1 karthik karthik AD 01/17/20082007-01-24
active
0:00:00.000 1
i have another table which has the following records
tabel 2:
select * from code master
codename codedescription
AD admin
sp supervisor
so in the first query i need the output as like tithe following:
userid user_name password role_code expiry_date created_date
1 karthik karthik admin 01/17/20082007-01-24
active
0:00:00.000 1
so how to combine this two tables inorder to get the codedescription as admin for the roll_code=ad
so please give me query for this
View 18 Replies
View Related
Jun 9, 2007
is it possible to combine two tables(not related with each other) as onde like we put them together with our hands physically.
this is what i want;here's the two tables to be combined:
table-a ______________table-b
15 _ a ____________ ny _____ arena
25 _ d ____________ fg_____ metus
35 _ f ____________
45 _ f ____________
these two tables above will become table-c like below;
table-c
15__arena
25__metus
35__null
45__null
but a warning , no relation between the tables and, row counts will not be equal anytime one of them may have more rows then the other ,ihe tried many join methods but gave me allways the lots of results more than i want ,please anyone can help? is it possible to put two tables physically like we put them together with our hands ?
View 14 Replies
View Related
Dec 20, 2007
Hi all,
I am working from a database containing sets of questions and answers relating to maternity episodes. The answer tables are broken up into two tables, freetext answers and standard drop-down answers. The questions relating to these answers are held in the same table, MAT_QUESTIONS.
When I want to view the freetext answers and questions for a certain episode (based on incidentid), the following code works..
SELECT P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, FA.ANSWERTEXT
FROM MAT_FREEANSWERS FA, MAT_QUESTIONS QS,
MAT_INCIDENTS I, MAT_DELIVERY D,
PASMAIN P
WHERE FA.QUESTIONID=QS.ID
AND FA.INCIDENTID=I.INCIDENTID
AND I.INCIDENTID=D.INCIDENTID
AND D.MOTHER_PAS_NO=P.PAS_NO
AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59'
AND QS.QUESTIONNAIREID=2
AND FA.INCIDENTID=4501
and if I want to view the answers to the standard drop down questions, I use the following code...
select P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, A.ANSWER
from MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P,
MAT_QUESTIONNAIRE Q, MAT_QUESTIONNAIREINCIDENTS QI, MAT_QUESTIONS QS,
MAT_ANSWERS A, MAT_INCIDENTANSWERS IA
WHERE I.INCIDENTID=D.INCIDENTID
AND I.INCIDENTID=QI.INCIDENTID
AND D.INCIDENTID=IA.INCIDENTID
AND D.MOTHER_PAS_NO=P.PAS_NO
AND Q.ID=QI.QUESTIONNAIREID
AND QI.QUESTIONNAIREID=QS.QUESTIONNAIREID
AND QS.ID=A.QUESTIONID
AND A.ID=IA.ANSWERID
AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59'
AND Q.ID=2
AND I.INCIDENTID=4501
However, I would like to join the two together, as I would like to see a list of all questions and answers posed to a particular patient. But, as there are so many tables in common, I'm having difficulty doing this.
Would appreciate any help!
Fiona
View 10 Replies
View Related
Mar 11, 2008
I have a database, and I need data from 3 tables.
From the first table I just need the primary key: issueID.
I can make a left outer join with my second table so I can have this result.
select A.issueID, B.projectID
from issuerequest as A left outer join projects as B on (A.issueID=B.referenceID)
group by A.issueID
gives:
100 | null
101 | P003
102 | P002
103 | null
...
When the value in the second column is null, I need to check the value
in the 3th table.
The join between the first and the thirth is:
select C.issueID, min(D.project) as Project
from issuerequest as C left outer join ProductCustomer
on (C.customer = D.customerID) and
(C.product=D.produktID)
where actuellproduct='1'
group by C.issueID
this gives a result as:
100 | P002
100 | P003
101 | P004
102 | P002
103 | null
...
What I actually want is just 2 columns with
in the first IssueID and in the second Project
and no duplicate data. Everything (except null) is more
important then the same value in column 3.
100 | P002
101 | P003
102 | P002
103 | null
I've tried with except, but can't get all duplicate
data out.
View 3 Replies
View Related
Jul 12, 2004
Hi...
I have two tables (teachers, students) each table has 3 fields (FirstName, LastName, EMail). I'm trying to combine the data from both tables into one so I have a "master list" to send e-mails to all teachers and students via an ASP script/CDO.
I've been reading up on Joins but I can't figure out the SQL to combine data from both tables. Any ideas?
Thanks,
Steven Lee
View 2 Replies
View Related
Oct 18, 2004
Hello, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other.
So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written? Sorry if this is a little confusing but im having a hard time visualising how to do this.
View 8 Replies
View Related
Jul 2, 2004
I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
Is this even possible?
View 4 Replies
View Related
Jul 1, 2014
I am trying to do a really weird combination of an Append/Union query..I'm doing most of my commands through macros and queries.how to do a normal Append/Union/Create queries but I am trying to do something kind of weird. If table X has rows A, B, C, and D, and table Y has a column with rows 1, 2, 3, and 4... I need the values in table Y to be assigned to each individual row in table X. Is there a way to create a new table where the columns merge such that values can be assigned like:
X Y
A 1
B 1
C 1
D 1
A 2
B 2
C 2
D 2
A 3
B 3
View 3 Replies
View Related
Apr 29, 2008
Hello all - I'm relatively new to SQL and has been struggling with the below query. Any help is greatly appreciated. Thanks.
I have the following 2 tables:
I'm trying to get the below output:
Here's the SQL I've been using:
SELECT
a.PERSON_ID,
sum(a.AMOUNT),
CASE
WHEN b.JOB_CODE = 10 then "Level 2 Seller"
WHEN b.JOB_CODE = 15 then "Level 3 Seller"
ELSE
'Level 1 Seller'
END as "Job_Code"
FROM
tbl_SALES a,
tbl_JOBFUNCTION b
WHERE
a.PERSON_ID = b.PERSON_ID
GROUP BY
a.PERSON_ID,
b.JOB_CODE
Here's what my results are with the query I've been using:
Each person could have many job codes, but when they have 10 then they're consider "level 2".
- If they have 15 then they're "level 3".
- If they don't have either 10 or 15 then they're "level 1".
- If they have both 10 and 15 they're still "level 3".
Please help.
Thanks,
Jim
View 3 Replies
View Related
Jun 3, 2008
Hey all
I'm trying to get data from two tables that have the same fields. One is a log table and the other is the actual table. I have the following:
SELECT tbl_LCData.* FROM tbl_LogOfLCDataTable, tbl_LCData
WHERE (((SELECT DATENAME(MONTH, tbl_LCData.PrintDate)) = (SELECT DATENAME(Month,getdate())) AND (SELECT DATENAME(MONTH, tbl_LogOfLCDataTable.PrintDate)) = (SELECT DATENAME(Month,getdate()))))
I am trying to display records that were printed this month. I only get data from tbl_LCData. It doesn't display data from the log table. For example:
Contents of tbl_LCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
18, B, Whatever, 11/04/2007
Contents of tbl_LogOfLCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 01/06/2008
So, I want the query to display:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
17, A, Sample, 01/06/2008
The above shows records printed in the month of June!
Is there a join I need to use??
Many thanks,
Rupa
View 18 Replies
View Related
Jan 10, 2014
I need to combine data from two tables located in the same database.
The column GID in Table 1 and RealEstateID in Table 2 has a common id
I want to specify a date interval (AcquistitionDate in Table 2) eg 20131001 to 20131030 and get Name from table 1, and combine these to a result table.
Something like this (?) : [URL] .....
View 4 Replies
View Related
Aug 7, 2014
I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.
I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B
SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID
I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.
View 3 Replies
View Related
Jul 20, 2005
Hi.I'm currently working on a project which involves the creation of aweb page which reports selected data to customers from two back-endsystems. I use ASP script language on IIS, the server is MS SQL 2000.Now I'm struggling with combining two tables from the differentdatabases. I'm sure it's simple enough, but I'm a little short on theSQL expertise.I've got two databases, db1 and db2, and then two tables, db1.t1 anddb2.t2. I need to combine these two tables (both tables have amatching key field) to make a list of all items from db1.t1, and thosewho correspond from db2.t2.I can list all items from db1.t1, but I can't seem to get the db2.t2joined in.Can anybody help me with the syntax for this, please ? Help !Answers, hints & tips greatly appreciated.Thanks in advance !Kenneth
View 2 Replies
View Related
Sep 20, 2007
Hi all,
How can I combine the contents of the two tables below? The combination result of these tables is provided below. Thanks
Table A
Client Weight Purchase
Tom 10 2
Bill 4 2
John 3 2
Table B
Client Weight Purchase
Jim 2 5
Lee 4 3
Bob 6 7
Combination table (result)
Client Weight Purchase
Tom 10 2
Bill 4 2
John 3 2
Jim 2 5
Lee 4 3
Bob 6 7
View 3 Replies
View Related
May 4, 2007
Hii all.
quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.
The problem:
When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...
Is there a way to join these tables without leavings gaps ?
any help is appreciated.
Neil
View 7 Replies
View Related
Nov 24, 2015
I have database with two different tables BaseData and QualityDetails as given below with ID as the Primary key
BaseData.
Sent_to_Client Name ID
1/2/2015 ABC1 1000000
2/24/2015 ABC2 1000001
5/24/2015 ABC3 1000002
3/24/2015 ABC4 1000003
QualityDetails
ID Error
1000000 1
1000001 2
1000002 0
1000003 3
I want to write an SQL Query to get the data by combining both the tables by dates from 2/1/2015 to 5/30/2015 in BaseData table. My table after executing the query should be
ID Name Error
1000001 ABC2 2
1000002 ABC3 0
1000003 ABC4 3
View 7 Replies
View Related
Jul 17, 2006
I have two tables - T_Assets and T_Inspections.
I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL:
SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection)
FROM T_Assets
INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID
GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
Can anyone please tell me a way around this.
Jagdip
View 8 Replies
View Related
