Select From Multiple Tables No Common Field
Oct 17, 2005
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
View 2 Replies
ADVERTISEMENT
Jan 4, 2008
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
View 6 Replies
View Related
Jan 29, 2004
Hello-
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
How do i do this?
View 6 Replies
View Related
Dec 1, 2005
I have a database with 10 tables
I have a field named CustID .... I want to find out how many tables have this field CustID
Now one way was check up the design of every table & see whether its there or not (the field)
is there any query through which we can get this info Please help
View 4 Replies
View Related
Feb 10, 2002
This is feeling very hard for me, but is surely very easy for many of you.
I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.
PLEASE HELP ME!!!
Pending deadline doom :(
Thanks,
kaskenasy@publishingconcepts.com
View 1 Replies
View Related
Jan 27, 2014
I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.
My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
This is my current query:
SELECT AC.IdActividad, CU.idcuenta
FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad
LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta
As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.
View 3 Replies
View Related
Nov 18, 2007
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
View 2 Replies
View Related
Dec 4, 2007
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
View 7 Replies
View Related
Aug 26, 2005
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
View 4 Replies
View Related
Nov 6, 2007
Hi all,
I want to retrieve the datas from the table with condition if DetailID is not null and DetailID is not guid.empty then it return only the DetailID = @DetailID
can any one help on this.
Thanks,
Sajith
View 4 Replies
View Related
Jul 23, 2005
I have a tables called subsid that I need to change the datatype fromtext to int.I think I got them all but is there a query I can run that will checkall fields call subsid accross all tables that are of type text.
View 1 Replies
View Related
Apr 9, 2007
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies
View Related
Sep 9, 2015
Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies
View Related
Sep 30, 2015
I have an XML file loaded into a table with XML type column. The XML format is such:
<CsResponse>
<Reports>
<Report>
<Id>186192</Id>
<DateCreated>9/29/2015 1:19:56 PM</DateCreated>
<DateUpdated>9/29/2015 2:19:10 PM</DateUpdated>
<RequestType>Roadway Sign (Damaged/Missing)</RequestType>
[Code] ....
I am trying to create 2 tables, one (Table 1) having:
Id
DateCreated
DateUpdated
RequestType
RequestTypeId
StatusType
StatusTypeIsClosed
The other (Table 2) with:
Id
DateCreated
Key
Value
Ultimately, these 2 table will be joined on Id. Here is the SQL, Table 1 creates properly, and Table 2 creates, but right now 'Id' comes in as NULL. How do I add the Id field from the upper node into Table 2 (excuse my terminology if it is incorrect) ?
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM pubanalysis.dbo.CS_XMLReportData
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
--TABLE 1
INSERT INTO pubanalysis.dbo.CS_RequestReports
[Code] ....
View 5 Replies
View Related
Apr 22, 2006
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:
Anna Smith
John Doe
etc. I did not see IDs listed for any of the names in the table when I looked.
There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.
Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:
strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"
This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.
Is there a way to modify this statement to pull only the records that I want?
Thanks for any help you can give.
AJ
View 3 Replies
View Related
May 31, 2006
I have developed an ASP.NET form with 12 different fields that will allow end users the cability to query 3 tables that are relational to one another.
I was curious what is the best way to perform this in a Stored Procedure?
Can I use a UNION -- Not sure if this is the best choice
or account for evey kind of WHERE clause based off IF statements on the data that is passed through the parameters?
View 3 Replies
View Related
Jul 9, 2007
Hi there,I want to select records from 3 tables. In SQL Server 2005, I'm using of "For XML" like this:Select *, (Select * From dbo.PageModules Where (PageId = 1) For Xml Auto) As Modules, (Select * From dbo.PageRoles Where (PageId = 1) For Xml Auto) As Roles From dbo.PagesThat works fine in SQL 2005 but not in SQL 2000, Because SQL 2000 does not support nested "FOR XML".Is there any way for selecting records from multiple tables by a query?Thanks in advance
View 4 Replies
View Related
Feb 24, 2012
I have three tables.
Table USERS Contains columns User_id and UserName
Table DOMAIN Contains columns Domain_id and DomainName
Table USER_DOMAIN Contains columns User_id, Domain_id, count, day, month, year
I am looking to run a report that pulls its information from USER_DOMAIN but instead of displaying User_id, Domain_id, it returns the UserName and DomainName associated.
The query to pull the info i need is very simple, where i am having problems is linking the user_id to the UserName and the Domain_id to the DomainName.
View 2 Replies
View Related
Jul 20, 2007
Hi all! I just registred (very nice site) and have problem with getting some data from multiple tables, I would like to get result in one result set and best would be in one sql query.
I have DB for miniMessenger proggy, what i try to do is retrieve list of contacts.
Table containing user account information.
CREATE TABLE `account` (
`id_account` mediumint(8) unsigned NOT NULL auto_increment,
`userdata_id` mediumint(8) unsigned NOT NULL default '0',
`login` varchar(15) NOT NULL default '',
`pwd` varchar(15) NOT NULL default '',
`messenger_id` mediumint(8) unsigned NOT NULL default '0',
`logged` tinyint(1) NOT NULL default '0',
`ost_login` varchar(11) default NULL,
PRIMARY KEY (`id_account`),
UNIQUE KEY `messenger_UN` (`messenger_id`),
UNIQUE KEY `userdata_UN` (`userdata_id`)
)
INSERT INTO `account` VALUES (1, 1, 'User', 'fatimah', 4118394, 0, NULL);
INSERT INTO `account` VALUES (2, 2, 'Admin', 'haslo', 3333333, 0, NULL);
Contact list, first field is contact number (like 4356789 - MESSENGER id) next to this number is its contact number, auth - if contact was authorised, ban selfexplained :) I just take every row with number 4356789 and get contact numbers next to it.
CREATE TABLE `contacts` (
`contact_id` mediumint(8) unsigned NOT NULL default '0',
`contacts` mediumint(8) unsigned NOT NULL default '0',
`auth` tinyint(1) unsigned NOT NULL default '0',
`ban` tinyint(1) unsigned NOT NULL default '0',
KEY `Contacts ID` (`contact_id`)
)
INSERT INTO `contacts` VALUES (4118394, 3333333, 1, 0);
INSERT INTO `contacts` VALUES (4118394, 1234567, 0, 1);
Its table for messenger data, ID, status of contact (offline,online,ect), description, chat archiwum,
CREATE TABLE `messenger` (
`id_messenger` mediumint(8) unsigned NOT NULL default '0',
`status_id` tinyint(3) unsigned NOT NULL default '0',
`description` varchar(255) NOT NULL default '',
`archiwum` mediumtext NOT NULL,
PRIMARY KEY (`id_messenger`)
)
INSERT INTO `messenger` VALUES (1234567, 0, '', '');
INSERT INTO `messenger` VALUES (3333333, 1, '', '');
INSERT INTO `messenger` VALUES (4118394, 2, '', '');
Status is enumeration of status states(off,on,brb ect).
CREATE TABLE `status` (
`id_status` tinyint(3) unsigned NOT NULL default '0',
`stat` varchar(15) default NULL,
PRIMARY KEY (`id_status`)
)
INSERT INTO `status` VALUES (0, 'offline');
INSERT INTO `status` VALUES (1, 'Online');
INSERT INTO `status` VALUES (2, 'brb');
What i want to get is contact list + additional info of specific user by its messenger id. Like:
id_messenger,contacts,auth,ban,stat
which is userID, contact ID, authorisation, ban, status
My query looks like this:
SELECT id_messenger,contacts,auth,ban,status_id
FROM account,messenger,contacts
WHERE account.login = 'User'
AND messenger.id_messenger = account.messenger_id
AND contacts.contact_id = messenger.id_messenger
And it shows in stat only status of user of which i retrieve contact list. Please help me, im tired of working on this, im sure it is trivial :(
thx in advance!
View 6 Replies
View Related
Nov 8, 2007
Can someone explain to me why this would be considered "bad"? One thing that pops in my mind is that I really don't need all the columns from all these tables, only specific columns. Would this cause a performance issue when used in a stored proc for a transactional app?
SELECT *
FROM CASE_XREF CX, CASE_RENEWAL_XREF CRX, RENEWAL_BATCH RB, PROPOSAL P
WHERERB.MKT_SEG = @MKT_SEG
AND RB.CORP_ENT_CD = 'oh'
AND RB.RENEWAL_DT = '01/01/2008'
AND CRX.TRIGGER_TYPE_CD = 'P'
AND RB.BATCH_ID = CRX.BATCH_ID
AND CRX.CASE_ID = CX.CASE_ID
AND CRX.REN_PROSPECT_ID = P.PROSPECT_ID
AND CRX.REN_PROP_NUM = P.PROP_NUM
AND P.PROP_STATUS <> 'C'
AND CX.ACCT_NBR = 123152
View 5 Replies
View Related
Dec 18, 2007
I have four tables. now i need to select the rows from all the four tables.
TABLE1: -Job
jobno
mtid
prid
mtpath
prpath
TABLE2: - livestaff this id will be store in Job
staffid
staffname
teamid
active
TABLE3: -masterstaff
mstaffid
staffname
teamid
active
TABLE4: -staffrel
masterstaffid
livestaffid
Now i need to select * from job and staffname from masterstaff and teamid from livestaff.
Please help
____________
Praba
View 2 Replies
View Related
Aug 8, 2007
Basically I have 5 tables. These are...
1/ RCPCrossRef
2/ RCPPositionData
3/ RGCrossRef
4/ RGData
5/ RComments
------------------------------------------------------
RCPCrossRef and RCPPositionData are related by these keys:
RCPPositionData.UniquePositionID = RCPCrossRef.CPPositionID
RGCrossRef and RGData are related by these keys:
ON RGData.PositionID = RGCrossRef.GPositionID
-----------------------------------------------------------
RCPCrossRef and RGCrossRef are related by these keys:
ON RCPCrossRef.GMatchID = RGCrossRef.GMatchID
But RCPCrossRef may also contain a NULL value for this key meaning no relationship exists for that row.
----------------------------------------------------------------
Finally RComments is related to RCPCrossRef and RGCrossRef by these keys...
ON RComments.GPositionID = RGCrossRef.GPositionID
ON RComments.CPPositionID = RCPCrossRef.CPPositionID
But again, one of these columns in the RComments table could contain a NULL value meaning no relationship exists for that row of data.
---------------------------------------------------------------
So my aim is to display ALL DATA for each of these tables.
Tried the below but doesn't return any rows...
Code SnippetSELECT gd.Quantity, c.Comments,
gc.GPositionID, cc.CPPositionID, cd.PositionDate
FROM ReconComments AS c
INNER JOIN
RGCrossRef AS gc
INNER JOIN
RGData AS gd
ON gc.GPositionID = gd.PositionID
ON c.GPositionID = gc.GPositionID
INNER JOIN
RCPData AS cd
INNER JOIN
RCPCrossRef AS cc
ON cd.UniquePositionID = cc.CPPositionID
ON c.CPPositionID = cc.CPPositionID
WHERE gc.ForcedMatch = 'yes' AND cc.ForcedMatch = 'yes'
Thanks.
View 6 Replies
View Related
Sep 13, 2006
i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.
View 1 Replies
View Related
Apr 7, 2008
Hi,
I have a 'charges' table that records charges for an invoice. There are several different types of charges, each with its own unique set of additional data fields that need to be recorded.
I maintain separate tables for each charge type and these tables participate in an "ISA" relationship with the main charges table.
Here is a simplified version of my schema. Hourly charges are one type of charge:
charges table
=============
id int (autoincremented primary key)
date datetime
amount money
hourly_charges table
====================
charge_id int (primary key, also a foreign key to charges table)
start_time datetime
end_time datetime
I need to write a query that will duplicate all charges meeting a certain criteria by inserting new records into both the charges table and the hourly_charges table.
Here is some non-working pseudo-code that hopefully will get across what I would like to accomplish:
INSERT INTO charges JOIN hourly_charges
(
charges.date,
charges.amount,
hourly_charges.charge_id,
hourly_charges.start_time,
hourly_charges.end_time
)
SELECT
date,
amount,
SCOPE_IDENTITY(),
start_time,
end_time
FROM charges
JOIN hourly_charges
ON charges.id = hourly_charges.charge_id
WHERE some condition is true
Now I realize this code is invalid and I'll have to go about this an entirely different way but I'm wondering if someone can tell me what the proper way is.
Thanks,
Adam Soltys
http://adamsoltys.com/
View 3 Replies
View Related
Oct 5, 2013
I'm trying to get the number of records from one table where a column matches another column in a 2nd table. I then need the total values of another column that it has selected.
SELECT HOLIDAY_REF].holiday_id, COUNT([BOOKING].booking_status_id) AS record_count COUNT([BOOKING].total_value) AS total_value FROM [HOLIDAY_REF] LEFT OUTER JOIN [BOOKING] ON [HOLIDAY_REF].holiday_id = [BOOKING].booking_status_id WHERE [BOOKING].holiday_id=[HOLIDAY_REF].holiday_id && booking_status_id = '330'
Table 1 HOLIDAY_REF
holiday_id | holiday_name
1 | Italy
2 | Russia
3 | Spain
Table 2 BOOKING
holiday_id | booking_status_id | total_value
1 | 330 | 2500
3 | 330 | 1500
1 | 330 | 1750
2 | 330 | 1240
2 | 330 | 5600
Results would be:
Holiday_id | holiday_name | total_value | record_count
1 | Italy | 4250 | 2
2 | Russia | 6840 | 2
3 | Spain | 1500 | 1
Not sure I'm going about it the right way.
View 3 Replies
View Related
Sep 27, 2007
Hi!
I want to get some fields from more than one table. How can I use select command to do this? Please help me! The results should be in one table only!
Thanks in advance!
View 9 Replies
View Related
Feb 14, 2006
Hi,I have two tables: Code and Color.The create command for them is :create table Color(Partnum varchar(10),Eng_Color char(10),Span_Color char(20),Frch_Color char(20),CONSTRAINT pkPartnum PRIMARY KEY(Partnum))create table Code(Partnum varchar(10),Barcode varchar(11),I2of5s varchar(13),I2of5m varchar(13),UPC varchar(11),BigboxBCode varchar(11),DrumBCode varchar(11),TrayBCode varchar(11),QtyBCode varchar(11),CONSTRAINT fkPartnum FOREIGN KEY(Partnum) references Color(Partnum))Now my question is,how can i give a select statement such that I can get all the fields asoutput.Also plz note that the above is a sample. I have another 9 tables and Ineed a solutionsuch that on being refered by Partnum, I can get all the attributes.Thanks
View 19 Replies
View Related
Aug 20, 2007
Hi,
I have a number of related tables:
RGData is related to RGCrossReference
RCPPositionData is related to RCPCrossReference
RGCrossReference is also related to RCPCrossReference.
The data is returned correctly from these tables.
However, I also want to return data from another table - RComments.
How do I do this?
RComments is related to either RGData or RCPPositionData only.
Thanks.
Code Snippet
SELECT cm.CommentImage AS ViewComment, gd.PositionID AS GPositionID, cd.UniquePositionID AS CPPositionID
FROM RGData gd
INNER JOIN
RGCrossReference g
ON g.GPositionID = gd.PositionID
INNER JOIN
RCPCrossReference c
ON c.GMatchID = g.GMatchID
INNER JOIN
RCPPositionData cd
ON cd.UniquePositionID = c.CPPositionID
left outer JOIN
RComments cm
ON ((cm.CPPositionID = cd.UniquePositionID) or (cm.GPositionID = gd.PositionID))
AND cm.CommentsDate =
(SELECT MAX(CommentsDate) AS Expr1
FROM RComments
WHERE (GPositionID = g.GPositionID))
WHERE
(cd.Quantity != gd.Quantity
OR
cd.Currency != gd.Currency)
AND g.ForcedMatch = 'no';
View 3 Replies
View Related
Jul 13, 2015
I have the following two tables....
tblTimeEntry
-entryID
-entryDate
-entryUser
-entryJob
-entryTask
-entryWeekNo
tblWagesWeeks
-weekID
-weekDay
-date
I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.
View 11 Replies
View Related
Apr 21, 2008
Hello, I have an aspx page with a text box. The user may enter a large portion of text including symbols like ' or ". On form submitting I would like a database (text or varchar(MAX) ) field to be updated with the value of the text box...The query that I have created for this purpose is the following: "string query ="UPDATE Article SET A_Content='" + content + "' WHERE A_Id=" + id; As I said before the user may (and in most cases must) enter special characters like ' (example: bla bla blah it's very good bla bla blah).The problem is that ' or " close the query string (SQL) and interpreter things that I am reffering to a field's name instead of the fields value (I think) resulting the following error:The identifier that starts with...is too long.Maximum length is 128.Unclosed quotation mark after the character string ...I think that it must be a very common problem with many solutions but for some reason I cant find anything on the web.What is the common practice to overcome the problem?Is there a function that might transform text into SQL acceptable text?Should I create methods for Replacing ' with other characters like ^^^ (text can be quite big...)?Thanx for any suggestions!
View 3 Replies
View Related
Feb 2, 2004
I want to run multiple DTS packages which export data into text files.
There is only one Data Source ..and multiple destinations.
When i write a code for this in VB ,for each Package i need to define the source connectioninividually. Can't i use the same Source connection which i used for the first package in the subsequent packages?
View 1 Replies
View Related
Apr 3, 2006
Information for configuring Service Broker when you have multiple initiators seems to be thin on the ground.
The examples are all point-to-point and seem to require separate logins for each initiator and corresponding exchange of certificates using a multi-pass installation.
E.g. Rushi's ServiceListing API.
We have an application with several hundred workstations which need to send transaction data to department servers and then on to corporate. There is also a need to replicate small amounts of reference data to all levels.
The examples seem to generate an installation and administrative nightmare.
I have a question - would the following scheme work?
1. All our machines are on the same domain so transport security can be handled by Windows Security (One less set of certificates to exchange).
2. Generate 2 Certificates on the Corporate database and export them to the department and workstations
use these pre-installed certificates in each database to handle dialog security for all converations.
Ie. multiple workstations feed into one user id on the
department server using a single certificate. Ditto for all the department servers feeding into corporate.
3. Use Service broker id's to determine where messages are sent (as the same services would be installed at multiple locations).
4. When a database is intalled/restored at a workstation does this invalidate any certificates that are shipped with it?
View 5 Replies
View Related
Feb 18, 2004
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?
Any ideas?
Thanks in advance.
View 6 Replies
View Related
