Distinct email addresses in 2 tables with different field names

Hi everyone,
I have 2 tables with table A containing an 'email' field and table B
containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).
Thank you
Maz.Hi Maz,
Please don't post questions independently in multiple newsgroups. You
question has already been answered in .programming.
--
Jacco Schalkwijk
SQL Server MVP
"Maziar Aflatoun" <maz88@.rogers.com> wrote in message
news:STSEb.32573$2We1.12257@.news04.bloor.is.net.cable.rogers.com...
> Hi everyone,
> I have 2 tables with table A containing an 'email' field and table B
> containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it
possible
> to issue a query that would return only the unqiue email addresses in
these
> 3 fields? So a long list with no duplicate emails(distinct).
> Thank you
> Maz.
>
>

Distinct and grouping problem

can someone look at the statement for me, it's not giving me distinct names,

SELECT DISTINCT TOP 10 leaderboard.name, Last(leaderboard.date_cr) AS date_cr, Last(leaderboard.handicap) AS handicap, Max(leaderboard.total) AS total, Last(leaderboard.cpsa) AS cpsa, Last(leaderboard.rbss) AS rbss, Max(leaderboard.visible) AS visible, Last(leaderboard.score) AS score
FROM leaderboard
WHERE (((leaderboard.visible)=True) AND ((leaderboard.rbss)=True))
GROUP BY leaderboard.name, leaderboard.total
ORDER BY Max(leaderboard.total) DESC , Last(leaderboard.date_cr);

if I take the second grouping off, I get distinct names, but the information isn't correct. i.e. the date, handicap, score, is from different record than the total field.

any help much appreciated.

thanks

That is because you are grouping on more than one column. The total column will get you additional values / rows in the Grouping section.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks for your reply.

So if I take the second grouping out how can I make the information 'line up' correctly?

i.e. have the correct date, handicap, score with the right total. As at the moment they are the wrong values for the Total.

|||That is hard to tell, because you only told that the query "is not working". Depending on your business case, you might want to get the max(total) or not ? That depends on the logic you want to put in the query.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

ok, these are the rules

1. If somebody has played more than once, only the highest score as calculated in one above should be displayed
2. the results should display correct details by total score in descending order
3. In the case where there are two or more equal total scores by different competitors, these should be sorted by date in ascending order (e, i - January 31 before Feb 12)

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

displaying report parameter names and values in a textbox

I'm trying to return the parameters used in a report in the report footer. Where it is no problem getting back the values I would also like to display the actual parameter name. However I haven't found a way to do that yet.
My hunch is this needs to be done with embedded code. If anyone has got the answer to this I'd be very glad if you could share it.
thanks a lot
Axe
From http://www.developmentnow.com/g/115_2004_7_0_21_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comUse Parameters!ParamName.Label
"Axel" wrote:
> I'm trying to return the parameters used in a report in the report footer. Where it is no problem getting back the values I would also like to display the actual parameter name. However I haven't found a way to do that yet.
> My hunch is this needs to be done with embedded code. If anyone has got the answer to this I'd be very glad if you could share it.
> thanks a lot
> Axel
> From http://www.developmentnow.com/g/115_2004_7_0_21_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

display/show tablename

hi,
anybody know if it is possible to display only the table names? I would like to run a query that display the table names...
thanksTake a look at the sysobjects table in the sql books online. Here is an example of a query that will return user tables from a database:

select * from sysobjects where xtype = 'U'

Display obsolete database names in restore panel

There are still a few obsolete databases showing in the SQL restore panel in
the field "Show backups of database".
Properly when someone drop the obsolete databases without selecting "delete
backup history".
How can I get rid of the database names in the backup history ?
SQL : SQL Server 2000/SP3aHi
Do you want to delete the backup history for a specific database? Then use:
EXECUTE msdb.dbo.sp_delete_database_backuphistory N'Test'
John
"Johnny" wrote:
> There are still a few obsolete databases showing in the SQL restore panel in
> the field "Show backups of database".
> Properly when someone drop the obsolete databases without selecting "delete
> backup history".
> How can I get rid of the database names in the backup history ?
> SQL : SQL Server 2000/SP3a

Display obsolete database names in restore panel

There are still a few obsolete databases showing in the SQL restore panel in
the field "Show backups of database".
Properly when someone drop the obsolete databases without selecting "delete
backup history".
How can I get rid of the database names in the backup history ?
SQL : SQL Server 2000/SP3a
Hi
Do you want to delete the backup history for a specific database? Then use:
EXECUTE msdb.dbo.sp_delete_database_backuphistory N'Test'
John
"Johnny" wrote:

> There are still a few obsolete databases showing in the SQL restore panel in
> the field "Show backups of database".
> Properly when someone drop the obsolete databases without selecting "delete
> backup history".
> How can I get rid of the database names in the backup history ?
> SQL : SQL Server 2000/SP3a

Display obsolete database names in restore panel

There are still a few obsolete databases showing in the SQL restore panel in
the field "Show backups of database".
Properly when someone drop the obsolete databases without selecting "delete
backup history".
How can I get rid of the database names in the backup history ?
SQL : SQL Server 2000/SP3aHi
Do you want to delete the backup history for a specific database? Then use:
EXECUTE msdb.dbo.sp_delete_database_backuphistory N'Test'
John
"Johnny" wrote:

> There are still a few obsolete databases showing in the SQL restore panel
in
> the field "Show backups of database".
> Properly when someone drop the obsolete databases without selecting "delet
e
> backup history".
> How can I get rid of the database names in the backup history ?
> SQL : SQL Server 2000/SP3a

Display hirearchy data using data region

I am trying to display hirearchical data using data region. For eg. the first level will be a list of customer names. The second level will be a list of Invoices under each customer. The third level will be a list of product names that were sold under each invoice.

I used a list for the data region and in each hireachy, there is a textbox in each list to display the data. I am trying to associate each data region with a datatable (I will provide the logic to populate the datatable with appropiate data). I do not want the ReportViewer to interact directly with a database because this is a 3 tier design and I am using the ReportViewer in local mode. I couldn't find any example of these. Can anybody help? Also I would like to see an example of the .rdlc file in these situation. Thanks very much.

Kam

Take a look at this article and post any follow-up questions to this post.|||

Hi Teo,

What my situation in is that I have a nested list data region. Specifically I have two list data region, one stack on top of each other. The list data region on the bottom has a textbox and another list data region. The textbox will display a list of customer name. The list data region on top also has a textbox. This textbox will display a list of invoice number with respect to each individual customer.

I read that not only can list data region be nested, each can associate with its own data table. So in my code I have two data table, the first one contain a list of customer name and its corresponding customer ID. The second one contain a list of invoice number and its corresponding customer ID that relates to the customer ID of the first table. I have trouble making this work because for some reason the textbox on the top list data region refuses to accept fields in the invoice data table. It only accepts fields in the customer data table.

I have seen example of nested list data region but it uses SQL statement and talks directly to a database. I am using this in local mode in a 3 tier situation so I have no access to a database.

Thanks very much on your last reply and I am looking forward on this one.

Thanks. Kam

|||

Take a look at the Sales Order sample report which ships with Reporting Services. Yes, data regions can be nested and each one can be bound to its own dataset.

Currently, SSRS doesn't support joining datasets at the report level. However, if all datasets will filter on a single customer you can synchronize them by using a customer query parameter. The Sales Order report demonstrates this technique although the list and table regions are not nested (they don't have to be because the datasets are pre-filtered already). An outer list can only reference the first or last record from an inner dataset as you can see by dragging a field onto it. That's because the inner list may have many records, e.g.; one customer has many invoices.

The report will work the same way in both remote and local modes. The only difference in local mode is that you have to bind the three application datasets to the report. The article I mentioned in my first reply demonstrates how dataset binding works in local mode.

|||Thanks for the tip. I haven't have a chance to look into it yet. I will let you know when I come back to this problem. Thanks.

Display database column in alphabetical order

Hello

I know how I can display a list of names in alphebetical order on my website:

Select L as [Last Name]

From Name_CatE
Where Education = 'yes'
Order By
LName ASC

However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.

What do I need to change in the following code, to view my database table column in a-z order?

SELECT LName FROM Name_CatE
ORDER BY LName ASC

Thanks

Lynn

Are you asking how to physically sort the rows in the table in LName order? If so, you could use a clustered index. But typically this is not a good idea for fields like LName, which could have many duplicate values. Check Books Online for details about the implications of doing this, particularly on performance.

But do you really need this? Is it just a preference, or do you have a solid business requirement for this? If it's just a preference, don't do it. that's what the ORDER BY clause is for.

Don