Argh! Two days of trying to get results from two tables ...

[dmcole]

I'm at wit's end. I have been trying to get a query built that produces information from two tables and I have been pulling my hair out for two days. I know this is simple, but I've tried LEFT JOINS with UNIONs to no avail.

 

I'm running MySQL 4.0.24 and have the following two tables:

 

table addresses
id  | first | last        | address           | Zip      | Country  |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1   | john  | smith       | 1234 Fifth St.    | 48069    |          |
2   | jeremy| jones       | 432 First St.     | 38406    |          |
3   | jane  | doe         | 345 Sixth St.     | 89046    |          |
4   | james | johnson     | 1 Second Ave.     | 48046    |          |
5   | kenny | ellis       | 1765 Mayflower St.| 96405    |          |
6   | sharon| nash        | 455 175th St.     |          | Canada   |
7   | sunny | daze        | 1015 Nova St.     | 92803    |          |
8   | fred  | morgan      | 912 Wright Ave.   | 28038    |          |


table attendance
id  | Person_ID  | Attend  |
++++++++++++++++++++++++++++
1   | 4          | NSS06   |
2   | 6          | NSS06   |
3   | 1          | NSS06   |
4   | 3          | NSS06   |
5   | 2          | NSS07   |
6   | 3          | NSS07   |
7   | 1          | NSS07   |
8   | 7          | NSS07   |

 

The result I want is:

*All who attended NSS06

*All who attended NSS07

*All whose zip is > 75001

 

