|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
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? |
|
#2
|
||||
|
||||
|
Post your table names and table Structure (column names and data types)
|
![]() |
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > select statement help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|