SQL question

[iamtiger]

I have 2 tables: A NewName table with fields of NewNameID and NewName, and a Name_Voter table that has fields of NewNameID, VoterID and vote.

I want all all the NewName rows from the NewName table and only vote rows from the Name_Voter table where the VoterID = 1. (There can be null values for the vote.)

The output could look like this

NewName Vote

chris 0
sam 1
john
bill 1
sue
ray 1

etc..

I Know a left join will give all rows from the newName table and only votes where the newNameID = from the left table = the newNameID from the right, but what if I want all tables from the left table, but only rows from the right table where the VoterID=1?

Thanks

[TheBao]

You can create a view to get around this:

USE Testing  -- replace this with your database name
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
        WHERE TABLE_NAME = 'VoteIDEqual1')
    DROP VIEW VoteIDEqual1
GO
CREATE VIEW VoteIDEqual1 (NewNameID, VoterID)
AS 
SELECT * FROM Name_Voter
WHERE VoterID=1
GO

Select * FROM NewName, VoteIDEqual1

Regards,
TheBao