I have a float field in a table ... when I try and add the result of the following division ... 3086/3600 ... to that field it displays as 0.0.
Can anyone explain why?
Does anyone know how to get around this and get 0.857 into that field instead.
I have tried changing the data type to Decimal(20,2) but this has had no effect.
Any suggestions would be very much appreciated.Solved it ... just do
3086 / CAST(3600 AS FLOAT)
and case solved :)|||Hi , Try this
select 3086/3600.00
will give u the desired result...
Cheers
Gola Munjal
Originally posted by briansy
I have a float field in a table ... when I try and add the result of the following division ... 3086/3600 ... to that field it displays as 0.0.
Can anyone explain why?
Does anyone know how to get around this and get 0.857 into that field instead.
I have tried changing the data type to Decimal(20,2) but this has had no effect.
Any suggestions would be very much appreciated.
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 22, 2012
3000 rows = 45 sec
I've got a table <Person> with 70 columns
that contains about 3000 rows
It takes 45 sec in the QA to run <Select * From Person>
3000 rows = 45 sec = Poor performances
So how in the heck do you all do to run
<Select * From MillionPersonsTable>
You've got superdoopers alien machines ?
(sorry, i've got terrible perf problems)Check for activity, locks on your server, try just select count(*) from your table or select one column from your table. It is impossible to have such performance on sql server.|||Do you servers have dual processors, and multiple hard drives?|||pentium III or IV|||select count(*) takes 3 secs
select column takes 3 secs too
I tought that I had poor perfs on Sybase
But they're practically the same on SQL Server|||How much data are you returning to the client?
select dpages/128 as KB
from sysindexes
where id = object_id('yourtable')
and indid in (0, 1)
70 columns sounds awfully wide.|||1 KB|||Heh. Small problem with units. Guess that's why I don't work at NASA...
You are returning somewhere between 1 and 2MB of data to the client. Still 45 seconds sounds a bit long for 1MB of data to wiggle its way through your cabling. Is the server in your building, or remote?|||the server is in the building|||The select you did above on a single column. Was that an indexed column? If so, how log does it take to return an unindexed column?|||3 secs ...
for indexed or non indexed columns|||OK. Last thought.
Open a QA session to the server, and note the SPID number (bottom right). In this window run the select * from table query. In a second window run the following:
select spid, cpu, physical_io, memusage, waittype, lastwaittype, blocked, waitresource
from master..sysprocesses
where spid = (your spid)
Blocked should always be 0 (so if it is not, you have an answer right there).
Lastwaittype is the type of waiting the connection did last, so it will not change often.
Waittype is a binary field that is the current waittype of the connection. You can find some of the definitions in KB article Q244455. 0x800 is network IO, I believe.
Physical_IO is the one I suspect you will see jump. If this jumps early in the query, then you are reading the table from disk. Table scans tend to recylcle memory this way, to keep more efficient tables in memory. If you have outrageous physical_io, you can look at trying to break up the table, or simply not do select * on it without a where clause.|||I'll check that tomorrow.
Thank you for all your time
Caroline|||Are you running any traces ? Is sql server agent running ?|||Run dbcc showcontig on the table, I bet you have high fragmentation. If you do not have a clustered index on the table, you should. There are rarely times that a table should not have a clustered index.
HTH|||dbcc showcontig
Table : 'T74PERS' (453576654); index ID = 1, base de donnes ID = 9
Analyse du niveau TABLE effectue.
- Pages analyses........................: 213
- extensions analyses.....................: 28
- extensions commutes....................: 28
- Moy des pages par extension............: 7.6
- Densit d'analyse [meilleure valeur du compte rel]......: 93.10% [27:29]
- Fragmentation d'analyse logique..: 12.68%
- Fragmentation d'analyse d'extension..: 3.57%
- Moy octets libres par page................: 389.8
- Densit de page moy (pleine).........: 95.18%
Is 12.68% and 3.57% high fragmentation ?|||I've got a clustered index on one of the columns of the table|||Your numbers look OK to me. Don't think it is an frag problem
that contains about 3000 rows
It takes 45 sec in the QA to run <Select * From Person>
3000 rows = 45 sec = Poor performances
So how in the heck do you all do to run
<Select * From MillionPersonsTable>
You've got superdoopers alien machines ?
(sorry, i've got terrible perf problems)Check for activity, locks on your server, try just select count(*) from your table or select one column from your table. It is impossible to have such performance on sql server.|||Do you servers have dual processors, and multiple hard drives?|||pentium III or IV|||select count(*) takes 3 secs
select column takes 3 secs too
I tought that I had poor perfs on Sybase
But they're practically the same on SQL Server|||How much data are you returning to the client?
select dpages/128 as KB
from sysindexes
where id = object_id('yourtable')
and indid in (0, 1)
70 columns sounds awfully wide.|||1 KB|||Heh. Small problem with units. Guess that's why I don't work at NASA...
You are returning somewhere between 1 and 2MB of data to the client. Still 45 seconds sounds a bit long for 1MB of data to wiggle its way through your cabling. Is the server in your building, or remote?|||the server is in the building|||The select you did above on a single column. Was that an indexed column? If so, how log does it take to return an unindexed column?|||3 secs ...
for indexed or non indexed columns|||OK. Last thought.
Open a QA session to the server, and note the SPID number (bottom right). In this window run the select * from table query. In a second window run the following:
select spid, cpu, physical_io, memusage, waittype, lastwaittype, blocked, waitresource
from master..sysprocesses
where spid = (your spid)
Blocked should always be 0 (so if it is not, you have an answer right there).
Lastwaittype is the type of waiting the connection did last, so it will not change often.
Waittype is a binary field that is the current waittype of the connection. You can find some of the definitions in KB article Q244455. 0x800 is network IO, I believe.
Physical_IO is the one I suspect you will see jump. If this jumps early in the query, then you are reading the table from disk. Table scans tend to recylcle memory this way, to keep more efficient tables in memory. If you have outrageous physical_io, you can look at trying to break up the table, or simply not do select * on it without a where clause.|||I'll check that tomorrow.
Thank you for all your time
Caroline|||Are you running any traces ? Is sql server agent running ?|||Run dbcc showcontig on the table, I bet you have high fragmentation. If you do not have a clustered index on the table, you should. There are rarely times that a table should not have a clustered index.
HTH|||dbcc showcontig
Table : 'T74PERS' (453576654); index ID = 1, base de donnes ID = 9
Analyse du niveau TABLE effectue.
- Pages analyses........................: 213
- extensions analyses.....................: 28
- extensions commutes....................: 28
- Moy des pages par extension............: 7.6
- Densit d'analyse [meilleure valeur du compte rel]......: 93.10% [27:29]
- Fragmentation d'analyse logique..: 12.68%
- Fragmentation d'analyse d'extension..: 3.57%
- Moy octets libres par page................: 389.8
- Densit de page moy (pleine).........: 95.18%
Is 12.68% and 3.57% high fragmentation ?|||I've got a clustered index on one of the columns of the table|||Your numbers look OK to me. Don't think it is an frag problem
30 Day Old Delete
I have a database of posts which i want to delete after 30days of being on the site, my code so far gets all the data out of the table and then if their is more than 0 rows it loops within the tblTable and finds all the posts made within the last 30days and deletes them, my problem is how do I get it to work out 30days ago, for 30days time, its just " DateTime.Now.AddDays(30);" but i cant seem to do something as simple for back in time, heres my code :
private void DeleteOldStuff()
{
string strSQL = "SELECT * FROM ForumPosts";
SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BlinkConnectionString"].ConnectionString);
Connection.Open();
SqlCommand comm = new SqlCommand(strSQL, Connection);
SqlDataAdapter da = new SqlDataAdapter(comm);
tblData = new DataTable();
da.Fill(tblData);
Connection.Close();
if (tblData.Rows.Count > 0)
{
for (int i = 0; i < tblData.Rows.Count; i++)
{
DataRow dr = tblData.Rows[i];
if ("30DAYSAGO" <= dr["DateCreated"])
{
Connection.Open();
SqlCommand Delete = new SqlCommand("DELETE FROM ForumPosts WHERE PostID = '" + dr["PostID"].ToString() +"'", Connection);
Delete.ExecuteNonQuery();
Connection.Close();
}
}
}
}
Any help / advice is appriciated! Thanks John
Meaby it is better if u do it in Sql code to select all data if is older than 30 days.. example
; //Pseudokod
Select * from ForumPosts where dataPost < DateTime.Now.AddDays(-30)//
and delete u can do it like this
delete from ForumPosts where dataPost < DateTime.Now.AddDays(-30)// Pseudokod
Sorry for my bad english
||| No worrys that works much better thanks a million John
3 views becoming unweildy
Hi
Table T_VesselRoute { VR_VesselTrip, VR_DateTime, VR_Distance }
FOR EACH { TODAY, TODAY+1, ..., TODAY+6, REST }
Need UNIQUE COUNT(VR_VesselTrip), COUNT(VR_DateTime),
SUM(VR_Distance)
I have devised the following 3 views to achieve this, but it is
becoming too big - especially the 3rd view (given incomplete, but u
get the idea). I also need to count UNIQUE VR_VesselTrips - 2nd view
I'm quite a newbie to SQL. Is there a better way to do this?
Riyaz
CREATE VIEW CleanMoveStatus AS
SELECT VR.*, CAST(VR_DateTime AS INT) AS MoveDate,
FROM T_VesselRoute AS VR
WHERE MoveDate >= CAST(GETDATE() AS INT)
GO
CREATE VIEW DailyMoveStatus AS
SELECT CMS.MoveDate AS MoveDate,
COUNT(CMS.VR_VesselTrip) AS DailyTrips, -- UNIQUE COUNT ?
COUNT(CMS.MoveDate) AS DailyMoves,
SUM(CMS.VR_Distance) AS DailyDistance
FROM CleanMoveStatus AS CMS
GROUP BY MoveDate
GO
CREATE VIEW MoveStatus AS
SELECT
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyTrips ELSE 0 END) AS TodaysDailyTrips,
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyMoves ELSE 0 END) AS TodaysDailyMoves,
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyDistance ELSE 0 END) AS TodaysDailyDistance,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyTrips ELSE 0 END) AS TodayPlus1DailyTrips,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyMoves ELSE 0 END) AS TodayPlus1DailyMoves,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyDistance ELSE 0 END) AS TodayPlus1DailyDistance
FROM DailyMoveStatus AS DMS
GOHi
"riyaz.mansoor@.gmail.com" wrote:
> Hi
> Table T_VesselRoute { VR_VesselTrip, VR_DateTime, VR_Distance }
> FOR EACH { TODAY, TODAY+1, ..., TODAY+6, REST }
> Need UNIQUE COUNT(VR_VesselTrip), COUNT(VR_DateTime),
> SUM(VR_Distance)
> I have devised the following 3 views to achieve this, but it is
> becoming too big - especially the 3rd view (given incomplete, but u
> get the idea). I also need to count UNIQUE VR_VesselTrips - 2nd view
> I'm quite a newbie to SQL. Is there a better way to do this?
> Riyaz
>
> CREATE VIEW CleanMoveStatus AS
> SELECT VR.*, CAST(VR_DateTime AS INT) AS MoveDate,
> FROM T_VesselRoute AS VR
> WHERE MoveDate >= CAST(GETDATE() AS INT)
> GO
> CREATE VIEW DailyMoveStatus AS
> SELECT CMS.MoveDate AS MoveDate,
> COUNT(CMS.VR_VesselTrip) AS DailyTrips, -- UNIQUE COUNT ?
> COUNT(CMS.MoveDate) AS DailyMoves,
> SUM(CMS.VR_Distance) AS DailyDistance
> FROM CleanMoveStatus AS CMS
> GROUP BY MoveDate
> GO
> CREATE VIEW MoveStatus AS
> SELECT
> SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
> DMS.DailyTrips ELSE 0 END) AS TodaysDailyTrips,
> SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
> DMS.DailyMoves ELSE 0 END) AS TodaysDailyMoves,
> SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
> DMS.DailyDistance ELSE 0 END) AS TodaysDailyDistance,
> SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
> DMS.DailyTrips ELSE 0 END) AS TodayPlus1DailyTrips,
> SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
> DMS.DailyMoves ELSE 0 END) AS TodayPlus1DailyMoves,
> SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
> DMS.DailyDistance ELSE 0 END) AS TodayPlus1DailyDistance
> FROM DailyMoveStatus AS DMS
> GO
>
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
useful sample data.
You don't give which version of SQL Server you are using. With SQL 2005
there is a pivot operator. The method you show will work on SQL 2000. Usually
pivoting is best left to the client. You should be able to do this with a
single view, which may be better if you don't need the other views. You make
want to make the view indexed which may improve performance depending how
many changes will be made.
If you want to use a single view the following should work assuming that you
are only testing the presence of the values indicating an entry and when
present it indicates a single occassion. It is not tested as there is no ddl.
CREATE VIEW MoveStatus AS
SELECT
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyTrips IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyTrips,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyMoves IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyMoves,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyDistance IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyDistance,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1
AND VR.DailyTrips IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyTrips,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1
AND VR.DailyMoves IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyMoves,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1 AND
VR.DailyDistance IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyDistance
FROM T_VesselRoute AS VR
WHERE VR.MoveDate >= CAST(GETDATE() AS INT)
Johnsql
Table T_VesselRoute { VR_VesselTrip, VR_DateTime, VR_Distance }
FOR EACH { TODAY, TODAY+1, ..., TODAY+6, REST }
Need UNIQUE COUNT(VR_VesselTrip), COUNT(VR_DateTime),
SUM(VR_Distance)
I have devised the following 3 views to achieve this, but it is
becoming too big - especially the 3rd view (given incomplete, but u
get the idea). I also need to count UNIQUE VR_VesselTrips - 2nd view
I'm quite a newbie to SQL. Is there a better way to do this?
Riyaz
CREATE VIEW CleanMoveStatus AS
SELECT VR.*, CAST(VR_DateTime AS INT) AS MoveDate,
FROM T_VesselRoute AS VR
WHERE MoveDate >= CAST(GETDATE() AS INT)
GO
CREATE VIEW DailyMoveStatus AS
SELECT CMS.MoveDate AS MoveDate,
COUNT(CMS.VR_VesselTrip) AS DailyTrips, -- UNIQUE COUNT ?
COUNT(CMS.MoveDate) AS DailyMoves,
SUM(CMS.VR_Distance) AS DailyDistance
FROM CleanMoveStatus AS CMS
GROUP BY MoveDate
GO
CREATE VIEW MoveStatus AS
SELECT
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyTrips ELSE 0 END) AS TodaysDailyTrips,
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyMoves ELSE 0 END) AS TodaysDailyMoves,
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyDistance ELSE 0 END) AS TodaysDailyDistance,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyTrips ELSE 0 END) AS TodayPlus1DailyTrips,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyMoves ELSE 0 END) AS TodayPlus1DailyMoves,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyDistance ELSE 0 END) AS TodayPlus1DailyDistance
FROM DailyMoveStatus AS DMS
GOHi
"riyaz.mansoor@.gmail.com" wrote:
> Hi
> Table T_VesselRoute { VR_VesselTrip, VR_DateTime, VR_Distance }
> FOR EACH { TODAY, TODAY+1, ..., TODAY+6, REST }
> Need UNIQUE COUNT(VR_VesselTrip), COUNT(VR_DateTime),
> SUM(VR_Distance)
> I have devised the following 3 views to achieve this, but it is
> becoming too big - especially the 3rd view (given incomplete, but u
> get the idea). I also need to count UNIQUE VR_VesselTrips - 2nd view
> I'm quite a newbie to SQL. Is there a better way to do this?
> Riyaz
>
> CREATE VIEW CleanMoveStatus AS
> SELECT VR.*, CAST(VR_DateTime AS INT) AS MoveDate,
> FROM T_VesselRoute AS VR
> WHERE MoveDate >= CAST(GETDATE() AS INT)
> GO
> CREATE VIEW DailyMoveStatus AS
> SELECT CMS.MoveDate AS MoveDate,
> COUNT(CMS.VR_VesselTrip) AS DailyTrips, -- UNIQUE COUNT ?
> COUNT(CMS.MoveDate) AS DailyMoves,
> SUM(CMS.VR_Distance) AS DailyDistance
> FROM CleanMoveStatus AS CMS
> GROUP BY MoveDate
> GO
> CREATE VIEW MoveStatus AS
> SELECT
> SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
> DMS.DailyTrips ELSE 0 END) AS TodaysDailyTrips,
> SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
> DMS.DailyMoves ELSE 0 END) AS TodaysDailyMoves,
> SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
> DMS.DailyDistance ELSE 0 END) AS TodaysDailyDistance,
> SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
> DMS.DailyTrips ELSE 0 END) AS TodayPlus1DailyTrips,
> SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
> DMS.DailyMoves ELSE 0 END) AS TodayPlus1DailyMoves,
> SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
> DMS.DailyDistance ELSE 0 END) AS TodayPlus1DailyDistance
> FROM DailyMoveStatus AS DMS
> GO
>
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
useful sample data.
You don't give which version of SQL Server you are using. With SQL 2005
there is a pivot operator. The method you show will work on SQL 2000. Usually
pivoting is best left to the client. You should be able to do this with a
single view, which may be better if you don't need the other views. You make
want to make the view indexed which may improve performance depending how
many changes will be made.
If you want to use a single view the following should work assuming that you
are only testing the presence of the values indicating an entry and when
present it indicates a single occassion. It is not tested as there is no ddl.
CREATE VIEW MoveStatus AS
SELECT
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyTrips IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyTrips,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyMoves IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyMoves,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyDistance IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyDistance,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1
AND VR.DailyTrips IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyTrips,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1
AND VR.DailyMoves IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyMoves,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1 AND
VR.DailyDistance IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyDistance
FROM T_VesselRoute AS VR
WHERE VR.MoveDate >= CAST(GETDATE() AS INT)
Johnsql
3 triggers into one
Hi,
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.
supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.
supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>
3 triggers into one
Hi,
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>
3 triggers into one
Hi,
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I want to synchronize some records in one destination table in
>> a
>> SQL server from some records in 3 source tables in another SQL server.
>> Instead of building 3 triggers for each source table, how can I make one
>> trigger and work for 3? Thanks.
>>
>
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I want to synchronize some records in one destination table in
>> a
>> SQL server from some records in 3 source tables in another SQL server.
>> Instead of building 3 triggers for each source table, how can I make one
>> trigger and work for 3? Thanks.
>>
>
3 tables wildcard searches
Hi,
I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.
There are 3 tables involved, the jobs, the applicants and the applications.
I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.
There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.
My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?
So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?
Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.
So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.
Thanks in advance.Can you post the URL for this assignment, or at least scan in the page(s) you got in class if they aren't available online?
-PatP|||If I understand the question correctly, something like this might work:
create proc usp_search(@.searchstring varchar(100))
as
select * -- replace with required cols
from applicants
join applications on applicants.applicantid = applications.applicantid
join jobs on applications.jobid = jobs.jobid
where forename like @.searchstring + '%' -- Auto Wild Card (Begins With)
or surname like @.searchstring + '%'
or jobtitle like @.searchstring + '%'
or jobref = @.searchstring
or applicantid = @.searchstring
I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.
There are 3 tables involved, the jobs, the applicants and the applications.
I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.
There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.
My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?
So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?
Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.
So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.
Thanks in advance.Can you post the URL for this assignment, or at least scan in the page(s) you got in class if they aren't available online?
-PatP|||If I understand the question correctly, something like this might work:
create proc usp_search(@.searchstring varchar(100))
as
select * -- replace with required cols
from applicants
join applications on applicants.applicantid = applications.applicantid
join jobs on applications.jobid = jobs.jobid
where forename like @.searchstring + '%' -- Auto Wild Card (Begins With)
or surname like @.searchstring + '%'
or jobtitle like @.searchstring + '%'
or jobref = @.searchstring
or applicantid = @.searchstring
3 tables to join
hi,
I knew how to join 2 tables but i have a process to select 3 tables. I have a sample table and field below. I want to join Parts & Orders using field Prt_no and Supplier & Parts using field Sup _code
Parts table Orders Table Supplier Table
Prt_no Prt_no Sup_code
Prt_name Oh_qt Sup_name
Re_Level Or_no
Pri_amnt
Sup_code
Thanks...SELECT
p.Prt_no,
o.Prt_no,
s.Sup_code,
p.Sup_code
FROM
Parts p
INNER JOIN Orders o ON p.Prt_no = o.Prt_no
INNER JOIN Supplier s ON p.Sup_code = s.Sup_code
--I would invest in a good TSQL book if I were you. Teach Yourself Transact-SQL in 21 Days by Sam's Publishing is really good for beginners.|||Where I can get that book?
I knew how to join 2 tables but i have a process to select 3 tables. I have a sample table and field below. I want to join Parts & Orders using field Prt_no and Supplier & Parts using field Sup _code
Parts table Orders Table Supplier Table
Prt_no Prt_no Sup_code
Prt_name Oh_qt Sup_name
Re_Level Or_no
Pri_amnt
Sup_code
Thanks...SELECT
p.Prt_no,
o.Prt_no,
s.Sup_code,
p.Sup_code
FROM
Parts p
INNER JOIN Orders o ON p.Prt_no = o.Prt_no
INNER JOIN Supplier s ON p.Sup_code = s.Sup_code
--I would invest in a good TSQL book if I were you. Teach Yourself Transact-SQL in 21 Days by Sam's Publishing is really good for beginners.|||Where I can get that book?
Tuesday, March 20, 2012
3 table query help
i have 3 tables member_info, subscription_info, exclude
member_info has 3 columns (login, fname, lname)
subscription_info has 5 columns
(login, subid, monthlypayment, startdate, enddate)
exclude has only one column (login)
I want to write a query that would return:
login, fname, lname, subid, monthlypayment
where login is not on of the logins from exclude table..
I tried this query:
select login, fname, lname, subid, monthlypayment
from member_info, subscription_info, exclude
where member_info.login=subscription_info.login and
member_info.login <> exclude.login
but that yeilds duplicate records, please helptry:
select m.login, m.fname, m.lname, s.subid, s.monthlypayment
from member_info m
inner join subscription_info s ON m.login = s.login
left outer join exclude e ON e.login = m.login
where e.login IS NULL
>--Original Message--
>i have 3 tables member_info, subscription_info, exclude
>member_info has 3 columns (login, fname, lname)
>subscription_info has 5 columns
>(login, subid, monthlypayment, startdate, enddate)
>exclude has only one column (login)
>I want to write a query that would return:
>login, fname, lname, subid, monthlypayment
>where login is not on of the logins from exclude table..
>I tried this query:
>select login, fname, lname, subid, monthlypayment
>from member_info, subscription_info, exclude
>where member_info.login=subscription_info.login and
>member_info.login <> exclude.login
>but that yeilds duplicate records, please help
>.
>
member_info has 3 columns (login, fname, lname)
subscription_info has 5 columns
(login, subid, monthlypayment, startdate, enddate)
exclude has only one column (login)
I want to write a query that would return:
login, fname, lname, subid, monthlypayment
where login is not on of the logins from exclude table..
I tried this query:
select login, fname, lname, subid, monthlypayment
from member_info, subscription_info, exclude
where member_info.login=subscription_info.login and
member_info.login <> exclude.login
but that yeilds duplicate records, please helptry:
select m.login, m.fname, m.lname, s.subid, s.monthlypayment
from member_info m
inner join subscription_info s ON m.login = s.login
left outer join exclude e ON e.login = m.login
where e.login IS NULL
>--Original Message--
>i have 3 tables member_info, subscription_info, exclude
>member_info has 3 columns (login, fname, lname)
>subscription_info has 5 columns
>(login, subid, monthlypayment, startdate, enddate)
>exclude has only one column (login)
>I want to write a query that would return:
>login, fname, lname, subid, monthlypayment
>where login is not on of the logins from exclude table..
>I tried this query:
>select login, fname, lname, subid, monthlypayment
>from member_info, subscription_info, exclude
>where member_info.login=subscription_info.login and
>member_info.login <> exclude.login
>but that yeilds duplicate records, please help
>.
>
3 table query
I am trying to do something like this but keep getting a syntax error. How would I get something like this?
sql = "SELECT COUNT(optin) AS total_customers_optin FROM (SELECT tbl_customers.*, tbl_register.*, tbl_photos.* FROM tbl_customers, tbl_register, tbl_photos WHERE tbl_register.cust_id = tbl_customers.cust_id AND tbl_photos.photo_id = tbl_register.photo_id AND tbl_photos.photo_date = '04/26/2003' AND tbl_photos.event_id = '109' AND tbl_customers.optin = 'Yes' )">> I am trying to do something like this ...
>> How would I get something like this?
right now you appear to be counting photos
i can think of many queries that are "something like this"
what did you actually want? :)
rudy
http://rudy.ca/|||I am trying to count the number of distinct customers (using distinct email) who have selected Yes in the optin field and registered a photo from the event_id '109' and the photo_date is '04/26/2003'|||try this:SELECT count(DISTINCT tbl_customers.email)
FROM tbl_customers
, tbl_register
, tbl_photos
WHERE tbl_register.cust_id
= tbl_customers.cust_id
AND tbl_photos.photo_id
= tbl_register.photo_id
AND tbl_photos.photo_date = '04/26/2003'
AND tbl_photos.event_id = '109'
AND tbl_customers.optin = 'Yes'rudy|||Thats it!
Thanks for your help.
sql = "SELECT COUNT(optin) AS total_customers_optin FROM (SELECT tbl_customers.*, tbl_register.*, tbl_photos.* FROM tbl_customers, tbl_register, tbl_photos WHERE tbl_register.cust_id = tbl_customers.cust_id AND tbl_photos.photo_id = tbl_register.photo_id AND tbl_photos.photo_date = '04/26/2003' AND tbl_photos.event_id = '109' AND tbl_customers.optin = 'Yes' )">> I am trying to do something like this ...
>> How would I get something like this?
right now you appear to be counting photos
i can think of many queries that are "something like this"
what did you actually want? :)
rudy
http://rudy.ca/|||I am trying to count the number of distinct customers (using distinct email) who have selected Yes in the optin field and registered a photo from the event_id '109' and the photo_date is '04/26/2003'|||try this:SELECT count(DISTINCT tbl_customers.email)
FROM tbl_customers
, tbl_register
, tbl_photos
WHERE tbl_register.cust_id
= tbl_customers.cust_id
AND tbl_photos.photo_id
= tbl_register.photo_id
AND tbl_photos.photo_date = '04/26/2003'
AND tbl_photos.event_id = '109'
AND tbl_customers.optin = 'Yes'rudy|||Thats it!
Thanks for your help.
3 table left outer join
I am joining 3 tables. 1st table I want all rows; I also want all rows in table 1 not in table 2 or in Table 2 but have a blank journal id and all rows in table 1 not in table 3. HELP!
SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = A.INVOICE
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.INVOICE = C.ITEM
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' 'could you post your query again, there seems to be a typo in it
AND A.INVOICE = C.ITEM
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
the PS_BI_HDR D is wrong where it is
rudy|||SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = A.INVOICE
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.INVOICE = C.ITEM
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' '
This is the query. Note: ITEM and INVOICE are on in the same on two different tables. I forgot the ().|||[QUOTE][SIZE=1]Originally posted by AMYC
SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = B.INVOICE
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' '
Update; found one error ;)|||i still see a syntax error in the following snippet of code --
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN ...
this is structurally the same as
FROM (derived table) foo bar LEFT OUTER JOIN ...
you can give the derived table one alias name but not two
rudy|||Thanks for your help but I feel like I have bigger issues than syntax errors. It's more structural. This is a new approach I am trying but I still get rows I should not ie rows from BI_ACCT_ENTRY that should not be returned. THanks!
SELECT A.BUSINESS_UNIT, A.INVOICE
FROM PS_BI_HDR A WHERE (NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU AND A.INVOICE = B.ITEM)
AND NOT EXISTS(SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INVOICE = C.INVOICE))
OR EXISTS (SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' ')|||i don't mean to criticize but it will help you if you get in the habit of formatting your code like this:
SELECT A.BUSINESS_UNIT
, A.INVOICE
FROM PS_BI_HDR A
WHERE (
NOT EXISTS
( SELECT B.GROUP_BU
, B.ITEM
FROM PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU
AND A.INVOICE = B.ITEM )
AND NOT EXISTS
( SELECT BUSINESS_UNIT
, INVOICE
FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.INVOICE = C.INVOICE )
)
OR EXISTS
( SELECT BUSINESS_UNIT
, INVOICE
FROM PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' ')
now you can plainly see that you will get PS_BI_HDR records if any PS_BI_ACCT_ENTRY entries exist with a blank JOURNAL_ID
was that your problem?
by the way, i would not alias the table in the 3rd subquery with C because you've already used that in the 2nd subquery -- not that it causes an error, but you never know...
rudy|||I do format my code but just copying and pasting here as it is a total pain to format it on here :) anyway, I fixed it. Thanks!
SELECT A.BUSINESS_UNIT, A.INVOICE
FROM PS_BI_HDR A WHERE
EXISTS (SELECT C.BUSINESS_UNIT, C.INVOICE FROM
PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' '
AND NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM
PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU
AND A.INVOICE = B.ITEM)
OR NOT EXISTS(SELECT BUSINESS_UNIT,
INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.INVOICE = C.INVOICE)
ORDER BY A.INVOICE|||it is a total pain to format it on here
not really
just wrap it in code tags -- i typically type them, but you can also use the number/hash sign # button (between the IMG and PHP buttons when you create a post)|||I didn't realize I could do that. Thanks!!sql
SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = A.INVOICE
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.INVOICE = C.ITEM
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' 'could you post your query again, there seems to be a typo in it
AND A.INVOICE = C.ITEM
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
the PS_BI_HDR D is wrong where it is
rudy|||SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = A.INVOICE
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.INVOICE = C.ITEM
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' '
This is the query. Note: ITEM and INVOICE are on in the same on two different tables. I forgot the ().|||[QUOTE][SIZE=1]Originally posted by AMYC
SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = B.INVOICE
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' '
Update; found one error ;)|||i still see a syntax error in the following snippet of code --
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN ...
this is structurally the same as
FROM (derived table) foo bar LEFT OUTER JOIN ...
you can give the derived table one alias name but not two
rudy|||Thanks for your help but I feel like I have bigger issues than syntax errors. It's more structural. This is a new approach I am trying but I still get rows I should not ie rows from BI_ACCT_ENTRY that should not be returned. THanks!
SELECT A.BUSINESS_UNIT, A.INVOICE
FROM PS_BI_HDR A WHERE (NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU AND A.INVOICE = B.ITEM)
AND NOT EXISTS(SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INVOICE = C.INVOICE))
OR EXISTS (SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' ')|||i don't mean to criticize but it will help you if you get in the habit of formatting your code like this:
SELECT A.BUSINESS_UNIT
, A.INVOICE
FROM PS_BI_HDR A
WHERE (
NOT EXISTS
( SELECT B.GROUP_BU
, B.ITEM
FROM PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU
AND A.INVOICE = B.ITEM )
AND NOT EXISTS
( SELECT BUSINESS_UNIT
, INVOICE
FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.INVOICE = C.INVOICE )
)
OR EXISTS
( SELECT BUSINESS_UNIT
, INVOICE
FROM PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' ')
now you can plainly see that you will get PS_BI_HDR records if any PS_BI_ACCT_ENTRY entries exist with a blank JOURNAL_ID
was that your problem?
by the way, i would not alias the table in the 3rd subquery with C because you've already used that in the 2nd subquery -- not that it causes an error, but you never know...
rudy|||I do format my code but just copying and pasting here as it is a total pain to format it on here :) anyway, I fixed it. Thanks!
SELECT A.BUSINESS_UNIT, A.INVOICE
FROM PS_BI_HDR A WHERE
EXISTS (SELECT C.BUSINESS_UNIT, C.INVOICE FROM
PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' '
AND NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM
PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU
AND A.INVOICE = B.ITEM)
OR NOT EXISTS(SELECT BUSINESS_UNIT,
INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.INVOICE = C.INVOICE)
ORDER BY A.INVOICE|||it is a total pain to format it on here
not really
just wrap it in code tags -- i typically type them, but you can also use the number/hash sign # button (between the IMG and PHP buttons when you create a post)|||I didn't realize I could do that. Thanks!!sql
3 table joins - 3rd table join main not exist (return null columns) - please help!
Hello SQL Guru's,
|||Kwareol,
This has had me stumped for about 8 hours already and I think I've gotten to a point where I'm trying the same things over and over again and they are just not working. Any help would be greatly appreciated!
My Table Structure:
Table 1) 'Modules'
ModuleID | ModuleName | isVisible
--
1 Test 1 True
2 Test 2 True
3 Test 3 False
4 Test 4 True
Table 2) 'ModuleUserTypes'
ID | ModuleID | UserType
1 1 1
2 1 2
3 2 1
4 3 1
5 4 1
6 4 2
Table 3) 'ModuleUserSettings'
ID | ModuleID | UserID | CustomTitle | BGColor
--
1 2 1 New Title2 Black
2 2 2 New Title2 White
3 3 1 New Title3 Orange
4 4 1 NewTitle4 Yellow
My Goal:
To be able to join the 3 tables 'Modules', 'ModuleUserTypes', and 'ModuleUserSettings' together and return 'ModuleID, ModuleName, CustomTitle, BGColor' for ALL Modules with UserType = 1 along with associated ModuleUserSettings IF the UserSetting exists, otherwise NULL for the columns.
My desired result set:
UserID = 1
UserType = 1
isVisible = True
ModuleID | ModuleName | CustomTitle | BGColor
--
1 Test1 NULL NULL
2 Test2 New Title2 White
4 Test4 New Title4 Yellow
I'm sure this type of query will be easy for someone out there, but rather hard for me!
Thanks for your efforts!
Execute the following query, to get your results :
select m.ModuleID,m.ModuleName,CustomTitle,BGColor
from Modules m left join ModuleUserTypes mut on m.ModuleID = mut.ModuleID
left join ModuleUserSettings mus on m.ModuleID = mus.ModuleID
where (UserID is null or UserID = 1)
and UserType=1
and IsVisible = 1
Assumption - IsVisible column is bit data type
otherwise - use -
and IsVisible = 'True'
Thanks
Naras.
|||you need to include your User table or you table that define the usertype of a user and you need to use left joinhope this helps
SELECT *
INTO #Modules
FROM (
SELECT 1 AS ModuleID
,'Test 1' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 2 AS ModuleID
,'Test 2' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 3 AS ModuleID
,'Test 3' AS ModuleName
, 'False' AS isVisible
UNION ALL
SELECT 4 AS ModuleID
,'Test 4' AS ModuleName
, 'True' AS isVisible
) Modules
SELECT *
INTO #ModuleUserTypes
FROM ( SELECT 1 AS [ID]
, 1 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 2 AS [ID]
, 1 AS ModuleID
, 2 AS UserType
UNION ALL
SELECT 3 AS [ID]
, 2 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 4 AS [ID]
, 3 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 5 AS [ID]
, 4 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 6 AS [ID]
, 4 AS ModuleID
, 2 AS UserType
) ModuleUserTypes
SELECT *
INTO #ModuleUserSettings
FROM (
SELECT 1 AS [ID]
, 2 AS ModuleID
, 1 AS UserID
, 'New Title2' AS CustomTitle
, 'White' AS BGColor
UNION ALL
SELECT 2 AS [ID]
, 2 AS ModuleID
, 2 AS UserID
, 'New Title2' AS CustomTitle
, 'Black' AS BGColor
UNION ALL
SELECT 3 AS [ID]
, 3 AS ModuleID
, 1 AS UserID
, 'New Title3' AS CustomTitle
, 'Orange' AS BGColor
UNION ALL
SELECT 4 AS [ID]
, 4 AS ModuleID
, 1 AS UserID
, 'New Title4' AS CustomTitle
, 'Yellow' AS BGColor
) ModuleUserSettings
SELECT *
INTO #Users
FROM (
SELECT 1 AS UserID
, 1 AS UserType
UNION ALL
SELECT 2 AS UserID
, 2 AS UserType
) Users
DECLARE @.UserType int
DECLARE @.UserID int
DECLARE @.isVisible varchar(5)
SET @.UserType = 1
SET @.UserID = 1
SET @.isVisible = 'True'
SELECT DISTINCT
m.ModuleID
, m.ModuleName
, mus.CustomTitle
, mus.BGColor
FROM #Modules m LEFT OUTER JOIN
#ModuleUserTypes mut ON m.ModuleID = mut.ModuleID LEFT OUTER JOIN
#ModuleUserSettings mus ON m.ModuleID = mus.ModuleID
AND mut.ModuleID = mus.ModuleID LEFT OUTER JOIN
#Users ut ON mut.UserType = ut.UserType
AND mus.UserID = ut.UserID
WHERE ISNULL(mut.UserType,@.UserType) = @.UserType
AND ISNULL(mus.UserID,@.UserID) = @.UserID
AND ISNULL(m.isVisible,@.isVisible) = @.isVisible
DROP TABLE #Modules
DROP TABLE #ModuleUserTypes
DROP TABLE #ModuleUserSettings
DROP TABLE #Users|||
select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolour
from Modules m
join moduleusertypes mut
on m.moduleid = mut.moduleid
and mut.usertype = 1
left join ModuleUserSettings mus
on mus.moduleid = mut.moduleid
and mus.userid = mut.usertype
where IsVisible = 1
Assuming userid in 'ModuleUserSettings' is equal to UserType in 'ModuleUserTypes'
Regards,
kwareol
|||Thanks Nara's for your reply. I tried a similar statement but it was not filtering correctly. It would work until I added the UserType=1 and isVisible=1 to the where clause.|||Kwareol,
Your statement took me in the right direction!
All I needed to add was the UserID filter.
This is the final statement that works exactly as I needed:
select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolor
from Modules m
join moduleusertypes mut
on m.moduleid = mut.moduleid
and mut.usertype = 1
left join ModuleUserSettings mus
on mus.moduleid = mut.moduleid
and (mus.userid = 1 or mus.userid is null)
where IsVisible = 1
Thank you and everybody so much for your time and efforts!! It's much much appreciated!
(I marked this post as the final answer. I'm not exactly sure how this forums works and if users get some sort of point ranking for posting correct answers. If so, I will change it to Kwareol for him leading me in the direction I needed to go)
3 Table join question
3 Table join question.
Here are 3 tables.
T1
--
ID VAL
A 0
B 0
C 0
D 0
E 0
T2
--
ID METER
A 1
X 1
B 1
Y 1
T3
--
ID NUM
B 2
H 2
R 2
J 2
K 2
X 2
E 2
I was reading a tutorial that said you can join the three tables in these
ways:
T1 joins T2 which joins T3
T1 joins T2 and the Join of T1 and T2 joins T3
T1 joins T2 and T1 joins T3
The tutorial doesn't give any examples.
Which SQL statements would represent these three types of multiple table
joins?
GeoffGeoff,
T1 JOIN T2
ON T1.ID = T2.ID
JOIN T3
ON T2.ID = T3.ID
However note that the order in which the tables are joined will be
determined by SQL Server not the order in which the tables are listed in the
FROM clause unless the the SET FORCEPLAN statement is used.
HTH
J
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:3FA2D589-6EBF-414E-A4F9-FA37E41C0102@.microsoft.com...
>3 Table join question.
> Here are 3 tables.
>
> T1
> --
> ID VAL
> A 0
> B 0
> C 0
> D 0
> E 0
>
> T2
> --
> ID METER
> A 1
> X 1
> B 1
> Y 1
>
> T3
> --
> ID NUM
> B 2
> H 2
> R 2
> J 2
> K 2
> X 2
> E 2
>
> I was reading a tutorial that said you can join the three tables in these
> ways:
> T1 joins T2 which joins T3
> T1 joins T2 and the Join of T1 and T2 joins T3
> T1 joins T2 and T1 joins T3
> The tutorial doesn't give any examples.
> Which SQL statements would represent these three types of multiple table
> joins?
> Geoff|||I suppose your answer is an example of (1)?
(1) T1 joins T2 which joins T3
(2) T1 joins T2 and the Join of T1 and T2 joins T3
(3) T1 joins T2 and T1 joins T3
Geoff
"Jerry Spivey" wrote:
> Geoff,
> T1 JOIN T2
> ON T1.ID = T2.ID
> JOIN T3
> ON T2.ID = T3.ID
> However note that the order in which the tables are joined will be
> determined by SQL Server not the order in which the tables are listed in t
he
> FROM clause unless the the SET FORCEPLAN statement is used.
> HTH
> J
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:3FA2D589-6EBF-414E-A4F9-FA37E41C0102@.microsoft.com...
>
>|||Probably. Depends on how the optimizer chooses to join the tables. It may
join 1 to 2 and then that intermediate result set to 3 or 3 to 2 and than
intermediate result set to 1.
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:1181B891-8E78-4A9E-8CBA-A6E020A31A19@.microsoft.com...
>I suppose your answer is an example of (1)?
> (1) T1 joins T2 which joins T3
> (2) T1 joins T2 and the Join of T1 and T2 joins T3
> (3) T1 joins T2 and T1 joins T3
> Geoff
> "Jerry Spivey" wrote:
>|||O.K. I get it. Those three "ways" to join are the ways the RDBMS may have
implemented the JOIN for three tables. The results from each of the three
"ways" are the same.
I read the statement as "These are the three ways to join tables" and you
pick the type of join you want.
I was playing with this as an example of the (3)rd way
SELECT *
FROM (SELECT T1.ID, T1.VAL, T2.METER
FROM T1, T2
WHERE T1.ID = T2.ID) B INNER JOIN
(SELECT T3.ID, T3.NUM
FROM T1, T3
WHERE T1.ID = T3.ID) A ON B.ID = A.ID
I guess I missed the point of the statement in the tutorial.
Thanks for the clarification,
Geoff
"Jerry Spivey" wrote:
> Probably. Depends on how the optimizer chooses to join the tables. It ma
y
> join 1 to 2 and then that intermediate result set to 3 or 3 to 2 and than
> intermediate result set to 1.
>
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:1181B891-8E78-4A9E-8CBA-A6E020A31A19@.microsoft.com...
>
>|||An example of (2) is
(T1 JOIN T2 ON T1.ID = T2.ID) JOIN T3 ON T2.ID = T3.ID
By using parenthesis, you can force the order in which the tables are
joined, creating a scenario in which T1 joins T2 and T3 joins the join of T1
and T2.
An exapmle of (3) is
T1
JOIN T2 ON T1.ID = T2.ID
JOIN T3 ON T1.ID = T3.ID
I think that (1) and (3) are actually just different orders of the same JOIN
statement.
Chris
"Geoff" wrote:
> O.K. I get it. Those three "ways" to join are the ways the RDBMS may have
> implemented the JOIN for three tables. The results from each of the three
> "ways" are the same.
> I read the statement as "These are the three ways to join tables" and you
> pick the type of join you want.
> I was playing with this as an example of the (3)rd way
> SELECT *
> FROM (SELECT T1.ID, T1.VAL, T2.METER
> FROM T1, T2
> WHERE T1.ID = T2.ID) B INNER JOIN
> (SELECT T3.ID, T3.NUM
> FROM T1, T3
> WHERE T1.ID = T3.ID) A ON B.ID = A.ID
>
> I guess I missed the point of the statement in the tutorial.
> Thanks for the clarification,
> Geoff
>
> "Jerry Spivey" wrote:
>
Here are 3 tables.
T1
--
ID VAL
A 0
B 0
C 0
D 0
E 0
T2
--
ID METER
A 1
X 1
B 1
Y 1
T3
--
ID NUM
B 2
H 2
R 2
J 2
K 2
X 2
E 2
I was reading a tutorial that said you can join the three tables in these
ways:
T1 joins T2 which joins T3
T1 joins T2 and the Join of T1 and T2 joins T3
T1 joins T2 and T1 joins T3
The tutorial doesn't give any examples.
Which SQL statements would represent these three types of multiple table
joins?
GeoffGeoff,
T1 JOIN T2
ON T1.ID = T2.ID
JOIN T3
ON T2.ID = T3.ID
However note that the order in which the tables are joined will be
determined by SQL Server not the order in which the tables are listed in the
FROM clause unless the the SET FORCEPLAN statement is used.
HTH
J
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:3FA2D589-6EBF-414E-A4F9-FA37E41C0102@.microsoft.com...
>3 Table join question.
> Here are 3 tables.
>
> T1
> --
> ID VAL
> A 0
> B 0
> C 0
> D 0
> E 0
>
> T2
> --
> ID METER
> A 1
> X 1
> B 1
> Y 1
>
> T3
> --
> ID NUM
> B 2
> H 2
> R 2
> J 2
> K 2
> X 2
> E 2
>
> I was reading a tutorial that said you can join the three tables in these
> ways:
> T1 joins T2 which joins T3
> T1 joins T2 and the Join of T1 and T2 joins T3
> T1 joins T2 and T1 joins T3
> The tutorial doesn't give any examples.
> Which SQL statements would represent these three types of multiple table
> joins?
> Geoff|||I suppose your answer is an example of (1)?
(1) T1 joins T2 which joins T3
(2) T1 joins T2 and the Join of T1 and T2 joins T3
(3) T1 joins T2 and T1 joins T3
Geoff
"Jerry Spivey" wrote:
> Geoff,
> T1 JOIN T2
> ON T1.ID = T2.ID
> JOIN T3
> ON T2.ID = T3.ID
> However note that the order in which the tables are joined will be
> determined by SQL Server not the order in which the tables are listed in t
he
> FROM clause unless the the SET FORCEPLAN statement is used.
> HTH
> J
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:3FA2D589-6EBF-414E-A4F9-FA37E41C0102@.microsoft.com...
>
>|||Probably. Depends on how the optimizer chooses to join the tables. It may
join 1 to 2 and then that intermediate result set to 3 or 3 to 2 and than
intermediate result set to 1.
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:1181B891-8E78-4A9E-8CBA-A6E020A31A19@.microsoft.com...
>I suppose your answer is an example of (1)?
> (1) T1 joins T2 which joins T3
> (2) T1 joins T2 and the Join of T1 and T2 joins T3
> (3) T1 joins T2 and T1 joins T3
> Geoff
> "Jerry Spivey" wrote:
>|||O.K. I get it. Those three "ways" to join are the ways the RDBMS may have
implemented the JOIN for three tables. The results from each of the three
"ways" are the same.
I read the statement as "These are the three ways to join tables" and you
pick the type of join you want.
I was playing with this as an example of the (3)rd way
SELECT *
FROM (SELECT T1.ID, T1.VAL, T2.METER
FROM T1, T2
WHERE T1.ID = T2.ID) B INNER JOIN
(SELECT T3.ID, T3.NUM
FROM T1, T3
WHERE T1.ID = T3.ID) A ON B.ID = A.ID
I guess I missed the point of the statement in the tutorial.
Thanks for the clarification,
Geoff
"Jerry Spivey" wrote:
> Probably. Depends on how the optimizer chooses to join the tables. It ma
y
> join 1 to 2 and then that intermediate result set to 3 or 3 to 2 and than
> intermediate result set to 1.
>
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:1181B891-8E78-4A9E-8CBA-A6E020A31A19@.microsoft.com...
>
>|||An example of (2) is
(T1 JOIN T2 ON T1.ID = T2.ID) JOIN T3 ON T2.ID = T3.ID
By using parenthesis, you can force the order in which the tables are
joined, creating a scenario in which T1 joins T2 and T3 joins the join of T1
and T2.
An exapmle of (3) is
T1
JOIN T2 ON T1.ID = T2.ID
JOIN T3 ON T1.ID = T3.ID
I think that (1) and (3) are actually just different orders of the same JOIN
statement.
Chris
"Geoff" wrote:
> O.K. I get it. Those three "ways" to join are the ways the RDBMS may have
> implemented the JOIN for three tables. The results from each of the three
> "ways" are the same.
> I read the statement as "These are the three ways to join tables" and you
> pick the type of join you want.
> I was playing with this as an example of the (3)rd way
> SELECT *
> FROM (SELECT T1.ID, T1.VAL, T2.METER
> FROM T1, T2
> WHERE T1.ID = T2.ID) B INNER JOIN
> (SELECT T3.ID, T3.NUM
> FROM T1, T3
> WHERE T1.ID = T3.ID) A ON B.ID = A.ID
>
> I guess I missed the point of the statement in the tutorial.
> Thanks for the clarification,
> Geoff
>
> "Jerry Spivey" wrote:
>
3 Table Join
I need to perform a 3 table join but my sql is a little rusty and I was never that good at Joins anyway!
I'll try and explain the basic DB layout. It's for a very simple forum board I'm making, I have 3 tables at the moment, one contains the messages which belong to a topics, which have their own table. And the topics all belong to a table listing all the forums.
tblThread
threadID PK
topicID FK
Author
Message
MessageDate
tblTopic
topicID PK
forumID FK
Subject
numViews
tblForum
forumID PK
forumName
forumDescription
forumOwner
What I'm trying to achieve is to get the number threads belonging to a forum.
So far my attempt at an sql query looks like this:
SELECT COUNT('tblthread.threadID') AS postsCount
FROM tblThread
INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1
but that's missing an operator somewhere. Anyone know where i'm going wrong?
Thanks,
RichardSELECT COUNT('tblthread.threadID') AS postsCount
There shouldn't be apostrophes around the column name.|||your statement seems be fine. what database server do you use? there might be some syntax problem (INNER JOIN or something) try this one, but basically that's the same:
SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1|||There shouldn't be apostrophes around the column name.
actually this shouldn't be issue. you can put whatever as count() argument. it can be column name, *, or some constant such as 1 or 'XXX'. in this case it's constant string which shouldn't affect result.|||True, I stand corrected.|||Hi Guys,
Thanks for the help. I'm using MS Access as the DB. I tried your suggestion madafaka but I'm still getting the same error:
"Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause."
Is this an error specific to access?
Thanks.|||daffy_dowden,
I created all 3 tables in MS Access (I think it's 2000 version)
I created Query (Create Query in Design view) using (SQL View)
simply: pasted code posted before
your code returned exactly the same error as you described. I don't know why, but what you can expect from MS Access :-)
Then I used (Design view) and it generated this code, which works fine:
SELECT count(*)
FROM tblForum
INNER JOIN (tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID) ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1;
code I posted before worked fine, so I don't know why you received an error
SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1;|||I guess Access joins tables step by step : tblThread join tblTopic, and then join tblForum
SELECT COUNT('tblthread.threadID') as postsCount
FROM
(tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID)
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1
I'll try and explain the basic DB layout. It's for a very simple forum board I'm making, I have 3 tables at the moment, one contains the messages which belong to a topics, which have their own table. And the topics all belong to a table listing all the forums.
tblThread
threadID PK
topicID FK
Author
Message
MessageDate
tblTopic
topicID PK
forumID FK
Subject
numViews
tblForum
forumID PK
forumName
forumDescription
forumOwner
What I'm trying to achieve is to get the number threads belonging to a forum.
So far my attempt at an sql query looks like this:
SELECT COUNT('tblthread.threadID') AS postsCount
FROM tblThread
INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1
but that's missing an operator somewhere. Anyone know where i'm going wrong?
Thanks,
RichardSELECT COUNT('tblthread.threadID') AS postsCount
There shouldn't be apostrophes around the column name.|||your statement seems be fine. what database server do you use? there might be some syntax problem (INNER JOIN or something) try this one, but basically that's the same:
SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1|||There shouldn't be apostrophes around the column name.
actually this shouldn't be issue. you can put whatever as count() argument. it can be column name, *, or some constant such as 1 or 'XXX'. in this case it's constant string which shouldn't affect result.|||True, I stand corrected.|||Hi Guys,
Thanks for the help. I'm using MS Access as the DB. I tried your suggestion madafaka but I'm still getting the same error:
"Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause."
Is this an error specific to access?
Thanks.|||daffy_dowden,
I created all 3 tables in MS Access (I think it's 2000 version)
I created Query (Create Query in Design view) using (SQL View)
simply: pasted code posted before
your code returned exactly the same error as you described. I don't know why, but what you can expect from MS Access :-)
Then I used (Design view) and it generated this code, which works fine:
SELECT count(*)
FROM tblForum
INNER JOIN (tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID) ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1;
code I posted before worked fine, so I don't know why you received an error
SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1;|||I guess Access joins tables step by step : tblThread join tblTopic, and then join tblForum
SELECT COUNT('tblthread.threadID') as postsCount
FROM
(tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID)
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1
3 table join
Does anyone know how to construct a select statement for a
3 table join ?. I want a left join with the table1 and
table2, then I want an inner join with table2 and table3.
Can this be done ? If so how can a Select statement would
be?
Thanks for any help.
Mike.
There is a 3 table join example in BOL [Index: SELECT, examples (example
B)]. There are also a number of outer join examples [Index: outer joins,
using outer joins]. Why not take a chance and try it yourself?
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.
|||When joining two tables you will have one JOIN and (at least) one ON.
When joining three tables you will have two JOINs and (at least) two ONs.
You will want to join on your primary/foreign key(s).
Give it a shot. post back to the newsgroups if you get stuck.
Keith
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.
3 table join ?. I want a left join with the table1 and
table2, then I want an inner join with table2 and table3.
Can this be done ? If so how can a Select statement would
be?
Thanks for any help.
Mike.
There is a 3 table join example in BOL [Index: SELECT, examples (example
B)]. There are also a number of outer join examples [Index: outer joins,
using outer joins]. Why not take a chance and try it yourself?
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.
|||When joining two tables you will have one JOIN and (at least) one ON.
When joining three tables you will have two JOINs and (at least) two ONs.
You will want to join on your primary/foreign key(s).
Give it a shot. post back to the newsgroups if you get stuck.
Keith
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.
3 table join
Does anyone know how to construct a select statement for a
3 table join ?. I want a left join with the table1 and
table2, then I want an inner join with table2 and table3.
Can this be done ? If so how can a Select statement would
be?
Thanks for any help.
Mike.There is a 3 table join example in BOL [Index: SELECT, examples (exampl
e
B)]. There are also a number of outer join examples [Index: outer join
s,
using outer joins]. Why not take a chance and try it yourself?
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.|||When joining two tables you will have one JOIN and (at least) one ON.
When joining three tables you will have two JOINs and (at least) two ONs.
You will want to join on your primary/foreign key(s).
Give it a shot. post back to the newsgroups if you get stuck.
Keith
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.sql
3 table join ?. I want a left join with the table1 and
table2, then I want an inner join with table2 and table3.
Can this be done ? If so how can a Select statement would
be?
Thanks for any help.
Mike.There is a 3 table join example in BOL [Index: SELECT, examples (exampl
e
B)]. There are also a number of outer join examples [Index: outer join
s,
using outer joins]. Why not take a chance and try it yourself?
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.|||When joining two tables you will have one JOIN and (at least) one ON.
When joining three tables you will have two JOINs and (at least) two ONs.
You will want to join on your primary/foreign key(s).
Give it a shot. post back to the newsgroups if you get stuck.
Keith
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.sql
3 table join
Does anyone know how to construct a select statement for a
3 table join ?. I want a left join with the table1 and
table2, then I want an inner join with table2 and table3.
Can this be done ? If so how can a Select statement would
be?
Thanks for any help.
Mike.There is a 3 table join example in BOL [Index: SELECT, examples (example
B)]. There are also a number of outer join examples [Index: outer joins,
using outer joins]. Why not take a chance and try it yourself?
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.|||When joining two tables you will have one JOIN and (at least) one ON.
When joining three tables you will have two JOINs and (at least) two ONs.
You will want to join on your primary/foreign key(s).
Give it a shot. post back to the newsgroups if you get stuck.
--
Keith
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.
3 table join ?. I want a left join with the table1 and
table2, then I want an inner join with table2 and table3.
Can this be done ? If so how can a Select statement would
be?
Thanks for any help.
Mike.There is a 3 table join example in BOL [Index: SELECT, examples (example
B)]. There are also a number of outer join examples [Index: outer joins,
using outer joins]. Why not take a chance and try it yourself?
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.|||When joining two tables you will have one JOIN and (at least) one ON.
When joining three tables you will have two JOINs and (at least) two ONs.
You will want to join on your primary/foreign key(s).
Give it a shot. post back to the newsgroups if you get stuck.
--
Keith
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1f8701c50885$7d35d640$a501280a@.phx.gbl...
> Does anyone know how to construct a select statement for a
> 3 table join ?. I want a left join with the table1 and
> table2, then I want an inner join with table2 and table3.
> Can this be done ? If so how can a Select statement would
> be?
> Thanks for any help.
> Mike.
3 small table database. Query: Return All Suppliers, Offering All Product, Excep
I have broken my question down into simpler terms using only 3 small tables, but the idea is the same. Well let me get to the problem (=.
Pretend we have just a small
database with 3 tables (Tb_Supplier, Tb_Product,
Tb_Offers)
Our problem is as follows:
Write an SQL statement which returns ALL Supplier Names who Offer ALL Products EXCEPT computers, cars, and tvs.
Does anyone have any advice how this might be accomplished? Here is our tables..and what I have tried/thought of so far.
CREATE TABLE Tb_Supplier (
Supp_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)
CREATE TABLE Tb_Product (
Prod_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)
CREATE TABLE Tb_Offers (
Supp_ID [bigint] REFERENCES Tb_Supplier(Supp_ID) ,
Prod_ID [bigint] REFERENCES Tb_Product(Prod_ID) ,
Quantity [decimal](18, 0) NULL ,
Price [money] NULL
)
The query I'm trying to solve is to return ALL
supplier names, who "offer" ALL products, EXCEPT cars, computers, and tvs. **Without creating any new tables ora dding columns.
Here is what I have tried/ my thoughts. I first tried breaking it
into parts and seeing if I could solve them. For instance, I wanted
to return all suppliers NOT offering computers, cars, or tvs. I
accomplished that with the following query.
SELECT Name
FROM Tb_Supplier
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))
(also wrote it using the NOT IN statement)
SELECT Name
FROM Tb_Supplier
WHERE Supp_ID NOT IN
(SELECT DISTINCT Supp_ID
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))
I don't know how to verify though that the suppliers have offered ALL products except those listed (computers, cars, tvs)
The only 3 tables that matter for this query are the Supplier, Product, and Offers Table. Here is what I need(in a small example).
Lets say we have 4 Suppliers. (Supp_ID's 111, 222, 333, and 444) (Names: Rick, Matt, Kelly, Steve) respectively
And we have 6 Products. (Prod_Id's 10, 11, 12, 13, 14, 15) (Names: cars, computers, tvs, soda, furniture, jewelry)
Our Offers Table contains the following
Supp_ID Prod_ID
111 10
111 13
222 11
222 13
222 14
333 13
333 14
333 15
444 14
444 15
I need to write a query which would return just those suppliers who are exactly like the Supplier (333). He offers ALL the products EXCEPT the computers, cars, and tvs.
I wouldn't want number 444, even though he doesn't offer computers, cars, or tvs...he still fails to offer all the other products by not offering #13 which is soda
I hope I am explaining this well. Any reply is greatly appreciated. Thanks!
(Oh and yes this is just Microsoft SQL Syntax)Try the following:
First create a query of the products you want to show.
SELECT Prod_ID, [Name]
FROM Tb_Products
WHERE Prod_ID NOT IN(10, 11, 12 [List of Prod_IDs not to include])
Once this query is created, create the new query using this query instead of Tb_Products|||this is a most interesting problem
let's assume that the primary key of Tb_Offers is (Supp_ID, Prod_ID)
in other words, a given supplier can offer a given product only once
(this is important because we'll be counting rows without using DISTINCT)
the number of products each supplier supplies is given by --
select Supp_ID, count(*)
from Tb_Offers
group by Supp_ID
the total number of products is --
select count(*) from Tb_Products
the suppliers which supply all products are --
select Supp_ID
from Tb_Offers
group by Supp_ID
having count(*) =
( select count(*) from Tb_Products )
now for the tricky part, excluding three certain products
first, let's figure out which product IDs they have --
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
now if a supplier supplies one of these three "excluded" products,
let's count a 1, and for any of the other products, let's count a 0 --
select Supp_ID
, sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
) then 1 else 0 end
) as excluded_product_count
from Tb_Offers
group by Supp_ID
notice how the subquery inside the CASE is not correlated,
which means that it will be extremely efficient
the suppliers we want are those with an "excluded product count" of 0
furthermore, the count of all the products they do supply has to be
3 less than the total number of products
so here is the final query --
select Supp_ID
from Tb_Offers
group by Supp_ID
having sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer' ,'car', 'tv')
) then 1 else 0 end
) = 0
and count(*) =
( select count(*) from Tb_Products ) - 3
i'm fairly confident in this, but have not tested it
please let me know how it works for you
rudy
http://r937.com/
http://rudy.ca/|||CreativeSoul,
What you are after is called relational division.
Celko has an article that you will find very helpful...
http://www.dbazine.com/celko1.html
Please read this first and then look at this query...There are several approaches to achieving this in SQL...but this is usually the easiest to understand....
SELECT S.Supp_ID
FROM Tb_Supplier S
INNER JOIN Tb_Offers O on O.SUPP_ID = S.SUPP_ID
WHERE O.Prod_ID NOT IN(10,11,12)
GROUP BY S.Supp_ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Tb_Product WHERE Prod_ID NOT IN(10,11,12))
Pretend we have just a small
database with 3 tables (Tb_Supplier, Tb_Product,
Tb_Offers)
Our problem is as follows:
Write an SQL statement which returns ALL Supplier Names who Offer ALL Products EXCEPT computers, cars, and tvs.
Does anyone have any advice how this might be accomplished? Here is our tables..and what I have tried/thought of so far.
CREATE TABLE Tb_Supplier (
Supp_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)
CREATE TABLE Tb_Product (
Prod_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)
CREATE TABLE Tb_Offers (
Supp_ID [bigint] REFERENCES Tb_Supplier(Supp_ID) ,
Prod_ID [bigint] REFERENCES Tb_Product(Prod_ID) ,
Quantity [decimal](18, 0) NULL ,
Price [money] NULL
)
The query I'm trying to solve is to return ALL
supplier names, who "offer" ALL products, EXCEPT cars, computers, and tvs. **Without creating any new tables ora dding columns.
Here is what I have tried/ my thoughts. I first tried breaking it
into parts and seeing if I could solve them. For instance, I wanted
to return all suppliers NOT offering computers, cars, or tvs. I
accomplished that with the following query.
SELECT Name
FROM Tb_Supplier
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))
(also wrote it using the NOT IN statement)
SELECT Name
FROM Tb_Supplier
WHERE Supp_ID NOT IN
(SELECT DISTINCT Supp_ID
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))
I don't know how to verify though that the suppliers have offered ALL products except those listed (computers, cars, tvs)
The only 3 tables that matter for this query are the Supplier, Product, and Offers Table. Here is what I need(in a small example).
Lets say we have 4 Suppliers. (Supp_ID's 111, 222, 333, and 444) (Names: Rick, Matt, Kelly, Steve) respectively
And we have 6 Products. (Prod_Id's 10, 11, 12, 13, 14, 15) (Names: cars, computers, tvs, soda, furniture, jewelry)
Our Offers Table contains the following
Supp_ID Prod_ID
111 10
111 13
222 11
222 13
222 14
333 13
333 14
333 15
444 14
444 15
I need to write a query which would return just those suppliers who are exactly like the Supplier (333). He offers ALL the products EXCEPT the computers, cars, and tvs.
I wouldn't want number 444, even though he doesn't offer computers, cars, or tvs...he still fails to offer all the other products by not offering #13 which is soda
I hope I am explaining this well. Any reply is greatly appreciated. Thanks!
(Oh and yes this is just Microsoft SQL Syntax)Try the following:
First create a query of the products you want to show.
SELECT Prod_ID, [Name]
FROM Tb_Products
WHERE Prod_ID NOT IN(10, 11, 12 [List of Prod_IDs not to include])
Once this query is created, create the new query using this query instead of Tb_Products|||this is a most interesting problem
let's assume that the primary key of Tb_Offers is (Supp_ID, Prod_ID)
in other words, a given supplier can offer a given product only once
(this is important because we'll be counting rows without using DISTINCT)
the number of products each supplier supplies is given by --
select Supp_ID, count(*)
from Tb_Offers
group by Supp_ID
the total number of products is --
select count(*) from Tb_Products
the suppliers which supply all products are --
select Supp_ID
from Tb_Offers
group by Supp_ID
having count(*) =
( select count(*) from Tb_Products )
now for the tricky part, excluding three certain products
first, let's figure out which product IDs they have --
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
now if a supplier supplies one of these three "excluded" products,
let's count a 1, and for any of the other products, let's count a 0 --
select Supp_ID
, sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
) then 1 else 0 end
) as excluded_product_count
from Tb_Offers
group by Supp_ID
notice how the subquery inside the CASE is not correlated,
which means that it will be extremely efficient
the suppliers we want are those with an "excluded product count" of 0
furthermore, the count of all the products they do supply has to be
3 less than the total number of products
so here is the final query --
select Supp_ID
from Tb_Offers
group by Supp_ID
having sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer' ,'car', 'tv')
) then 1 else 0 end
) = 0
and count(*) =
( select count(*) from Tb_Products ) - 3
i'm fairly confident in this, but have not tested it
please let me know how it works for you
rudy
http://r937.com/
http://rudy.ca/|||CreativeSoul,
What you are after is called relational division.
Celko has an article that you will find very helpful...
http://www.dbazine.com/celko1.html
Please read this first and then look at this query...There are several approaches to achieving this in SQL...but this is usually the easiest to understand....
SELECT S.Supp_ID
FROM Tb_Supplier S
INNER JOIN Tb_Offers O on O.SUPP_ID = S.SUPP_ID
WHERE O.Prod_ID NOT IN(10,11,12)
GROUP BY S.Supp_ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Tb_Product WHERE Prod_ID NOT IN(10,11,12))
3 quick queries
I want help with a couple of SQL queries.
I have two Tables Table A and Table B.
Both tables have the same two fields Name and Hobbies.
One Name can appear beside multiple hobbies in each table.
I have two Tables Table A and Table B.
Both tables have the same two fields Name and Hobbies.
One Name can appear beside multiple hobbies in each table.
There are three queries I wish to run.
1) Find all the Hobbie and Name combinations in Table B not in Table A
only for Names that exist in Table A
2) Find all Hobbies and Name combinations in Table A not in Table B
3) Return all data in Table B that contains a Name that exists in Table
A
Regards,
CiarnLooks like homework to me. Have you looked up some example queries and
tried to adapt them to your application? What have you tried so far?
--
David Portas
SQL Server MVP
--|||No its not homework, what I need to do is slightly different, but
thought if I got the answers to these three I'd be able to figure it
out.
I've been playing with inner and outer joins, but can't figure out what
I need to do.
Regards,
Ciarn
BTW what does MVP stand for?|||On 4 Apr 2005 08:48:30 -0700, chudson007@.hotmail.com wrote:
>I want help with a couple of SQL queries.
>I have two Tables Table A and Table B.
>Both tables have the same two fields Name and Hobbies.
>One Name can appear beside multiple hobbies in each table.
>There are three queries I wish to run.
>1) Find all the Hobbie and Name combinations in Table B not in Table A
>only for Names that exist in Table A
>2) Find all Hobbies and Name combinations in Table A not in Table B
>3) Return all data in Table B that contains a Name that exists in Table
>A
>Regards,
>Ciarn
Hi Ciarn,
I guess the real question is why you store the same data in two tables.
Try if the following work. If not, then post table structure and sample
data as described here: www.aspfaq.com/5006.
1)
SELECT b.Hobbie, b.Name
FROM TableB AS b
WHERE EXISTS (SELECT *
FROM TableA AS a
WHERE a.Name = b.Name)
2)
SELECT a.Hobbie, a.Name
FROM TableA AS a
WHERE NOT EXISTS (SELECT *
FROM TableB AS b
WHERE b.Name = a.Name
AND b.Hobbie = a.Hobbie)
3) Same as 1.
Notes:
* 1 and 3 can also be done with an inner join, but you need some way to
prevent duplicates - either DISTINCT or a derived table
* 2 can also be done with an outer join.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||1 and 2:
SELECT A.name, A.hobby, B.name, B.hobby
FROM TableA AS A
FULL JOIN TableB AS B
ON A.name = B.name
AND A.hobby = B.hobby
WHERE A.name IS NULL
OR B.name IS NULL
3.
SELECT DISTINCT B.name, B.hobby
FROM TableA AS A
JOIN TableB AS B
ON A.name = B.name
Info on the Most Valuable Professional programme:
http://mvp.support.microsoft.com/
--
David Portas
SQL Server MVP
--|||I've tried those queries, but cannot get what I want.
Below are tables like the Tables I am using.
For Query1 I want to return
NameHobby
PhilBoxing
AndyBoxing
CiaranBoxing
For Query2 I want to return
NameHobby
PhilAthletics
AndyRugby
CiaranFootball
For Query3 I want to return
NameHobby
PhilFootball
PhilRugby
PhilAthletics
AndyFootball
AndyRugby
AndyAthletics
CiaranFootball
CiaranRugby
CiaranAthletics
TableA
NameHobby
PhilFootball
PhilRugby
AndyAthletics
AndyFootball
CiaranRugby
CiaranAthletics
PhilBoxing
AndyBoxing
CiaranBoxing
TableB
NameHobby
PhilFootball
PhilRugby
PhilAthletics
AndyFootball
AndyRugby
AndyAthletics
CiaranFootball
CiaranRugby
CiaranAthletics
MarkFootball
MarkRugby
MarkAthletics
Regards,
Ciarn|||I've tried those queries, but cannot get what I want.
Below are tables like the Tables I am using.
For Query1 I want to return
Name Hobby
Phil Boxing
Andy Boxing
Ciaran Boxing
For Query2 I want to return
Name Hobby
Phil Athletics
Andy Rugby
Ciaran Football
For Query3 I want to return
Name Hobby
Phil Football
Phil Rugby
Phil Athletics
Andy Football
Andy Rugby
Andy Athletics
Ciaran Football
Ciaran Rugby
Ciaran Athletics
TableA
Name Hobby
Phil Football
Phil Rugby
Andy Athletics
Andy Football
Ciaran Rugby
Ciaran Athletics
Phil Boxing
Andy Boxing
Ciaran Boxing
TableB
Name Hobby
Phil Football
Phil Rugby
Phil Athletics
Andy Football
Andy Rugby
Andy Athletics
Ciaran Football
Ciaran Rugby
Ciaran Athletics
Mark Football
Mark Rugby
Mark Athletics
Regards,
Ciarn|||I think you swapped the first two around from your original but thanks
for the examples. Try:
SELECT B.name, B.hobby
FROM TableB AS B
LEFT JOIN TableA AS A
ON A.name = B.name
AND A.hobby = B.hobby
WHERE A.hobby IS NULL
AND EXISTS
(SELECT *
FROM TableA
WHERE name = B.name)
SELECT A.name, A.hobby
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.name = B.name
AND A.hobby = B.hobby
WHERE B.hobby IS NULL
My third query seems to produce the result you asked for (in a
different order maybe but you just need to add an ORDER BY clause if a
specific order is important). Try it again and let me know. Here's your
sample in code so that you can easily reproduce it. It's always best to
post your table structures and sample data as code so that others can
understand what your tables look like and test out possible solutions:
CREATE TABLE TableA (name VARCHAR(20) NOT NULL, hobby VARCHAR(20) NOT
NULL, PRIMARY KEY (name,hobby))
CREATE TABLE TableB (name VARCHAR(20) NOT NULL, hobby VARCHAR(20) NOT
NULL, PRIMARY KEY (name,hobby))
INSERT INTO TableA (name, hobby)
SELECT 'Phil', 'Football' UNION ALL
SELECT 'Phil', 'Rugby' UNION ALL
SELECT 'Andy', 'Athletics' UNION ALL
SELECT 'Andy', 'Football' UNION ALL
SELECT 'Ciaran', 'Rugby' UNION ALL
SELECT 'Ciaran', 'Athletics' UNION ALL
SELECT 'Phil', 'Boxing' UNION ALL
SELECT 'Andy', 'Boxing' UNION ALL
SELECT 'Ciaran', 'Boxing'
INSERT INTO TableB (name, hobby)
SELECT 'Phil', 'Football' UNION ALL
SELECT 'Phil', 'Rugby' UNION ALL
SELECT 'Phil', 'Athletics' UNION ALL
SELECT 'Andy', 'Football' UNION ALL
SELECT 'Andy', 'Rugby' UNION ALL
SELECT 'Andy', 'Athletics' UNION ALL
SELECT 'Ciaran', 'Football' UNION ALL
SELECT 'Ciaran', 'Rugby' UNION ALL
SELECT 'Ciaran', 'Athletics' UNION ALL
SELECT 'Mark', 'Football' UNION ALL
SELECT 'Mark', 'Rugby' UNION ALL
SELECT 'Mark', 'Athletics'
Hope this helps.
--
David Portas
SQL Server MVP
--|||Absolutely perfect.
Very much appreciated.
Thanks,
Ciarn
Subscribe to:
Posts (Atom)