From the data above, that should produce a list of seven names (id 8, fred morgan, didn't attend in either 06 or 07 and his zip code is less than 75001).

 

Any thoughts on how to solve this would be appreciated -- not only by me, but my wife as well, who says I'm losing my hair too fast anyway.

 

Thanks.

 

\dmc

 

[kenchucky]

select * from addresses where (zip>75001)or(Person_ID in (select id from attendance where attend in('NSS06','NSS07')))

[fnairb]

SELECT DISTINCT addresses.*
FROM addresses, attendance
WHERE addresses.id = attendance.Person_ID
  AND Attend IN ('NSS06', 'NSS07')
  AND Zip > 75001

[dmcole]

Boy, thanks for the quick responses.

 

@fnairb: That gets me only those who attended in 06 or 07 that are in zips 75001 or greater. It does not get me the people in the zips 00001-75000 who attended in 06 or 07 and it does not get me the people who didn't attend but whose zip is 75001 or greater. I want all who attended in NSS06 or NSS07 -- regardless of what their zip code might be -- and all whose zip code is greater than 75001 regardless of whether they attended in either year or not.

 

@kenchucky: Got the syntax error "near 'select id from attendance where attend in ( 'NSS06' , 'NSS07' ) "

 

Again, thanks. Keep those cards and letters coming.

 

\dmc

 

[fnairb]

SELECT DISTINCT addresses.*
FROM addresses, attendance
WHERE addresses.id = attendance.Person_ID
  AND (Attend IN ('NSS06', 'NSS07')
          OR  Zip > 75001)

[kenchucky]

kenchucky: Got the syntax error "near 'select id from attendance where attend in ( 'NSS06' , 'NSS07' ) "

The exact query works fine for me, so you must have changed it somehow, or have a weird MySQL version.

[dmcole]

Ding! Ding! Ding! We have a winner:

 

@fnairb: I had to tinker with the parenthesis to make it work absolutely correct, but it's mostly your work.

SELECT
     DISTINCT addresses .* FROM addresses , attendance
     WHERE addresses . id =attendance . Person_ID
     AND (Attend IN ('NSS06' , 'NSS07' ))
     OR (Zip >75001 )

 

Again, thanks so much.

 

\dmc

 

[dmcole]

OK, the client throws a monkey-wrench into the works.

 

Now we need to delete any row that has a "NSS08" entry in the attendance table.

 

So, a row would be deleted even if it had "NSS06" and "NSS07" and was in a zip greater than 75001.

 

I have tried building temporary tables and deleting one from the other, but I haven't been able to get the syntax to work.

 

Any further help would be appreciated.

 

TIA.

 

\dmc

 

[fnairb]

Let me see if I can restate to ensure I understand.

 

You want to delete any and every record in the attendance table that has Attend = 'NSS08'?

 

DELETE FROM attendance WHERE Attend = 'NSS08';

 

Or are you trying to delete from the addresses table?

[dmcole]

I just released "delete" is a poor choice of words.

 

Just as I'm trying to choose records that have the qualities of NSS06, NSS07 and Zip > 75001, I want to ignore any records that have the quality of NSS08.

 

In other words, taking your query of yesterday, the client wants me to make sure that the generated list doesn't have anybody who has already registered for NSS08.

 

Sorry for the poor wording.

 

\dmc

 

[fnairb]

Gotcha!

 

Could you paste in your final query.  I would hate for you to have to retrofit anything based on an out of date post.

[dmcole]

This works, but I need to keep anyone having an "NSS08" Attend record from being on the list.

 

SELECT DISTINCT
	addresses .* FROM addresses , attendance
	WHERE
	addresses.id = attendance.Person_ID 
	AND
		(Attend IN ("NSS06","NSS07"))
	OR
		(Zip > 75001)
	ORDER BY Zip

 

Again, thanks.

 

\dmc

 

[kenchucky]

SELECT DISTINCT

addresses .* FROM addresses , attendance

WHERE

addresses.id = attendance.Person_ID

AND

(Attend IN ("NSS06","NSS07"))

OR

(

(Zip > 75001)

  and (Attend!='NSS08')

)

ORDER BY Zip

[dmcole]

@Kenchucky:

 

Sorry, that doesn't elminate the NSS08 records ... they still show up in the query result.

 

Thanks.

 

\dmc

 

[fnairb]

SELECT DISTINCT

addresses .* FROM addresses , attendance

WHERE addresses.id = attendance.Person_ID

                          AND (ATTEND NOT IN ("NSS08")

                          AND (

AND (Attend IN ("NSS06","NSS07"))

OR  (Zip > 75001)

                          )

ORDER BY Zip

[fnairb]

This query is getting nasty.  You may be better of applying this last filter in your code instead of the query.

 

 

Though this should work

SELECT DISTINCT addresses .* 
FROM addresses , attendance
WHERE addresses.id = attendance.Person_ID
  AND ATTEND NOT IN ("NSS08")
  AND (
           Attend IN ("NSS06","NSS07")
           OR  Zip > 75001
        )
ORDER BY Zip

[dmcole]

From that query, I get this:

 

Check the manual that corresponds to your MySQL server version for the
right syntax to use near 'AND ( Attend IN ( "NSS06" , "NSS07" ) )  OR ( Zip > 75001 )  ) 

 

Any other thoughts?

 

Thanks.

 

\dmc

 

[richardw]

I just wanted to know if your making headway. If not, I will give it a try in the am. I have already recreated your table structures, but I won't be able to test my query till the am.

 

 

 

 

[dmcole]

@richardw ...

 

Thanks. fniarb's last two efforts haven't helped much ... and I wasn't clear what he meant about a filter ... that may be my lack of MySQL background showing through ...

 

Any help you could provide -- tomorrow or any day -- would be appreciated.

 

Again, thanks.

 

\dmc

 

[dmcole]

@fnairb:

 

The error message was from the code you posted Thursday night, not the code posted this morning. That code produced a set of records that actually includes anyone who attended in NSS06 or NSS07 but didn't eliminate the people who are pre-registered for NSS08.

 

Should I add a single-character field to the attendance table called "pre-register" and have the data entry people tick that off?

 

\dmc

 

[dmcole]

Something of a bump, but with new data!

 

Considering the problem as it now stands, here are some new data in the tables:

table addresses
id  | first | last        | address           | Zip5     | Country  |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1   | john  | smith       | 1234 Fifth St.    | 48069    |          |
2   | jeremy| jones       | 432 First St.     | 38406    |          |
3   | jane  | doe         | 345 Sixth St.     | 89046    |          |
4   | james | johnson     | 1 Second Ave.     | 48046    |          |
5   | kenny | ellis       | 1765 Mayflower St.| 96405    |          |
6   | sharon| nash        | 455 175th St.     |          | Canada   |
7   | sunny | daze        | 1015 Nova St.     | 92803    |          |
8   | fred  | morgan      | 912 Wright Ave.   | 28038    |          |
9   | dan   | dole        | 1444 Benjamin St. | 95050    |          |

table attendance
id  | Person_ID  | Attend  |
++++++++++++++++++++++++++++
1   | 4          | NSS06   |
2   | 6          | NSS06   |
3   | 1          | NSS06   |
4   | 3          | NSS06   |
5   | 2          | NSS07   |
6   | 3          | NSS07   |
7   | 1          | NSS07   |
8   | 7          | NSS07   |
9   | 9          | NSS07   |
10  | 9          | NSS08   |

The desired results include:

*All who are NSS06.

*All who are NSS07.

*All whose zip is > 75001

*None who are NSS08 (regardless of whether they meet above three criteria).

 

Any thoughts would be appreciated.

 

\dmc

 

[fnairb]

Ok, This is a bit drastic but I can't seem to crack this nut...

 

How about:

SELECT * FROM (
  SELECT DISTINCT addresses .* FROM addresses , attendance
  WHERE addresses.id = attendance.Person_ID 
    AND (Attend IN ("NSS06","NSS07"))
     OR (Zip > 75001)
)
WHERE Attend NOT IN("NSS08")
ORDER BY Zip

[dmcole]

@fnairb: I get an error on the second select; this is MySQL 4.0.24, which I don't think supports nested selects ...

 

I'm going to try to platform something a bit more current on another machine, load in this DB and see if this query works there.

 

\dmc

 

[fnairb]

The "filter it out in code" solution I mentioned should be a last resort but is certain to work.  Just use the query that gets all the data but doesn't exclude 'NSS08'.  In your loop where you run through the data (I guess there is an assumption that you are looping through the data) just through a check for 'NSS08' at the top of the loop.

 

This will get the job done but it is not very flexible as it only takes into account 'NSS08'.  However, if you need this done now and the future can take care of itself you'll have something.

[dmcole]

@fnairb: Because I had problems upgrading to something better than 4.0.24, I ended up using your "filter" concept. I ran a query where I retrieved all the NSS08 records and put them into an array with the Person_ID numbers and then did an array_search against that array before spitting out the names. Works great.

 

Thanks to all who gave some brain power to my problem.

 

\dmc