SQL Help

Ummm, first of all, your concept of a primary key is all jacked up. In no way should you have to filter through several constraints (ie, if such and such has XYZ pull that record.)

So your first order of business would be to make a proper primary key:
example: id INT NULL AUTO_INCREMENT PRIMARY KEY.

Once you have a primary key, you need to focus on creating proper database design. For example, instead of having a reference number column included in table 1. I would rather see you add another table for reference numbers and include the id of the reference numbers table as a reference id in table 1. So, you would link the reference number table and table 1 by a referenceid, so to speak.

 

Also, if you were so inclined, I would pick up SIMPLY SQL sitepoint books, and also PHP & MYSQL from novice to ninja sitepoint book by Kevin Yank. It would help you out a lot.

 

man i am taking a relational database class next semester...is this what i have to look forward to... eek.

 

Pun, your explanations of what you're trying to pull seem to conflict, but I think you're asking for something along the lines of this as an additional criteria:

and t1.table1_id not in (select table1_id from table1 a1 where not exists(select 123 from table2 a2 where a1.table_1_id = a2.table2_id and a1.table1_ref_numb = a2.table2_ref_numb and a2.table2_status not in ('RW', 'RE')))

 

This ^

But, you definitely need to clean that up. No one in there right mind is going to query consistent results on table with no referential integrity.

 

[EDIT] just saw arkoe already answered it. Here's my version with notes.
Rock3t Man, minor point. He could turn on referential integrity using two column primary keys.

-------------------

First, the CODE in your post doesn't match the table names in the example tables. I'm going to assume that's a typo. It isn't table1_id; it is table_1_id, right? and table*_ref_num is table*_ref_numb.

Given that correction, you can do this with a nested SELECT in the WHERE that returns all IDs for records missing from the original criteria. Like this:

Code:

SELECT table_1_id
     , table1_status
     , table1_ref_numb
     , table1_year
     , table2_id
     , table2_status
     , table2_source
FROM table1 t1
   , table2 t2
WHERE t1.table_1_id = t2.table2_id
AND t1.table1_ref_numb = t2.table2_ref_numb
AND t1.table1_status IN ('RE','RW')
AND t2.table2_status NOT IN ('RE','RW')
[COLOR="Red"]AND t1.table_1_id NOT IN[/COLOR] (
    SELECT table_1_id
    FROM
       table1 hp1
    WHERE NOT EXISTS
        (SELECT 1
         FROM table2 hp2
         WHERE hp1.table_1_id = hp2.table_2_id
         AND hp1.table1_ref_numb = hp2.table2_ref_numb
         AND hp2.table2_status NOT IN ('RE','RW')         
        )
    );

So,

Everything is the same except I added another AND criteria "AND t1.table_1_id NOT IN"
The NOT IN set is a nested SELECT
The nested SELECT returns ids of missing records instead of matching records
The WHERE NOT EXISTS will return your table1 IDs that do not have records in table2 matching your criteria.

I'm pretty sure this works.

 

Yeah he could but that would be a pain in the butt, especially since he would have to bring in the third column because ID and Ref Number from both tables look almost identical (haven't checked every datapoint). The composite primary key would be ID, RefNumber and Status.

heypartner has the right idea with the nested select statement, it's cleaner and easier to understand for anyone modifying it in the future.

 

I was getting confused reading what you wanted between the id's and the ref_numb's... had to read it a few times though and finally got the idea of what you wanted from the comments on the pic. I basically couldn't figure out though if you wanted to pull all 1's and 4's regardless of what was in table 2 or just the lines that were non RW/RE in table 2.

What HP and I wrote are exactly the same - he was just kind enough to format it (which is easier to read for someone coming along after).

 

But, ID and Ref Number are the same between both tables (if I understand the problem statement currently). So, they actually should be named the same.

Image the two tables having column one and two named

customer_id and order_id

That's the way I looked at it. The names of the columns is what sucks, not the schema, imo. The names should express similarity between the IDs. They don't. There lies the initial confusion we all had. I wanted to redesign the tables, too, but then realized its a perfectly fine schema, but with bad naming convention.

L'il Pun,

Read the above for my explanation of why your problem statement was so confusing at first.

 

Yes, arkoe described the exact same thing, first. Mine was just formatting with notes. At first, I actually posted an OUTER JOIN with NOT NULL in the nested select (that's my company's convention), but edited it to agree with arkoe, so I didn't confuse Lil Pun with two different ways of doing the same thing.

NOT EXIST, NOT IN and OUTER JOIN can be used in the Nested Select interchangeably for various performance tweaks. NOT EXIST looks the most readable for most.

 

Sum on hours and group by the ID.

 

Pun, I don't want to assume too much, but I get the feeling you learn these things on the job and you haven't had formal training, because I remember that a lot of what you ask (in this and a couple other threads) is covered in beginner to intermediate SQL and somewhat related database classes.

I would recommend that it would be more helpful to take a class or two both on how to properly design and maintain databases, and how to script and write good SQL Queries. I'm sure that with the struggles you seem to have, your employer would find it very helpful that you learn these things.

I'm not trying to sound like I know too much, because I don't, but I have been in the same boat you are now. I learned HTML, CSS, XML, and other markup languages on my own because they were easy, and some programming like JavaScript, PHP on my own, but as soon as I told upper management I was doing this myself and taking a bit longer, they helped get me the right training. Now I no longer script for a living, I manage higher-level components of the stuff I know how to program and have gone through a lot of formal instruction. With formal training, you have the foundation of how things can work better for your advantage and you have that paper or certification to back it up and make yourself worth more. If you've already had the classes, a refresher course might be able to help take some rust off previous practice.

Like I've said before about you, it's a good feeling to help you at any time, man.