April 17, 2012 at 2:13 pm
Please find script to create tables below.
With a record in [ads], of the boolean columns [iscompany], [isorg],[isprof] and [isprivate] exactly 1 of these 4 fields is set to 'true'.
now if ad.iscompany='true' then I want to select companies.id, companies.title and companies.friendlyurl
now if ad.isorg='true' then I want to select healthorganizations.id, healthorganizations.title and healthorganizations.friendlyurl
now if ad.isprof='true' then I want to select healthprof.id, healthprof.title and healthprof.friendlyurl
now if ad.isprivate='true' then I want no real values returned and just select 0, '' and ''
How would I go about this?
Here's the SQL so far:
select <id>,<title>,<friendlyurl>
from ads a
left join healthprof hp on hp.userid=a.userid
left join healthorganizations ho on ho.userid=a.userid
left join companies c on c.userid=a.userid
where ads.id=X
USE [zz]
GO
/****** Object: Table [dbo].[ads] Script Date: 04/17/2012 15:59:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ads](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](100) NOT NULL,
[friendlyurl] [nvarchar](150) NOT NULL,
[userid] [uniqueidentifier] NOT NULL,
[iscompany] [bit] NOT NULL,
[isorg] [bit] NOT NULL,
[isprof] [bit] NOT NULL,
[isprivate] [bit] NOT NULL
) ON [PRIMARY]
GO
USE [zz]
GO
/****** Object: Table [dbo].[healthorganizations] Script Date: 04/17/2012 15:55:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[healthorganizations](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [nvarchar](50) NOT NULL,
[title] [nvarchar](200) NOT NULL,
[friendlyurl] [nvarchar](200) NOT NULL
) ON [PRIMARY]
GO
USE [zz]
GO
/****** Object: Table [dbo].[healthprof] Script Date: 04/17/2012 15:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[healthprof](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [nvarchar](50) NOT NULL,
[title] [nvarchar](200) NOT NULL,
[friendlyurl] [nvarchar](200) NOT NULL
) ON [PRIMARY]
GO
USE [zz]
GO
/****** Object: Table [dbo].[companies] Script Date: 04/17/2012 15:58:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[companies](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [nvarchar](50) NOT NULL,
[title] [nvarchar](50) NOT NULL,
[friendlyurl] [nvarchar](200) NOT NULL
) ON [PRIMARY]
GO
April 17, 2012 at 2:17 pm
Nice, ddl for the tables. Now you need sample data (as a series of INSERT INTO statements) and expected results.
April 18, 2012 at 12:10 pm
Below are the insert statements and expected results.
@celko: thanks for your extensive explanation.
I didnt provide the full DDL for healthorganizations, companies etc.
Then you seem to want a non-query in which a vague, generic, magical “id” can change into a company, a health business, a health professional, a squid, an automobile or Lady Gaga. In RDBMS an identifier applies to one and only one kind of entity.
IMO it's justified to place them in separate tables since they have widely different properties.
However, even though they all are different entities, they can still place ads. An organization, company etc is OWNED by exactly 1 specific userid. Also a userid may own more entities (company, organization, etc).
Now, by inserting the userid and the type of entity as which the ad was placed, I'm able to deduce the id,title and friendlyurl of that entity by which the ad was placed.
Now, ofcourse I could just make 3 statements and just look at the isprof,iscompany and isorg fields of the ad and based on that write a join. But I need a single query so that I can pass the 3 resulting fields (id, title,friendlyurl)
into my Apache Solr index.
--AD1
INSERT INTO ads(userid,title,friendlyurl,iscompany,isorg,isprof,isprivate) VALUES ('793966E9-4CE6-406F-A6F6-6C2D0D7399FB','wheelchair','wheelchair','false','false','false','true')
--AD2
INSERT INTO ads(userid,title,friendlyurl,iscompany,isorg,isprof,isprivate) VALUES ('3A0A7D72-552F-443E-80C8-8A259C071156','car support','car-support','true','false','false','false')
--AD3
INSERT INTO ads(userid,title,friendlyurl,iscompany,isorg,isprof,isprivate) VALUES ('1380D856-9AF3-4E7F-8DE1-20E6ECE0C2AC','spare motor','spare-motor','false','true','false','false')
--COMPANY 1 (since Id is determined on row insertion, the assumption here is that company 1 has id 1 etc)
INSERT INTO companies(userid,title,friendlyurl) VALUES ('3A0A7D72-552F-443E-80C8-8A259C071156','My company','my-company')
--COMPANY 2
INSERT INTO companies(userid,title,friendlyurl) VALUES ('1380D856-9AF3-4E7F-8DE1-20E6ECE0C2AC','Company 123','company-123')
--HEALTHORG 1
INSERT INTO healthorganizations(userid,title,friendlyurl) VALUES ('3A0A7D72-552F-443E-80C8-8A259C071156','hospital X','hospital-x')
--HEALTHORG 2
INSERT INTO healthorganizations(userid,title,friendlyurl) VALUES ('1380D856-9AF3-4E7F-8DE1-20E6ECE0C2AC','Eye Clinic 4 U','eye-clinic-4-u')
--HEALTHPROF 1
INSERT INTO healthprof(userid,title,friendlyurl) VALUES ('3A0A7D72-552F-443E-80C8-8A259C071156','Psychologist 123','psychologist-123')
--HEALTHPROF 2
INSERT INTO healthprof(userid,title,friendlyurl) VALUES ('1380D856-9AF3-4E7F-8DE1-20E6ECE0C2AC','Manual Therapy','manual-therapy')
Now first the expected result for AD1. This ad has the isprivate column set to 'true' (so userid will have no match in other tables), the result would be:
id=0,title='',friendlyurl=''
The expected result for AD2. This ad has the iscompany column set to 'true', the result would be:
id=1,title='My company',friendlyurl='my-company'
The expected result for AD3. This ad has the isorg column set to 'true', the result would be:
id=2,title=''Eye Clinic 4 U',friendlyurl='eye-clinic-4-u'
I hope this is clear, if not, let me know! 🙂
Oh and I will be on a long holiday, so if I dont reply right away, you know why 🙂
Thanks!
April 20, 2012 at 5:02 pm
for once i agree with celko on the point its bad design. if you look at all your tables you have the exact same columns in each. since the columns are the same its not really normalized (the tables may be normalized but the DB is not) and go with a table as such:
CREATE TABLE [dbo].[Companies](
[id] [int] IDENTITY(1,1) NOT NULL,
[CompanyType] VARCHAR(32), --or int if you want to move the varchar name out to a dimention table
[userid] [nvarchar](50) NOT NULL,
[title] [nvarchar](200) NOT NULL,
[friendlyurl] [nvarchar](200) NOT NULL
) ON [PRIMARY]
the adds table gets the same column of CompanyType to cut out the redundant storage in the bits that are marked as a 0. im guessing only one can be set and if 2 can be set is the company in both type tables? that is not normalized and will make for a nightmare when it comes to updating things.
and if you condense your 4 Tables down to one and add a type to your adds table you get a very nice join condition of companyid = companyid with an ad number as the primary key of that table.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply