Sql-server – SQL, creating a new table with auto generated passords from other tables

selectsql servertable

i have this sql below which i have retrieved from our student database. It retreives all that i need, but i would like to create a new table which includes all the tables plus one column with auto generated passwords.

Is this possible?

SELECT
    CurrentPupil.SchoolID AS 'SchoolID',
    CurrentPupil.PupilID AS 'PupilID',
    PupilPersonalDetails.Surname AS 'Surname',
    PupilPersonalDetails.Forename AS 'First_Name',
    PupilPersonalDetailsGenderLookupDetails.Description AS 'Gender',
    ContactMaster.LastName AS 'Contact_Last_Name',
    ContactMaster.FirstName AS 'Contact_First_Name',
    ContactMasterGenderLookupDetails.Description AS 'Contact_Gender',
    ContactMaster.LastName2 AS 'Contact_Last_Name_2',
    ContactMaster.FirstName2 AS 'Contact_First_Name_2',
    ContactMasterGender2LookupDetails.Description AS 'Contact_Gender_2',
    ContactEmailInformation.EmailID AS 'Contact_Email_Address',
    PupilCurrentSchoolYearGroupLookupDetails.Description AS 'Year_Group',
    PupilCurrentSchoolFormSchoolLookupDetails.Description AS 'Form',
    ContactMaster.ContactID
FROM
    win2k12engage.EngageSpratton.dbo.CurrentPupil
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilPersonalDetails AS PupilPersonalDetails ON CurrentPupil.SchoolID = PupilPersonalDetails.SchoolID
AND CurrentPupil.PupilID = PupilPersonalDetails.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS PupilPersonalDetailsGenderLookupDetails ON PupilPersonalDetailsGenderLookupDetails.LookupDetailsID = PupilPersonalDetails.Gender
AND PupilPersonalDetailsGenderLookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilContacts AS PupilContacts ON CurrentPupil.SchoolID = PupilContacts.SchoolID
AND CurrentPupil.PupilID = PupilContacts.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.ContactMaster AS ContactMaster ON PupilContacts.ContactID = ContactMaster.ContactID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.ContactEmailInformation AS ContactEmailInformation ON ContactMaster.ContactID = ContactEmailInformation.ContactID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS ContactMasterGenderLookupDetails ON ContactMasterGenderLookupDetails.LookupDetailsID = ContactMaster.Gender
AND ContactMasterGenderLookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS ContactMasterGender2LookupDetails ON ContactMasterGender2LookupDetails.LookupDetailsID = ContactMaster.Gender2
AND ContactMasterGender2LookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilCurrentSchool AS PupilCurrentSchool ON CurrentPupil.SchoolID = PupilCurrentSchool.SchoolID
AND CurrentPupil.PupilID = PupilCurrentSchool.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS PupilCurrentSchoolYearGroupLookupDetails ON PupilCurrentSchoolYearGroupLookupDetails.LookupDetailsID = PupilCurrentSchool.YearGroup
AND PupilCurrentSchoolYearGroupLookupDetails.LookupID = '3004'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.SchoolLookupDetails AS PupilCurrentSchoolFormSchoolLookupDetails ON PupilCurrentSchoolFormSchoolLookupDetails.LookupDetailsID = PupilCurrentSchool.Form
AND PupilCurrentSchoolFormSchoolLookupDetails.LookupID = '1002'
WHERE
    ('%wc%' = '%wc%')

*************************************UPDATE***************************
Hi Guys back to my desk. I am adding one additional column to the current query and have that additional column contain a random string of data that would be a password…also
the tables / columns above are from a database that i cannot write to but only read, so i use the win2k12engage.EngageSpratton.dbo.fieldname to connect to my other database Eletter.dbo.newtable where i want to have all the fields above plus one more column with the password.

Cheers

Many thanks in advance
Vitor

Best Answer

I don't have a Database to test this on, but Potentially, you could use DEFAULT, RAND() and REPLACE() to do this.

CREATE TABLE Users( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Password varchar(255) DEFAULT REPLACE(FLOOR(RAND()*(987654321-123456789+1))+123456789,'1','!') );

Just keep adding more REPLACE to make the Password a bit more Complex with each.

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

MySQL – Get Items Without Successful ‘Sent’ Log Entry

Query itself seems to be quite straightforward (from data sample provided I assume that a record with action:sent , failure_reason:NULL is the final state for a given list_id) :

SELECT a.*
FROM list a
WHERE NOT EXISTS (SELECT NULL FROM log b WHERE b.list_id = a.list_id
and b.action='Sent' and b.failure_reason IS NULL)

However, if tables are huge , performance will suffer even if you have a covering index in log table which is probably not good - name "log" implies that you are mostly inserting data into it (and insert has to be as fast as possible), so each extra index defeats the main purpose of this table.
It looks to me though that date/datetime field is missed in the model; I guess you typically want failed tasks for a particular (presumably relatively small) date interval .

Best Answer

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

MySQL – Get Items Without Successful ‘Sent’ Log Entry

Related Question