I've got a table in my database that has 3 relationships to the same table (you can enter 3 states, so it has a relationship from like addressstateID to StateID in the state table, and one for billingstateaddress, etc).
In the table itself, I have 2 test columns entered. However, when I run my select statement it's pulling back 5,000 results. Everything is identical, except for Billing State and Shipping State. It'll pull back one record for Alabama and Alabama. Then the next record will be identical except it'll pull back Alabama and Alaska, and it keeps going through this for each and every possible combination of states, even though there are only 2 actual records in the database.
Doing my own troubleshooting, it seems to be narrowed down to the CROSS JOIN - if I remove the CROSS JOIN, then it only pulls back 2 records - but it only pulls back the StateID not the state names like I need.
I did something very similiar on another page, and it works flawless, and after 3 hours of looking through everything I can't figure out what would cause this. Has anybody else run into this before and would know what is causing it? I double checked the relationships and everything else, and I can't for the life of me figure it out.
SELECT a.VendorLocationId, a.VendorId, a.Address, a.City, a.StateId, a.ZipCode, a.ShippingAddress, a.ShippingCity, a.ShippingStateId, a.ShippingZipCode,
a.BillingAddress, a.BillingCity, a.BillingStateId, a.BillingZipCode, a.PrimaryPhone, a.SecondaryPhone, a.Fax, a.Email, b.State, c.State AS BillingState,
d.State AS ShippingState
FROM VendorLocation AS a CROSS JOIN
State AS b CROSS JOIN
State AS c CROSS JOIN
State AS d
WHERE (a.VendorId = @VendorID) AND (a.StateId = b.StateId OR
a.StateId IS NULL) AND (a.BillingStateId = c.StateId OR
a.BillingStateId IS NULL) AND (a.ShippingStateId = d.StateId OR
a.ShippingStateId IS NULL)