complicated select statement atleast for me

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

complicated select statement atleast for me

Author

Topic

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-14 : 20:38:27

Hi Guys,
Please help me.I have 3 tables and i want info from these three tables like one has company name, second has individual name(not a company)third has company id and i need output like
Advertiser count
A(company name) 12
B(company name) 5
C(Individual Name) 2
My query is not giving me the right output.I really don't know what am i doing wrong? My guess is that something is wrong with my where clause.Right now i am not worried about count but what is important is that it should give me (company name or/individual name or both) individual name. Here is my select statement
select dt.display_name_id,gen_companies.company_name,gen_individuals.first_name from gen_companies,gen_individuals,
--select gen_individuals.first_name,dt.display_name_id from gen_individuals,
(select substring(','+ display_name_id +',', ID,
CharIndex(',',','+ display_name_id + ',', ID)-ID)As display_Name_Id from Di_Tally,di_webclick_statistics
where display_name_id!='0'AND entered_date between '2004-10-07' AND '2004-10-12'
and ID <=Len(',' + display_name_id +',')
and substring(',' + display_name_id +',', ID-1, 1)=','
and ID<=Len(',' + display_name_id +',')
AND CharIndex(',' , ',' +display_name_id + ',' , ID) - ID > 0)dt
where dt.display_name_id=gen_individuals.name_id
AND dt.display_name_id=gen_companies.name_id
Thank You in advance

SQLServerSpotlight
Yak Posting Veteran

57 Posts

Posted - 2004-10-15 : 01:04:50

the sql is complex
Can you also provide some create tables and inserts with sample data?
You can make the SQL look sql-like by wrapping [BLAH] [/BLAH] around it where BLAH = CODE
(I can't write it as its transforms it :-))

Kristen
Test

22859 Posts

Posted - 2004-10-15 : 01:26:17

Yes you can - just put:
[black][[/black]code] = [code]

And now you can guess how many BLACKs I had to use to escape the BLACKs ... <g>

There's probably a <PRE> equivalent in Forum COde too, but I dunno what it is

Kristen

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-15 : 09:00:55

Can anyone give me modified select statement or suggest me how should i modify my where clause.Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-15 : 09:46:13

I guess that since you won't fix your table structure, we can look forward to seeing lots of questions from you as you struggle to do the most basic things with your database. Eventually, you'll understand that if you clean this thing up queries like this are pretty easy.

There is a reason why about 10 of us have been suggesting that you should really fix your table structure -- have you seen why yet?

- Jeff

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-15 : 10:09:18

Jeff! This is not my database and i am not in a position to change it. I am just working on this database.Someone else is the owner of this database and he doesn't want to change it. Thanks anyway.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-15 : 10:33:56

if you really can't change it, then you should create views of all tables that are structurd in this manner that fix the design, and write your SQL using the normalized views.

so, instead of repeating the whole Tally Table / CHARINDEX(...) thing over and over, create a view of each table that you will need to query. Write the view in a generic manner so that it returns what information you need. Then write your queries off of the views at all times, ignoring the true (ugly) table structure. (and direct the person in charge to some books on database design and/or here at SQLteam)

for example -- if your table has columns like this:

TableX: SomeID, SomeOtherColumn, ListOfValues

and "ListOfValues" has values like this (as you've indicated) :

1248 9437 7834 4925
3943
4857 2943 2394
2394 4723

then you should write a view of this table (and you know how to do it from the help you've been given) that normalizes this table and returns:

SomeID, SomeOtherColumn, Value

where in this case "Value" is the single value from the table, 1 per row. Save that SQL as a view of TableX, and write your queries off of that view. Don't repeat all that code that joins to the tally table and parses it all over and over -- do it once.

Does this make sense/ help ? Doing this is the first step before writing ANY further queries in this database.

- Jeff

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-15 : 11:39:18

Thanks Jeff! Let me try it.I appreciate your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources