January 24, 2010 at 1:28 am
Ok, I've been working on this for several hours and it's driving me crazy.
First of all, here is my code:
SELECT SNP_crs.crs_id AS id, crs_name, crs_author, sch_start, sch_end, intro, recorded, edit_av, time,
sd_transc, md_bounced FROM SNP_media
INNER JOIN SNP_crsunits
ON SNP_media.crsunit_id =
SNP_crsunits.crsunit_id
INNER JOIN SNP_crs
ON SNP_crsunits.crs_id =
SNP_crs.crs_id
WHERE ((intro = '0' AND recorded = '0'
AND edit_av = '0' AND sd_transc = '0'
AND md_bounced = '0') OR
(intro IS NULL AND recorded IS NULL
AND edit_av IS NULL AND sd_transc IS NULL
AND md_bounced IS NULL))
AND sch_start != '-'
So, here is the thing. I might have 9 records for the same crs_id. I want this to only return results if the intro, recorded, edit_av, sd_transc, and md_bounced are either = 0 or is null AND where sch_start is not = to -. I want this for any of the records with the same crs_id. So, an example of where I'm having an issue. If I have 9 records for the same crs_id and there is a 1 for intro for one of those records, the other 8 are still being returned, because they don't have an 1. This makes total sense; however, I can't figure out how to get it to return no results if there is a 1 for any of those fields, for any of the records with the same crs_id. I've tried a group by, but that doesn't work.
Any suggestions?
Thank you for your time,
Jordon
January 24, 2010 at 3:12 am
SELECT c.crs_id AS id,
crs_name,
crs_author,
sch_start,
sch_end,
intro,
recorded,
edit_av,
[time],
sd_transc,
md_bounced
FROM SNP_media m
INNER JOIN SNP_crsunits u
ON m.crsunit_id = u.crsunit_id
INNER JOIN SNP_crs c
ON u.crs_id = c.crs_id
WHERE ((intro = '0' AND recorded = '0'
AND edit_av = '0' AND sd_transc = '0'
AND md_bounced = '0') OR
(intro IS NULL AND recorded IS NULL
AND edit_av IS NULL AND sd_transc IS NULL
AND md_bounced IS NULL))
AND sch_start != '-'
-- Which table contains the column [intro]?
-- I've added an alias to each of the tables e.g. SNP_media m
-- can you apply the correct alias to each of the output columns?
-- the first output column is done (I would have used m personally but c is INNER JOINed)
Cheers
ChrisM@home
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 24, 2010 at 2:08 pm
That still isn't working. Here is the table structure of the three tables that I'm joining together:
/****** Object: Table [dbo].[SNP_crs] Script Date: 01/24/2010 15:05:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SNP_crs](
[crs_id] [int] IDENTITY(1,1) NOT NULL,
[crs_name] [varchar](200) NOT NULL,
[crs_author] [varchar](200) NULL,
[crs_active] [int] NOT NULL,
[sch_start] [varchar](50) NOT NULL,
[sch_end] [varchar](50) NOT NULL,
[sec_link] [text] NULL,
[crs_units] [int] NULL,
[pro_id] [int] NOT NULL,
CONSTRAINT [PK_SNP_crs] PRIMARY KEY CLUSTERED
(
[crs_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SNP_crsunits] Script Date: 01/24/2010 15:06:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SNP_crsunits](
[crsunit_id] [int] IDENTITY(1,1) NOT NULL,
[crs_id] [int] NOT NULL,
[unit] [varchar](200) NULL,
[unit_title] [varchar](200) NULL,
[edit_av] [smallint] NULL,
[sd_transc] [smallint] NULL,
[rd_transc] [smallint] NULL,
[mapped] [smallint] NULL,
[designer] [int] NULL,
[qc_check] [smallint] NULL,
[bounced] [smallint] NULL,
[published] [smallint] NULL,
[qc_check2] [smallint] NULL,
[readbx] [smallint] NULL,
[attend] [smallint] NULL,
[complete] [smallint] NULL,
[discuss] [smallint] NULL,
CONSTRAINT [PK_SNP_crsunits] PRIMARY KEY CLUSTERED
(
[crsunit_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SNP_media] Script Date: 01/24/2010 15:06:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SNP_media](
[media_id] [int] IDENTITY(1,1) NOT NULL,
[crsunit_id] [int] NOT NULL,
[intro] [smallint] NULL,
[recorded] [smallint] NULL,
[time] [varchar](200) NULL,
[md_bounced] [smallint] NULL,
CONSTRAINT [PK_SNP_media] PRIMARY KEY CLUSTERED
(
[media_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
January 24, 2010 at 2:21 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2010 at 8:15 pm
Here is sample data from the SNP_media table:
INSERT INTO SNP_media
(crsunit_id
, intro
, recorded
, [time]
, md_bounced)
VALUES
('1'
, '1'
, '1'
, '16 min'
, '1')
INSERT INTO SNP_media
(crsunit_id
, intro
, recorded
, [time]
, md_bounced)
VALUES
('2'
, '0'
, '1'
, '24 min'
, '1')
INSERT INTO SNP_media
(crsunit_id
, intro
, recorded
, [time]
, md_bounced)
VALUES
('3'
, '1'
, '1'
, '12 min'
, '1')
Here is sample data from the SNP_crs table
INSERT INTO SNP_crs
(crs_name
, crs_author
, crs_active
, sch_start
, sch_end
, crs_units
, pro_id)
VALUES
('Business Law'
, 'Harvey Slentz'
, '1'
, '1/18/2010'
, '1/19/2010'
, '8'
, '1')
Finally, sample data from the SNP_crsunits table
INSERT INTO SNP_crsunits
(crs_id
, unit
, edit_av
, sd_transc)
VALUES
('1'
, 'Introduction'
, '1'
, '1')
INSERT INTO SNP_crsunits
(crs_id
, unit
, edit_av
, sd_transc)
VALUES
('1'
, 'Unit 1'
, '1'
, '1')
INSERT INTO SNP_crsunits
(crs_id
, unit
, edit_av
, sd_transc)
VALUES
('1'
, 'Unit 2'
, '1'
, '1')
Ok, so you will notice that these are all the same course. So, for this one course, if intro, recorded, edit_av, sd_transc, and md_bounced are either all 0 or NULL and sch_start is not = to '-', meaning it actually has a date in it, then I need the name of that course, no matter how many records that course has. So, if there is a 1 in any of those fields, for any of those records for that one course name, then don't display that course. I also only need it once. Meaning, in this data that I've provided, you will notice that in the SNP_crsunits and the SNP_media, there are 3 records for the same course; however, I don't need that to result 3 times, I just need to know the name of that course once, the author of the course, and the data that course starts.
Hopefully, this gives a better example of what I'm wanting. I know that it's possible, I'm just confused on how to do it.
January 25, 2010 at 6:06 am
jordon.shaw (1/24/2010)
That still isn't working.
Nope, wasn't meant to. There are a few questions at the bottom of the code list, any chance of an answer?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2010 at 7:01 am
Chris Morris-439714 (1/25/2010)
Nope, wasn't meant to. There are a few questions at the bottom of the code list, any chance of an answer?
I had posted my table structure. I thought that answered the questions that you had. Can you see?
Thanks,
Jordon
January 25, 2010 at 7:38 am
jordon.shaw (1/25/2010)
Chris Morris-439714 (1/25/2010)
Nope, wasn't meant to. There are a few questions at the bottom of the code list, any chance of an answer?I had posted my table structure. I thought that answered the questions that you had. Can you see?
Thanks,
Jordon
Yes I can see - but like most other folks here, I'm fitting this into my lunchtime or in between tasks at work.
Which is your driving table here? Surely it should be SNP_crs, with child SNP_crsunits, and SNP_media a child of SNP_crsunits?
BTW here's table structures and sample data rejigged into a readily-consumable format:
DROP TABLE #SNP_crs
DROP TABLE #SNP_crsunits
DROP TABLE #SNP_media
CREATE TABLE #SNP_crs(
[crs_id] [int] IDENTITY(1,1) NOT NULL,
[crs_name] [varchar](200) NOT NULL,
[crs_author] [varchar](200) NULL,
[crs_active] [int] NOT NULL,
[sch_start] [varchar](50) NOT NULL,
[sch_end] [varchar](50) NOT NULL,
[sec_link] [text] NULL,
[crs_units] [int] NULL,
[pro_id] [int] NOT NULL)
--
CREATE TABLE #SNP_crsunits(
[crsunit_id] [int] IDENTITY(1,1) NOT NULL,
[crs_id] [int] NOT NULL,
[unit] [varchar](200) NULL,
[unit_title] [varchar](200) NULL,
[edit_av] [smallint] NULL,
[sd_transc] [smallint] NULL,
[rd_transc] [smallint] NULL,
[mapped] [smallint] NULL,
[designer] [int] NULL,
[qc_check] [smallint] NULL,
[bounced] [smallint] NULL,
[published] [smallint] NULL,
[qc_check2] [smallint] NULL,
[readbx] [smallint] NULL,
[attend] [smallint] NULL,
[complete] [smallint] NULL,
[discuss] [smallint] NULL)
--
CREATE TABLE #SNP_media(
[media_id] [int] IDENTITY(1,1) NOT NULL,
[crsunit_id] [int] NOT NULL,
[intro] [smallint] NULL,
[recorded] [smallint] NULL,
[time] [varchar](200) NULL,
[md_bounced] [smallint] NULL)
--
INSERT INTO #SNP_media (crsunit_id, intro, recorded, [time], md_bounced)
SELECT '1', '1', '1', '16 min', '1' UNION ALL
SELECT '2', '0', '1', '24 min', '1' UNION ALL
SELECT '3', '1', '1', '12 min', '1'
--
INSERT INTO #SNP_crs (crs_name, crs_author, crs_active, sch_start, sch_end, crs_units, pro_id)
SELECT 'Business Law', 'Harvey Slentz', '1', '1/18/2010', '1/19/2010', '8', '1'
--
INSERT INTO #SNP_crsunits (crs_id, unit, edit_av, sd_transc)
SELECT '1', 'Introduction', '1', '1' UNION ALL
SELECT '1', 'Unit 1', '1', '1' UNION ALL
SELECT '1', 'Unit 2', '1', '1'
--
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end,
m.intro,
m.recorded,
u.edit_av,
m.[time],
u.sd_transc,
m.md_bounced
FROM #SNP_media m
INNER JOIN #SNP_crsunits u
ON m.crsunit_id = u.crsunit_id
INNER JOIN #SNP_crs c
ON u.crs_id = c.crs_id
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2010 at 7:53 am
Chris,
Thank you very much for your help. I wasn't trying to tell you to hunt for the data or anything, I thought that I had posted a detailed response to your question, maybe I was too detail or maybe I didn't answer it correctly.
To give you a little more back ground, this is a application that serves as a check list on developing courses. So, we create a course shell, which inserts the course into SNP_crs table. That also creates each individual units in the SNP_crsunits and the SNP_media table. So, I need to run a query that will tell me when a course is scheduled to be recorded, meaning the date is set; however, nobody has started working on it, meaning the fields are either all null or all 0. Once somebody starts working on the course and checks that they started one of those processes, then the field will get updated to a 1 and then I need it to come off my report.
Hopefully, this helps give a better understanding of what I'm needing. Thank you very much for writing the code in a better format. I'm a PHP programmer that is trying to learn SQL.
Thank you all for your help!
Jordon
January 25, 2010 at 8:04 am
No worries mate.
Have a look at this, I reckon it's getting close:
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end,
m.intro, --
m.recorded, --
u.edit_av, --
m.[time],
u.sd_transc, --
m.md_bounced --
FROM #SNP_crs c
INNER JOIN #SNP_crsunits u ON u.crs_id = c.crs_id
INNER JOIN #SNP_media m ON m.crsunit_id = u.crsunit_id
LEFT JOIN (SELECT ud.crs_id
FROM #SNP_crsunits ud
INNER JOIN #SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
md.[time] = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1)) ex ON ex.crs_id = c.crs_id
WHERE ex.crs_id IS NULL
AND NOT c.sch_start = '-'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2010 at 8:12 am
That is very close. If I set all three units intro, recorded, edit_av, sd_transc, and md_bounced to 0, then it should return. If any of those are set to 1, then it shouldn't return. This is working perfectly. The only thing that I need know is for it to only return 1 time for my report. Currently, if all three records have those fields set to 0, it's returning all three records. If they have the same course number, then I only need it to return once. Is that possible? I tried a group by, but it doesn't seem to work, since the records doesn't match exactly.
Thanks,
Jordon
January 25, 2010 at 8:15 am
Which output columns vary within the "group by"? Have a gander at them, and decide which of your options would best suit you: MIN / MAX / AVG etc
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2010 at 8:22 am
Actually, come to think about it, at this point, non will be different. At this point, I just need to know, which course has been schedule, but hasn't been worked on. If I try to group by crs_id, it says that the other fields in the query are invalid, because they are not contained in an aggregate function or the group by clause.
January 25, 2010 at 8:30 am
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end,
MAX(m.intro) AS intro, -- not in GROUP BY, so aggregate function required
MAX(m.recorded) AS recorded, -- not in GROUP BY, so aggregate function required
MAX(u.edit_av) AS edit_av, -- not in GROUP BY, so aggregate function required
MAX(m.[time]) AS [time], -- not in GROUP BY, so aggregate function required
MAX(u.sd_transc) AS sd_transc, -- not in GROUP BY, so aggregate function required
MAX(m.md_bounced) AS md_bounced -- not in GROUP BY, so aggregate function required
FROM #SNP_crs c
INNER JOIN #SNP_crsunits u ON u.crs_id = c.crs_id
INNER JOIN #SNP_media m ON m.crsunit_id = u.crsunit_id
LEFT JOIN (SELECT ud.crs_id
FROM #SNP_crsunits ud
INNER JOIN #SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
md.[time] = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1)
) ex ON ex.crs_id = c.crs_id
WHERE ex.crs_id IS NULL
AND NOT c.sch_start = '-'
GROUP BY c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2010 at 8:34 am
That's it! You're a genus! Thank you so much for your help!!!
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply