SQL Server 2005 Query Multiple Tables all rows from one

Hello All;

OK. This is the Query.

SELECT     Zones.ZoneID, Zones.ZoneName, Lists.AskID
                      Zones ON Lists.ZoneID = Zones.ZoneID
WHERE     (Lists.AskID = 1) AND (Zones.SCID = 29)

This is an example of the data that NEEDS to be delivered to the Member.
1 - Solid -  1
2 - time -  null
3 - old -  1
4 - new -  null
5 - like -  null
6 - some -  null
7 - find -  null
8 - one -  null
9 - info -  1
10 - tons - 1

But this is not getting delivered, instead, it is only giving the results that are in the Lists of the member, that match the zones.
NOW, I can do this.

SELECT     Zones.ZoneID, Zones.ZoneName, Lists.AskID, Zones.SCID
FROM         Lists right OUTER JOIN
                      Zones ON Lists.ZoneID = Zones.ZoneID
WHERE     (Lists.AskID = 1 or Lists.AskID is null) AND (Zones.SCID = 29)

And this will give me all of the results, (Demonstrated Above on 1b that are in the zone that match the SCID=29.
However, IF another members comes in, that DOES NOT have that SCID=29 in his/her Lists, then they will ONLY get what is NULL displayed to them.

How can I get this query to display to the member ALL of the items that are listed in the Zones tables, regardless of WHO has it listed in their Lists Table.
I need it to show just like it is shown in 1b above, Basically like so:
If items in the
Do not match the
Then show Null or if it cannot show Null, then nothing is displayed.

Thank you
LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you have a design flaw in your zones table, as you have a relation to the SCID field...

hence, you should have the zones table like this:
ZoneID + ZoneName

and the Lists table
ListID + ZoneID

and eventually a third table that links

this is called normalization ...

please review if that answers your problem
Wayne BarronAuthor, Web DeveloperAuthor Commented:
The Lists table, is for the members ONLY, this is where they attach the zones they want to be a part of.
The Zones Table, list all the zones.
The SCID is from SubCat table.
The Zones Table, has a Relationship with the SubCat table, through the SCID column.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, this is the filter in regards to the user:
Lists.AskID = 1

I think you want this:
SELECT     z.ZoneID, z.ZoneName, l.AskID, z.SCID
FROM         Zones  z
LEFT JOIN  Lists  l
                      ON l.ZoneID = l.ZoneID
            AND l.AskID = 1 
WHERE     z.SCID = 29

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If you are filtering by (Lists.AskID = 1) then it only return this ID.
You may want to add the IS NULL filter as well:
SELECT     Zones.ZoneID, Zones.ZoneName, Lists.AskID
 FROM  Lists 
RIGHT OUTER JOIN Zones ON Lists.ZoneID = Zones.ZoneID
 WHERE  (Lists.AskID = 1 OR Lists.AskID IS NULL) AND (Zones.SCID = 29)

Open in new window

Wayne BarronAuthor, Web DeveloperAuthor Commented:
Your code gives me 341 results, duplicating each item, 31 times.
But after adding the GROUP BY, it displays the correct amount of records.
However, it shows the askid=1 for every record, and that is not going to work.
It needs to show the results, similar to what I demonstrated above in 1b,
Where is only has the Members ID beside the ones that they have in their list
And nothing beside the ones that are not in the list.

What did you do? Copy my code, and show it back to me again?
The code you provided, is from the 1c example above.
Which does not work.
Wayne BarronAuthor, Web DeveloperAuthor Commented:
I am close.
This will give me all the records back, However, it also shows the records for all other members as well.
So, it will not work.
Just need to find a way to NOT show the records of the other users.

SELECT Zones.ZoneID, Zones.ZoneName,Zones.SCID, Lists.AskID 
FROM Lists RIGHT OUTER JOIN Zones ON Lists.ZoneID = Zones.ZoneID 
WHERE (Lists.AskID = ?) AND (Zones.SCID = ?) OR (Zones.SCID = ?)

Open in new window

Anthony PerkinsCommented:
I have no idea what you are trying to achieve, but unfortunately as written above by including items from Lists in your WHERE clause you have an implicit INNER JOIN not an OUTER JOIN.  If you really want an OUTER JOIN than you should write it this way:
SELECT Zones.ZoneID, Zones.ZoneName,Zones.SCID, Lists.AskID 
FROM Lists RIGHT OUTER JOIN Zones ON Lists.ZoneID = Zones.ZoneID AND Lists.AskID = ?
WHERE (Zones.SCID = ?) OR (Zones.SCID = ?)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please show some input data from the different tables, as otherwise we would continue to "guess around"
including the current result (from my query) and the relevant expected results:
limited data is enough: 2- records of those to be included and also some to be excluded, to make it clear
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Anthony, that did it.
Removing the Askid out of the where, is what it was needing.
To be honest, the last couple of days, is the only thing that I have ever seen it used that way.
So, I am learning something new.

Thank you Anthony, you ROCK!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.