Please help-- query problem

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

September 20th, 2004, 01:03 AM

killer119

Registered User

Join Date: May 2004

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

Please help-- query problem

I have a query like:
SELECT DISTINCT Users.Users_ID, Users.Users_Name Category.Category_Name
FROM Users, Category
WHERE Users.Users_Category_Id=Category.Category_ID

The problem is, It selects alright, but the tables are like
Users:

Code:

Users_ID | Users_Name | Users_Category_Id
      1           hhh                   4
       2          fff                     2
      3          fsdf                <NULL>
      4          dde                <NULL>
      5          ffdf                    1

Category:

Code:

Category_ID | Category_Name
      1                 hhhh
      2                 wesd
      3                 dsfds
      4                 sdfsdf
      5                 dfgdf

There is no <NULL> Categry_ID in category
this will not returns teh 3rd ard the 4th row..

if I try
SELECT DISTINCT Users.Users_ID, Users.Users_Name Category.Category_Name
FROM Users, Category
WHERE (Users.Users_Category_Id=Category.Category_ID or Users.Users_Category_Id=null)
It will return all the category values for each user. Not distinct.
Please help

September 20th, 2004, 01:09 AM

SergeWiz

http://www.softdevelop.ru

Join Date: Sep 2004

Posts: 298

Time spent in forums: 9 h 40 m 53 sec
Reputation Power: 4

SELECT DISTINCT Users.Users_ID, Users.Users_Name Category.Category_Name
FROM Users left outer join Category on Users.Users_Category_Id=Category.Category_ID

September 20th, 2004, 01:25 AM

killer119

Registered User

Join Date: May 2004

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

Thank You !!!

September 20th, 2004, 01:38 AM

killer119

Registered User

Join Date: May 2004

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

oops..
There has been a change in the users table

Users:

Code:

Users_ID | Users_Name | Users_Source_Id | Users_Special_Id
      1           hhh                   4                     2
       2          fff                     2                   <NULL>
      3          fsdf                <NULL>                   3
      4          dde                <NULL>                <NULL>
      5          ffdf                    1                    <NULL>

Source:

Code:

Source_ID | Source_Category_Id |Source_Name
      1                     3                      ljlkj
       2                     4                      sdfds
       3                     3                     sdfgsd
       4                     3                      sdfsdf

Category:

Code:

Category_ID | Category_Name
      1                 hhhh
      2                 wesd
      3                 dsfds
      4                 sdfsdf
      5                 dfgdf

Special:

Code:

Special_Id  |  Special_Name
      1                lklj
       2              sdfsd
         3               dsfgd

Now I need To write
SELECT DISTINCT Users.Users_ID, Users.Users_Name Category.Category_Name, Special.Special_Name
FROM Users, Category, Source, Special
WHERE Users.Users_Source_Id=Source.Source_Category_ID
AND Source.Source_Category_ID=Category.Category_ID
AND Users.Users_Special_Id=Special.Special_Id

Here there is the intermediate table 'source'
and the usual 'special' table
again there is the same problem

of the query returning all the values in category..
Call me a novice but this looks tough..

Help!

September 20th, 2004, 01:54 AM

killer119

Registered User

Join Date: May 2004

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

Thank you I got it..
I had to use two more left outer join's...
THANX.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: