TABLE JOINS QUESTION

[JohnCP]

Hi all, I worked on this for awhile, and i just can't seem to get it right. I have 3 tables, all with the same column names on each table: EMAIL, LASTNAME, FIRSTNAME, SIGNEDIN. the last column, SIGNEDIN contains a value of NULL or 'YES' depending on who signed in. The first 3 column values are the same for all 3 tables.I'm trying to use a join to get collect data on the email address of the people that have a value of NULL for the SIGNEDIN column from all 3 tables. now with this new list of people, i'm going to compare it to another table (TABLE_FOUR) that contains the same EMAIL column list and see if the SIGNED_IN column value of NULL matches the SIGNED_IN FIELD in TABLE_FOUR by email address. Sorry if this sounds confusing, but any help is much appreciated..Thanks, JP

[jteixeira]

Select "colums name"from table1 INNER JOIN table2 on table2.(foreign column)=table1.(foreign column) INNER JOIN table3 on table3.(foreign column)=[table1.(foreign column) or table2.(foreign column)]

[justsomeguy]

Lets start with the first one, a way to get all rows where the signedin field is null. This query assumes that the exact same rows are in each table (i.e. all email fields in table 1 are also in tables 2 and 3, and vice versa).This is for MS SQL Server:

SELECT email, lastname, firstnameFROM table1 AS t1, table2 AS t2, table3 AS t3WHERE t1.email=t2.email AND t1.email=t3.email AND ISNULL(t1.signedin, 'NO') = 'NO' AND ISNULL(t2.signedin, 'NO') = 'NO' AND ISNULL(t3.signedin, 'NO') = 'NO'

This is for MySQL:

SELECT email, lastname, firstnameFROM table1 AS t1, table2 AS t2, table3 AS t3WHERE t1.email=t2.email AND t1.email=t3.email AND t1.signedin IS NULL AND t2.signedin IS NULL AND t3.signedin IS NULL

Start with that. I'm not sure specifically what you mean with the query for table4, but you can probably add it into the existing query. For example, to get all users with a null in the first three tables, but a non-null in table4, you can do this in MySQL:

SELECT email, lastname, firstnameFROM table1 AS t1, table2 AS t2, table3 AS t3, table4 AS t4WHERE t1.email=t2.email AND t1.email=t3.email AND t1.email=t4.email AND t1.signedin IS NULL AND t2.signedin IS NULL AND t3.signedin IS NULL AND t4.signedin IS NOT NULL