Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free ForumsDatabaseMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old December 11th, 2003, 01:16 PM
papaluco papaluco is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Posts: 1 papaluco User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
select statement help

I have a select query statement that is not producing the results i want.
I am joining a table (B) to another table (A). Table A will always have one record, but table B may have 0 or more records. If table B has at least one record, one of them will be flaged in a column (bDefault = 1) and the rest (bDefault = 0).

What i need to do is select from table A and if there are records in table B get the one with bDefault=1. This has proven to be beyond my skills. Below is the code I have tried so far. What happens when i run it is this: table B has three records for one member (6) and just one for another (3). the querry returns three rows for 6 and one for 3. How ever many records there are in table B for a given member is how many rows are returned by the query. I don't want that. i want to return only one row per member and if they have a row in table b, return only the row from table B where bDefault = 1 and if they don't have a row in table B, to ignore it.

Here is the first code I am tried:
SELECT P.nMID, P.nAge, (SELECT MI.sURLPath + 't_' + convert(varchar(10),MI.nID) + '.jpg' WHERE bDefault = 1 AND MI.nMID = P.nMID) as sURLPath
FROM tblMember_profile P
Left Outer JOIN tblMember_Images MI ON MI.nMID = p.nMID WHERE P.nMID IN ('7','6') ORDER BY P.nMID

the 2nd querry here produced the same result:
SELECT P.nMID, P.nAge, (SELECT MI.sURLPath + 't_' + convert(varchar(10),MI.nID) + '.jpg' WHERE bDefault = 1 AND MI.nMID = P.nMID) as sURLPath
FROM tblMember_profile P
JOIN tblMember_Images MI ON MI.nMID = p.nMID WHERE P.nMID IN ('7','6') ORDER BY P.nMID

and this is the result:
6 22 NULL
6 22 /images/MemImages/t_36.jpg
6 22 NULL
7 27 /images/MemImages/t_51.jpg

I tried this too:
SELECT P.nMID, P.nAge, (MI.sURLPath + 't_' + convert(varchar(10),MI.nID) + '.jpg') as sURLPath
FROM tblMember_profile P
JOIN tblMember_Images MI ON MI.nMID = P.nMID WHERE MI.bDefault = 1 AND P.nMID IN ('6','7') ORDER BY P.nMID

and it gave me the results i wanted except that if someone does not have a record in table 2, it does not display them at all.
it looks like this:
6 22 /images/MemImages/t_36.jpg
7 27 /images/MemImages/t_51.jpg


I know this is long and drawn out, but could someone please make a suggestion on how to deal with this?

Reply With Quote
  #2  
Old December 11th, 2003, 03:13 PM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
ASP Free God 14th Plane (11500 - 11999 posts)
 
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,760 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 5 h 22 m 16 sec
Reputation Power: 443
Post your table names and table Structure (column names and data types)

Reply With Quote
Reply

Viewing: ASP Free ForumsDatabaseMicrosoft SQL Server > select statement help


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway