select statment returning more than table columns

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

General SQL Server Forums

New to SQL Server Programming

select statment returning more than table columns

Author

Topic

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-11 : 05:12:40

hello all...i have a very very basic problem...i have two tables and i wanted to check if there is any records whose column with the name activity is not in the table list_of_activity...so i did the followig

select distinct * from updates_table join list_of_activities on updates_table.activity<>list_of_activities.activity

and the shocking result is that i got a number of columns 10 times the number of columns found in the updates_table table..please help

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-11 : 05:33:22

hi
can u explain a bit more with the help of some sample data...

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-11 : 05:39:19

ok i have in updates table many columns one of them is activity and i have a table called list of activities...i created a relationship between the two tables in order to insure that the value stored in activity is one of the items in the list of activities...and i wanted to make sure again that there are no values in the column activity in the updates_table outside the values in the list of activities table so i typed the select statement above and i expected to get no columns or at least a small number of columns but on the contrary i got a number of columns greater than the one i have in the updates_stable...which is not logical!!!

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-11 : 05:56:36

again confusing,

please give some sample data of ur tables , it will be more easier to solve

tanx...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-04-11 : 07:07:32

Will this get you the data you are looking for?

select distinct 
   activity
from 
   list_of_activities a
where 
   not exists 
  (
      select 
         * 
      from 
         updates_table u 
      where 
         u.activity = a.activity
   )

If you are looking for the opposite i.e., entries in updates_table that are not in the list_of_activities table, swap the list_of_activities and updates_table in the query.

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-12 : 04:54:20

Hello !!!

What I understood from your description here is that you have a table called activity which has a subset of records(or rows) from the table list_of_activities. In other words, list_of_activities is the master table and the table activity can only have equal or fewer number of records than there are in list_of_activities right ?

If I understood you correctly so far, then the following query will fetch all of the records that are NOT there on either sides -

SELECT MAX(theTableName) AS theTableName, theIdentifier
FROM
(SELECT 'ActivityTable' AS theTableName, Activity AS theIdentifier FROM Activity
UNION ALL
SELECT 'ListOfActivitiesTable' As theTablename, Activity as theIdentifier FROM ListOfActivities
)
AS d
GROUP BY theIdentifier HAVING COUNT(*) = 1

Now, if you want to see all of the records that are there ON both sides, then just change the count(*) > 1

regards,
Anil Kumar.

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-12 : 05:21:22

no actually its the complete opposite..thetable contains thousands or records with a column named activity..this activity can have one of the values found in the table called list of activities

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-12 : 15:55:39

Hello Again.

Yes, in either case also, it should fetch you the records that are there on the other side.

Try running the sql & see what happens as should bring you what you are lookng for.

regards,
Anil Kumar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:17:59

[code]select * from updates_table t where not exists (select 1 from list_of_activities where activity = t.activity)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources