Union query produces same results after changing WHERE 1

[melaniews]

All I'm trying to do is put a group of records from the first table together with a group of records from a second table. I want records meeting certain criteria from the first and all records from the second.

This query produces lines only from the second table. I've changed it several times and get the same 108 record result every time. That is everything in the second table. Unless I make it some really wild criteria, and then I get most of the records from the first table and nothing from the second. What am I doing wrong?


SELECT [location name1], [mailing address1], [mailing address2], [mailing city], [mailing state], [mailing zip5]
FROM LOCATION
WHERE [location name1]="dick*" and "*sporting*";
UNION SELECT [company], [address1], [address2], [city], [state], [zip]
FROM sheet1
ORDER BY [mailing state], [mailing city], [mailing address1];

Thanks for your help.

 

[KenReay]

Hi

This is because you have an error in your WHERE clause, you are anding two literals and getting a false, which never matched the left hand side of the WHERE,

you need

WHERE [location name1]="dick*" and [location name1]= "*sporting*"
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[AutumnBlues]

try this...

SELECT [location name1], [mailing address1], [mailing address2], [mailing city], [mailing state], [mailing zip5]
FROM LOCATION
WHERE [location name1] like "dick*" and [location name1] like"*sporting*";
UNION SELECT [company], [address1], [address2], [city], [state], [zip]
FROM sheet1
ORDER BY [mailing state], [mailing city], [mailing address1];

 

[melaniews]

Thanks to both who replied, that helped a lot. However, I still get the same 108 records from the second table. I'm not getting anything from the first table. I should get 108 from table 2 and whatever comes up from table 1. There are records in table 1 that meet the criteria, I checked.

Is there a way to reset or clear the previous results. It seems to be generating the same thing no matter what changes.

 

[KenReay]

Hi

A) you have removed the ; after the firsts elct haven't you, as well as the other change I posted.

B) have you tried each SELECT on its own? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[melaniews]

The change is there and the ; after first select is gone. I tested the criteria in table 1 and it didn't work. So I made one that did work and copied it into a Union query. Still doesn't work. I get only the records from table 2.

There is an apostrophe in the names that I am looking for in table 1. Is that perhaps throwing it off? I'm trying to get "dick's sporting goods" from one table and "dicks sporting goods" from the other. I also wanted to leave it open to other near options as well, but those two I know for sure.

 

[KenReay]

Hi

Surely you cannot have got any rows selected with

WHERE [location name1] like "dick*" and [location name1] like"*sporting*"

this [location name1] like "dick*" and [location name1] like"*sporting*" can never be true


should surely be

WHERE [location name1] like "dick*" OR [location name1] like"*sporting*"

I just feel like adumbo for not spoting it sooner!!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[melaniews]

You're right on the "OR" thing. Here's what it looks like now -- and still doesn't work in Union. Works in select query very well.

SELECT [location name1], [mailing address1], [mailing address2], [mailing city], [mailing state], [mailing zip5]
FROM LOCATION
WHERE [location name1]="dick's spo*" or [location name1]="dicks spo*"
UNION SELECT [company], [address1], [address2], [city], [state], [zip]
FROM sheet1
ORDER BY [mailing state], [mailing city], [mailing address1];

What about that apostrophe? Would it cause problems?

Thanks.

 

[melaniews]

Okay I got it to work somewhat. Here's what it looks like.

SELECT LOCATION.[LOCATION NAME1], LOCATION.[MAILING ADDRESS1], LOCATION.[MAILING ADDRESS2], LOCATION.[MAILING CITY], LOCATION.[MAILING STATE], LOCATION.[MAILING ZIP5]
FROM LOCATION
WHERE (((LOCATION.[LOCATION NAME1]) Like "dic*&quot)
UNION SELECT [company], [address1], [address2], [city], [state], [zip]
FROM sheet1
ORDER BY [mailing state], [mailing city], [mailing address1];

Now my question is how do I modify it so I only get dick's sporting goods. I don't want Dick's place or dick's RV etc. I'm not sure how to work with those parentheses.

Thanks. Melanie

 

[CosmoKramer]

Your WHERE criteria should be OK. Try adding it to the second select also.....

SELECT [location name1], [mailing address1], [mailing address2], [mailing city], [mailing state], [mailing zip5]
FROM LOCATION
WHERE [location name1] like "dick*" and [location name1] like"*sporting*";
UNION SELECT [company], [address1], [address2], [city], [state], [zip]
FROM sheet1

WHERE [company] like "dick*" and [company] like "*sporting*"

ORDER BY [mailing state], [mailing city], [mailing address1];

 

[KenReay]

Hi again

Since your delimeter is " I do not see why an embedded ' in the search field would make any difference.

However you keep on changing the Select statement the last one you posted with a ' in it looked so:

SELECT [location name1], [mailing address1], [mailing address2], [mailing city], [mailing state], [mailing zip5]
FROM LOCATION
WHERE [location name1]="dick's spo*" or [location name1]="dicks spo*"

This will not select records becuase you are using = not like, it should be:

SELECT [location name1], [mailing address1], [mailing address2], [mailing city], [mailing state], [mailing zip5]
FROM LOCATION
WHERE [location name1] Like "dick's spo*" or [location name1] Like "dicks spo*"



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use