Comparing 2 tables then showing data one doesnt have.

Mark_Nordstrom

I need to compare 2 tables that have some of the same event names and then i need to show the rows that the one table doesnt have.

This is what i came up with

SELECT name, Event.event FROM Event, Attendance WHERE name='Mark' AND Event.date > (CURDATE() - INTERVAL 2 MONTH) ORDER BY Event.date ASC

Event.event and Attendance.event have same data but Event.event has more rows and that is the data i wanna show. I need to compare Event.event to Attendance.event and show the events attendance doesnt have. Cant get it to work thou.

One more thing. Event table does not have the name column just Attendance does.

SELECT name, Event.event FROM Event, Attendance WHERE name='Mark' AND Event.event=Attendance.event AND Event.date > (CURDATE() - INTERVAL 2 MONTH) ORDER BY Event.date ASC

This shows common rows but i wanna show the rows that dont match. Problem is the event rows are VARCHAR if it was a number i could easily do it.

laserlight

Event.event and Attendance.event have same data but Event.event has more rows

You should combine them into one table, and either have a flag to denote which rows are under 'Attendance', or have an Attendance table that contains the keys of all the rows under 'Attendance'. I believe the latter would be preferable. Incidentally, have you read PHP Builder's article on Database Normalization and Design Techniques?

Mark_Nordstrom

laserlight wrote:
You should combine them into one table, and either have a flag to denote which rows are under 'Attendance', or have an Attendance table that contains the keys of all the rows under 'Attendance'. I believe the latter would be preferable. Incidentally, have you read PHP Builder's article on Database Normalization and Design Techniques?

Ive read most of it, but the problem is theres no similar id in these tables except the event rows and i dont need to get the results of Common data i can do that. I need to get the results of uncommon event names.

laserlight

the problem is theres no similar id in these tables except the event rows and i dont need to get the results of Common data i can do that. I need to get the results of uncommon event names

What is your current relevant database schema?

Mark_Nordstrom

Attendance structure is

name,
event,
date,

Event structure is
event

The event table shows every event possible while the attendance one shows names and the event name for the ones youve been to. I need to compare the 2 tables for the events you didnt go to.

laserlight

Ah, okay. Well, you could use a LEFT JOIN, e.g.,

SELECT name, Event.event FROM Event
    LEFT JOIN Attendance ON Event.event=Attendance.event
    WHERE name='Mark'
        AND Event.date > (CURDATE() - INTERVAL 2 MONTH)
        AND Attendance.event IS NULL
    ORDER BY Event.date ASC

Mark_Nordstrom

Doesnt return any rows. ill mess around with it maybe i can get this to work if you have any idea why it returns no results let me know.

The problem must be where Attendance.event IS NULL its not making the uncommon event names from event table null

basically say i got 2 events. One is named T. This event is on both rows, then I got an event named M and its only on the Event table. I wanna show the M event and disregard the T because its on both tables.

laserlight

Oh, I see why. For the condition name='Mark' to be true, there must be a record in the Attendance table. As such, Attendance.event cannot be NULL, so the combined condition is always false.

Now, a person can attend many events, and an event has many participants. As such, there is a many-many relationship between Attendance and Event. You should introduce a third table:

Attendance:
attendance_id
name
date

Event:
event_id

AttendanceEvent:
id
attendance_id
event_id

Now, to select the events that a person has not attended, you would write something along the lines of:

SELECT Attendance.name, Event.event_id FROM Attendance, Event, AttendanceEvent
    WHERE Attendance.attendance_id=AttendanceEvent.attendance_id
        AND Event.event_id=AttendanceEvent.event_id
        AND Attendance.name='Mark'
        AND Attendance.date > (CURDATE() - INTERVAL 2 MONTH)
    ORDER BY Attendance.date

I have taken the liberty of changing Event.date to Attendance.date, though if all participants must attend the event on the same date then it should be Event.date (and your schema should be modified accordingly). Sorry if my SQL is incorrect, I am rather rusty.

Mark_Nordstrom

So create a 3rd table with them rows with no data ?

laserlight

Yes, it is outlined on page 5 of the article I linked to.

Mark_Nordstrom

laserlight wrote:
Yes, it is outlined on page 5 of the article I linked to.

How do I populate that table thou with the data i need ? As the Query does not do that as it still returns zero results because the 3rd table isnt populated.

Mark_Nordstrom

Is there anyway to assign ids to each event name and have the same id for that event on each table ?

So on the table Attendance the Event T would have the event_id of 1 and the same ID as on table Event ?

This way I can just do a <= or >= with the WHERE clause.

Something like UPDATE Attendance SET event_id=
Problem is getting the same event_id for each table.

Mark_Nordstrom

Or even easier idea is query to create a 3rd table with all event and name next to each event then a 3rd column with a 0 if you attended it and a 1 if you didnt.

Problem is I wouldnt know where to start with this.

laserlight

How do I populate that table thou with the data i need ? As the Query does not do that as it still returns zero results because the 3rd table isnt populated.

The general idea would be to select the data from the existing Attendance table and then insert it into AttendanceEvent. It is basically just a separation of tables. The query you want should be possible on your current schema, but normalisation is usually better.

I note that my suggested query is wrong 🙁
The date field should probably be in AttendanceEvent instead, upon which you may be able to write:

SELECT event_id, "date" FROM AttendanceEvent WHERE attendance_id NOT IN (
    SELECT attendance_id FROM Attendance
        WHERE name='Mark'
            AND "date" > (CURDATE() - INTERVAL 2 MONTH))

Is there anyway to assign ids to each event name and have the same id for that event on each table ?

That's precisely what the Event table is for, in my example. Attendance becomes more like Participants (a list of participants), and Event becomes more like Events (a list of events), with the third table recording each occasion where a participant participated in an event.

Mark_Nordstrom

Ive hit a brick wall on this. As its not resolved but just cant get it to work. Just gonna give up and do it another way. Thanks for the help.

Mark_Nordstrom

SELECT name, Event.event FROM Attendance, Event WHERE name='Angor' AND Event.event NOT LIKE '%Attendance.event%' AND Event.date > (CURDATE() - INTERVAL 2 MONTH) GROUP BY Event.event ORDER BY Event.date ASC

This shows all events in Events, the problem is it want it to show only events that dont match the event names in attendance.

SELECT name, Event.event FROM Attendance, Event WHERE name='Angor' AND Event.event LIKE Attendance.event AND Event.date > (CURDATE() - INTERVAL 2 MONTH) GROUP BY Event.event ORDER BY Event.date ASC

This shows common names in the 2 tables. Shouldnt NOT LIKE work like i want it to ?